asked on

Can this code work for appending date to file name? Getting error message in Excel 2010 and 2016.

I had this question after viewing Looking for macro to do version saving of workbooks in Excel 2016..

Found this code at: http://www.excelforum.com/showthread.php?t=552718

Sub SaveCopyOfFile()
 Dim s As String
 Const FileLocation As String = "C:\UnderDevelopment\"
 s = FileLocation & ActiveWorkbook.Name
 s = s & Space(1) & Format(Now, "yyyy mm dd Hh Nn Ss")
 s = s & ".xls"
 ActiveWorkbook.SaveCopyAs
 End Sub

Open in new window

Here's error I get:

Other people ran into a problem adding the date name at:
http://stackoverflow.com/questions/32138908/run-time-error-1004-method-saveas-of-object-workbook-failed

Joe Howard

Try:

Sub SaveCopyOfFile()
    Dim s As String
    Const FileLocation As String = "C:\UnderDevelopment\"
    s = FileLocation & Split(ActiveWorkbook.Name, ".")(0)
    s = s & Space(1) & Format(Now, "yyyy mm dd Hh Nn Ss")
    s = s & ".xls"
    ActiveWorkbook.SaveCopyAs
 End Sub

Open in new window

Guy Hengel [angelIII / a3]

ActiveWorkbook.SaveCopyAs needs to be
ActiveWorkbook.SaveCopyAs(s)
so you pass the file name

if that still errors, we need to see the value of "s"

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alex Campbell

ASKER

Thanks for the work. It looks like you are very close, but just a couple of questions:
If I am using .xlsx what should the format number be?

I found an interesting discussion of format numbers up at http://www.rondebruin.nl/win/s5/win001.htm, but don't understand
how to update this code for using .xlsx.

Sub SaveCopyOfFile()
Dim s As String
Const FileLocation As String = "C:\UnderDevelopment\"
s = FileLocation & Split(ActiveWorkbook.Name, ".")(0)
s = s & Space(1) & Format(Now, "yyyy mm dd Hh Nn Ss")
s = s & ".xls"
ActiveWorkbook.Sheets.Copy
ActiveWorkbook.SaveAs Filename:=s , fileformat:=56
ActiveWorkbook.Close
End Sub

SOLUTION