Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

STL - Formerly Best Training Solutions Through Learning
TrustPilot
Excellent
Request Callback We will call you back
0207 987 3777 Call for assistance
Your Basket Basket is empty
vba save file based

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA to save a file based upon the date

VBA to save a file based upon the date

ResolvedVersion 2007

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

Excel tip:

Excel 2010 Shortcuts - Start and End of the Worksheet

Did you know you can quickly move to either the beginning or the end of an Excel Worksheet using just a couple of keys on the keyboard?

Press Ctrl + Home key to move the cursor to cell A1

Press Ctrl + End key to move the cursor to the end of the current worksheet.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.