Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE
2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA to save a file based upon the date
Resolved · Medium Priority · Version 2007
Ryan has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
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
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
|
Excel tip:Turn Function tooltips on and offExcel 2002 (XP) and Excel 2003 have the Function tooltips facility. When you type in a function name followed by a bracket, for example, =IF(, a yellow box appears beside the function name and lists the function's arguments. This is very useful when you can't quite remember the order of a function's arguments or what the arguments actually are! |
We'll call during UK business hours
Server loaded in 0.07 secs.