Ryan has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
VBA to save a file based upon the date
Good afternoon,
I have written a piece of code to save a file based upon the current date (both the directory and file name are affected).
Whenever I run the macro however, I am given a runtime error '1004' and the message that the file has not been saved. When I debug, it is the final line of my code which is highlighted, but I cannot ascertain the error - please can you help?
Sub savefile()
Dim year As String
Dim path As String
Dim month As String
Dim month2 As String
Dim month3 As String
year = DatePart("yyyy", Date)
month = Format(Now, "mm")
month2 = MonthName(month)
month3 = Format(Now, "ddmmyy")
ActiveWorkbook.SaveAs Filename:="F:\DOCS\Finance\PRIVATE\Sales Ledger\Ledger\" & year & "\" & month & " " & month2 & "\Ledger" & " " & month3 & ".xlsx"
End Sub
* were this to be saved today for example, the correct file path and name would be:
F:\DOCS\Finance\PRIVATE\Sales Ledger\Ledger\2012\01 January\Ledger 250112.xlsx
Kind thanks,
Ryan
RE: VBA to save a file based upon the date
Hi Ryan,
Thank you for your question.
Try this code:
Sub SaveIt()
Dim dt As String, wbNam As String
wbNam = "Apple_"
dt = Format(CStr(Now), "yyy_mm_dd_hh_mm")
ActiveWorkbook.SaveAs Filename:=wbNam & dt
End Sub
CStr converts the current date to a string. You could replace the Filename input with an Inputbox or just use the current workbook name concatenated with the date variable.
I hope this helps.
Regards
Simon
