vba save file based

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

TrustPilot

starstarstarstarstar Excellent

  • Home
  • Courses
  • Promotions
  • Schedule
  • Formats
  • Our Clients

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

resolvedResolved · Medium Priority · Version 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:

Turn Function tooltips on and off

Excel 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!

However, Function tooltips can become annoying. To turn them off, choose Tools|Options. and select the General tab. Then, untick the Function tooltips box and choose OK.

View all Excel hints and tips


Server loaded in 0.07 secs.