VBA to append current date to file name during Save As

[General Ledger]

Dear All,

I am looking for VBA to do a Save As on the Excel file and append the current date to the end of the file name.

For example, if the file name is "Accounts Payable" and the current date is 18 July 2011, I would like the Save As file name to be "Accounts Payable 20110718" (YYYYMMDD sequence for the date).

Also, if the user selects Cancel from the Save As dialog box, I do not want the macro to return a Run-Time Error 1004 as it does now. I would like it to cancel the Save As operation and continue on in the macro.

Here is my current code:

    'Save file
 
        ChDir "Z:\Excel"
    ActiveWorkbook.SaveAs Filename:="Z:\Excel\ACCOUNTS PAYABLE.xlsx", FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False

I am using Windows 7 and Excel 2010

Thanks,

GL

 

[josh1981]

OK. Thanks, Ill give it a try.

 

[Gilesth]

Dear All,

Hope you can help, I am getting a compile error on all of the examples in this thread? Wrong Number of Arguments or invalid property assignment???

Here is the code I was using, four versions of the same from this thread, none work all give the same error on the format part. the code is lifted from the thread, so unsure why its not working.
Win 7 / Win 8 / Win 10 all running Excel 2010.

Sub Save()
' Save Macro

' ChDir "Z:\Excel\"
' s = ActiveWorkbook.FullName
' ActiveWorkbook.SaveAs Filename:=Left(s, Len(s) - 5) & " " & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=51

' ActiveWorkbook.SaveAs Filename:="Z:\Excel\ACCOUNTS PAYABLE & Format(Date, "yyyymmdd") & ".xlsx", FileFormat _
' :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
' False, CreateBackup:=False

' ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").Range("A4").Value & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=51


' Dim FName As String
' FName = "C:\Users\Owner\Documents\Test Date " & Format(Range("A1"), "YYYY-MM-DD") & ".xlsx"
' ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=51

End Sub

Thanks for your help!

 

[kenchristensen11]

Hi VoG,

This code works wonders!!! How do I tweak the code below so that always saves to the same folder? For instance, I need to always save it in this folder, "S:\Forecast Uploads"

Sub SaveasCSV()
'
' SaveasCSV Macro
'


'
Sheets("Upload File").Select
Sheets("Upload File").Copy
ActiveWorkbook.SaveAs Filename:=Sheets("Upload File").Range("F2").Value & Format(Date, "yyyymmdd") & ".csv", FileFormat:=51

End Sub