Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA to save a file based upon the date
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
VBA to save a file based upon the date
Resolved · Medium Priority · Version 2007
Reply Wed 25 Jan 2012, 16:51Delegate Ryan said...
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
For upcoming training course dates see: Pricing & availability
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
|