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

netanel99 · Apr 25th 2021

Hello everyone,

really ask for this help to finish this very long project, will be so grateful for any help please.

I'm trying to write a VBA code that executed by a commandbutton, the code will search for a folder called "library" next to the excel file, and inside that library the code will search for the last created Excel file and copy it and paste it with the name the user gave it in the commandbutton.

I've added 2 pictures that demonstrate what I'm trying to accomplish, and I've added a code not really related, the code is doing something different but I thought that it might help, the code copy paste an excel file located next to the file and paste it inside a folder, name it like the user type and create a row and create hyperlink . This code was created by JBeaucaire :

Quote

Option Explicit

Private Sub CommandButton1_Click()

Dim StrategyName As String, strategyRow As Long, NewName As String 'JBeaucaire made this code.

StrategyName = Application.InputBox("Enter the new product/service name", "New product/service Name", Type:=2)

If StrategyName = "False" Then Exit Sub

On Error Resume Next

strategyRow = WorksheetFunction.Match(StrategyName, ThisWorkbook.Sheets("Products & Services Contents").Range("B:B"), 0)

If strategyRow > 0 Then

MsgBox "This product/service already exists"

Exit Sub

End If

MkDir ThisWorkbook.Path & "\StrategiesAuto"

MkDir ThisWorkbook.Path & "\StrategiesAuto" & "\" & StrategyName

NewName = ThisWorkbook.Path & "\StrategiesAuto" & "\" & StrategyName & "\StrategyFromTemplate-" & StrategyName & ".xlsm"

FileCopy ThisWorkbook.Path & "\StrategyFromTemplate.xlsm", NewName

With ThisWorkbook.Sheets("Products & Services Contents").Range("B" & Rows.Count).End(xlUp).Offset(1)

.Value = StrategyName

ActiveSheet.Hyperlinks.Add _

Anchor:=.Offset(, -1), _

Address:=NewName, _

TextToDisplay:="Link"

Workbooks.Open NewName

End With

End Sub

Display More

**royUK** · Apr 25th 2021

Pictures don't help, attach an example file.

netanel99 · Apr 25th 2021

Thanks so much for the follow up Roy!!

I've attached 2 files, with the unreverent code from above, it does something a bit different, it copies the excel's template file that located in the same folder with the excel with the button and paste it in a specific sub-folder,

this might help because I'm trying to achieve something close to that, I'm trying to make the code to duplicate the last created excel file (macro available) inside a folder called "library" that located next to the excel's file with the commandbutton.

I've attached the 2 files with explanation,

Roy I appreciate it so much thank you!

Please, if there's anything I can add/do, I'm 100% dedicated to accomplishing the task , I'll do whatever it takes.

StrategyFromTemplate.xlsm

StrategyFromTemplate-Project Tracker.xlsm

**royUK** · Apr 25th 2021

What do mean by next to? Do you mean a sub folder of where the main workbook is?

Try this, I haven't tested it but post back with any errors and I'll check back tomorrow.

Code

Sub NewestFile()
    Dim wbNew As Workbook
    Dim sPath As String, sFil As String, sLastFil As String
    Dim dLastDate As Date, sLastModified As Date
    ''///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
        sLastModified = FileDateTime(sPath & sFil)
        If sLastModified > dLastDate Then
            sLastFil = sFil
            dLastDate = sLastModified
        End If
        sFil = Dir
    Loop
    Application.ScreenUpdating = False
    Set wbNew = Workbooks.Open(sPath & sLastFil)
    ThisWorkbook.SaveAs sPath & Application.PathSeparator & wbNew.Name & "- Copy", 51   ''///51 = xlsx
    wbNew.Close False
    
End Sub

Display More

netanel99 · Apr 26th 2021

Thanks for the big hopes here, respect your help here!

I'm sorry by "next to" I've meant where the main workbook is.

I've tried the CODE, I didn't quiet worked it didn't failed either it gives back message: "No files were found" ,

I've tried to put the code inside "commandbutton" and inside a module, It gives the same error, I've tried to change xls to xlsx to see if it'll work, and tried to put in the Library folder xls file, Xlsx file and xlsm file , it didn't unfortunately

I've forgot to mention that I'm trying to duplicate the last xlsm file I'm sorry it's was important detail I was missing to give.

thanks a million! can't stretch it enough .

Net

**royUK** · Apr 26th 2021

So the files are in the same folder. Maybe this will work

Code

Sub NewestFile()
 Dim wbNew As Workbook
 Dim sPath As String, sFil As String, sLastFil As String
 Dim dLastDate As Date, sLastModified As Date
 ''///change the file path
 sPath = ThisWorkbook.Path
 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
 Application.ScreenUpdating = False
 Set wbNew = Workbooks.Open(sPath & sLastFil)
 ThisWorkbook.SaveAs sPath & Application.PathSeparator & wbNew.Name & "- Copy", 51 ''///51 = xlsx
 wbNew.Close False
 
End Sub

Display More

netanel99 · Apr 26th 2021

Thank you! really ! !!!

I'm sorry for being not clear , the excel xlms files that I want to duplicate the newest one are located in a sub folder that refer to the excel file with the VBA button, the name of the sub folder is "Library"

I'm getting right away to test the new code thanks!

netanel99 · Apr 26th 2021

Thanks for the follow up !

I've tested it . Code Search For old "xls." file, I wish please if it can search for the latest xlms. It opens the Xls file that I've put there and VBA gives run error 1004 - doesnt had access to the file.

I'll be playing with the code and see what I understand and can do , probably not much though.

**royUK** · Apr 26th 2021

Try changing this

Code

sFil = Dir(sPath & "*.xls*", vbNormal)

to 

sFil = Dir(sPath & "*.xls", vbNormal)

netanel99 · Apr 26th 2021

Sure thing , doing it now.

netanel99 · Apr 26th 2021

still the same error, oh the other right * is for making it search whatever excel file - amazing I wish I knew how to write code like this, is it possible so that the VBA will ask the user what is the name of the new file so before it'll duplicate it , and add a row inside the table with a hyperlink ? that will be like.. a dream

**royUK** · Apr 26th 2021

You can use an InputBox to get the new name.

I don't think we have the correct folder to search. It's currently searching the folder that the workbook with the code is in.

netanel99 · Apr 26th 2021

Thanks , I'll try to do it hope for a new breaking score in my VBA skills

I added what you wrote in the first code

"sPath = ThisWorkbook.Path & Application.PathSeparator & "library""

to the second code , and now it search in Library , but it doesn't want to duplicate the file

Code

ThisWorkbook.SaveAs sPath & Application.PathSeparator & wbNew.Name & "- Copy", 51 ''///51 = xlsx

looks like Japanese to me, maybe I can ask my girlfriend

**royUK** · Apr 26th 2021

Is it finding the file?

We can add an InputBox later.

Try this so we can see what it is opening. When the code has finished see if it has opened the correct file.

Code

Sub NewestFile()
 Dim wbNew As Workbook
 Dim sPath As String, sFil As String, sLastFil As String
 Dim dLastDate As Date, sLastModified As Date
 ''///change the file path
 sPath = ThisWorkbook.Path
 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
 Application.ScreenUpdating = False
 Set wbNew = Workbooks.Open(sPath & sLastFil)
' ThisWorkbook.SaveAs sPath & Application.PathSeparator & wbNew.Name & "- Copy", 51 ''///51 = xlsx
' wbNew.Close False
 
End Sub

Display More

netanel99 · Apr 26th 2021

Yes it finding the file and opening it on the background

Thank you I'm running it and testing it for opening the newest file , doing it right away.

**royUK** · Apr 26th 2021

When you've checked that we can save it as the new workbook

netanel99 · Apr 26th 2021

Amazing!! It opens the newest file regardless if it xlsx or xlsm . This opens the newest file without problem or any error.

netanel99 · Apr 26th 2021

Quote from royUK

When you've checked that we can save it as the new workbook

Oh it didn't saved the file, sorry for confusing, it gave error for trying to save it as a new file, but it opens the newest file on any folder I choose, but it seems to me like this was the trickiest part, not like I know how to proceed

netanel99 · Apr 26th 2021

This can be amazing for my project, thank you so much, I'll be able to make the user always open the last Excel file and work on it, giving ability to work with much needed previous texts inside. and just review and update what needed, it's amazing efficiency.

**royUK** · Apr 26th 2021

That's why I used the last *, it acts as a Wild card so any file extension beginning with .xls is found.

This code will prompt the user for a name. Note, if they don't enter a name it will keep prompting twice more

Code

Sub NewestFile()
    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
    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 & Application.PathSeparator & wbNew.Name & ans, 51 ''///51 = xlsx
    wbNew.Close False
    
End Sub

Display More

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

Participate now!

Share