VBA to copy-paste the last created Excel file inside of a folder and naming it.

THANKS for the follow up!! I'm on it right away.

It finds the newest file and opens it and ask for a name for the new file, but the code doesn't want to duplicate it, the stubborn error is "run time error 1004 - didn't had access to the file-try to see that the folder exist /doesn't have something like [email protected]#$% .."

And the VBA code flash in yellow this code

wbNew.SaveAs sPath & Application.PathSeparator & wbNew.Name & ans, 51 ''///51 = xlsx 

wow If you knew how excited I'm to hear that , thanks sir.

Grateful for your help and skills.

sure I'll add it now .

Code finds successfully the latest excel file inside "library" folder, than the Code gives an error when trying to copy-paste the file

wbNew.SaveAs sPath & Application.PathSeparator & wbNew.Name & ans, 51 ''///51 = xlsx

The code opens the latest excel file I wonder if the code can open the new file that get duplicated instead , and add a link in the table below.

Attached the excel file, I have in the same folder of this excel file a sub-folder called "library" and inside it few excel files and the code always finds the latest excel file successfully.

StrategyFromTemplate-Project Tracker.xlsm

Best wishes,

Netanel.

Thank you for the follow up.

It gives "compile error - wrong number of arguments or invalid property assignment" and flash yellow "Private Sub CommandButton1_Click()" and it seems like the code have problem with "SaveCopyAs".

thanks ,

Netanel.

Hi Roy!

Hovering the cursor doesn't gave any more details, I've attached a photo shows how it looks with yellow row "Private Sub CommandButton1_Click()"

and a blue mark on "SaveCopyAs"

regardless if I tried the "commandbutton" (code on sheet) or the button (code on module).

sorry for all of it being a tricky problem sir.

best regards,

Netanel

Thanks for the follow up! Still same error like last time,

Added the last updated excel file

StrategyFromTemplate-Project Tracker.xlsm

Sub Secondone()
 Dim wbNew As Workbook
 Dim sPath As String, sFil As String, sLastFil As String
 Dim dLastDate As Date, sLastModified As Date
 Dim iCnt As Integer
 Dim ans As Variant
 ''///change the file path
 sPath = ThisWorkbook.Path & Application.PathSeparator & "library"
 If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
 sFil = Dir(sPath & "*.xls*", vbNormal)
 If Len(sFil) = 0 Then
 MsgBox "No files were found...", vbExclamation
 Exit Sub
 End If
 Do While Len(sFil) > 0 And sFil <> ThisWorkbook.Name
 sLastModified = FileDateTime(sPath & sFil)
 If sLastModified > dLastDate Then
 sLastFil = sFil
 dLastDate = sLastModified
 End If
 sFil = Dir
 Loop
prompt_again:
 iCnt = iCnt + 1
 If iCnt < 3 Then
 ans = InputBox("Please Enter a name for the new workbook", "Enter Name")
 If Len(ans) = 0 Then GoTo prompt_again
 Else: Exit Sub
 End If
 Application.ScreenUpdating = False
 Set wbNew = Workbooks.Open(sPath & sLastFil)
 wbNew.SaveCopyAs sPath & wbNew.Name & ans, 51 ''///51 = xlsx
 wbNew.Close False

End Sub

Thank you so much Roy!

The code successfully asks for a name for the new excel file, then locate the latest excel file inside the folder and copy- paste it, giving it the name which the user has chosen.

Is it possible please to add to it that the new excel file will be open and the code will make a new row in the table, with a hyperlink to the file ?

I was trying to integrate this to do it but I'm not really good at VBA and it failed

With ThisWorkbook.Sheets("Strategies Contents").Range("B" & Rows.Count).End(xlUp).Offset(1)
    .Value = StrategyName
    ActiveSheet.Hyperlinks.Add _
        Anchor:=.Offset(, -1), _
        Address:=NewName, _
        TextToDisplay:="Link"
    Workbooks.Open NewName

Wow that's an interesting question to speak about, because I've found that , at least in my opinion , this holds an unbelievable efficiency and productivity power that goes long long way afterwards,

For me excel is the real power house for database management , I don't use Access, that because I just have a table that connect with hyperlinks to different excel files and just draws the important data from the other excel files to the table(and if I change something I update)- while keeping my main excel file with the organizing table running like a rabbit with all the power of excel instead of the unfriendly Access, for me its a perfect win win situation , If you like I can show you my work with deeper insights on Zoom .

Here, it's a special database that focus always on the last file, because there's an ongoing update action that takes place on the newest file all the time.

I hope that I didn't explained wrong ,please I've added pictures to illustrate what I'm asking ,

Thank you for being the only one who helped me in this problem

I did the last part of the code, I don't know how, but it works, the code add a new row in the table, add the hyperlink to the new excel file and opens the new excel files,

Is it possible to make the code copy-paste the last Created excel file in "library" folder, instead of last Modified excel file ? I just paid attention to it and it will make trouble down the line because the real intent is for the last created file.

Is it possible in a case of an error when the code search for the last edited/created excel file , that it will open simply the file "Template.xlsm" please ? (just to make sure that the code will still work even if there'll be an error, sometimes I move files from one hard disc to the other and the all files get the same date created/edited)

best regards,

Netanel

last code working, with my add on .

Option Explicit

Private Sub CommandButton1_Click()
 Dim wbNew As Workbook
 Dim sPath As String, sFil As String, sLastFil As String
 Dim dLastDate As Date, sLastModified As Date
 Dim iCnt As Integer
 Dim ans As Variant
 ''///change the file path
 sPath = ThisWorkbook.Path & Application.PathSeparator & "library"
 If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
 sFil = Dir(sPath & "*.xls*", vbNormal)
 If Len(sFil) = 0 Then
 MsgBox "No files were found...", vbExclamation
 Exit Sub
 End If
 Do While Len(sFil) > 0 And sFil <> ThisWorkbook.Name
 sLastModified = FileDateTime(sPath & sFil)
 If sLastModified > dLastDate Then
 sLastFil = sFil
 dLastDate = sLastModified
 End If
 sFil = Dir
 Loop
prompt_again:
 iCnt = iCnt + 1
 If iCnt < 3 Then
 ans = InputBox("Please Enter a name for the new workbook", "Enter Name")
 If Len(ans) = 0 Then GoTo prompt_again
 Else: Exit Sub
 End If
 Application.ScreenUpdating = False
 Set wbNew = Workbooks.Open(sPath & sLastFil)
 wbNew.SaveAs sPath & ans, 52 ''///51 = xlsx 52= xlms
 wbNew.Close False
 
  With ThisWorkbook.Sheets("Products & Services Contents").Range("B" & Rows.Count).End(xlUp).Offset(1)
 .Value = ans
 ActiveSheet.Hyperlinks.Add _
 Anchor:=.Offset(, -1), _
 Address:=(sPath & ans & ".xlsm"), _
 TextToDisplay:="Link"
 Workbooks.Open sPath & ans
 End With
End Sub

Quote

It should be saving into the folder called library.

yes , it is , the code working good just need 2 adjustments.

Quote

Do you ant to open a template workbook not the last modified?

Right now it open the last modified, but I want it to open the last created. (and also copy paste it and everything evolved this code on last created instead of last modified)

And only in a case of error, I'm thinking that sometimes I move files from hard disk to hard disk and it doesn't save the last created date on the excel files, so I'm thinking maybe in this case the code will give an error, so I wonder what you think and if it will be smart to add "if" to the code that in a case that the code doesn't find last created date excel file that it'll open specific excel file in the "library" called "template.xlms" ?

thank you for your patient brother.

best regards,

netanel