Save a Closed Workbook with new name in New Location

[rhombus4]

I know I can save an open/active workbook using code below.

What I want to do is Copy a closed workbook and Save a Copy into a New Location adding the Date

i.e If Closed workbook is Called MainDoc.xls and is located in C:\MainDoc.xls

I want it copied to my documents and add today's date and full time in case it is copied at same time e.g. C:\MyDocs\Maindoc 25-06-20 14:06:22.xls

Dim wbookstring As String
wbookstring = "C:\Copy.xlsx"
ActiveWorkbook.SaveAs filename:=wbookstring

 

[Trevor G]

You will have to set the Reference to use Microsoft Scripting. You also wont get the format in the way you want for time.

Sub CopyFile()
Dim OfSO As Object
Set OfSO = CreateObject("Scripting.FileSystemObject")
Call OfSO.CopyFile("C:\MainDoc.xls", "C:\MyDocs\Maindoc " & Format(Date, "DD - MM - YY") & ".xls")

End Sub

 

[rhombus4]

Thanks Trevor, but is there another way without using Microsoft SCripting

 

[Trevor G]

Try this

Sub CopyFile2()
Dim sFolder, dFolder As String
sFolder = "C:\"
dFolder = "C:\MyDocs\"
FileCopy sFolder & "MainDoc.xls", dFolder & "MainDoc " & Format(Now(), "DD mmm YY") & ".xls"
End Sub

 

[rhombus4]

Thanks again
wasn't sure if it would cause error when saving as same name but it worked ok!

but did edit the line to get time without : i.e. 161723

& Format(Now(), "DD mmm YY hhmm:ss") & ".xlsx"