Save File with Date
To save your file in VBA with today’s date as part of the name, there are basically two functions that you need to know: Date and Format.
ThisWorkbook.SaveAs “C:\My Documents\MyFile” & Format(Date, “yyyymmdd”) & “.xls”
The resulting filename, if you save it today, will be MyFile20040421.xls.
The Date function returns the current system date. The Format function converts the date to whatever format you want using y’s, m’s and d’s.
Lance:
i’m attepting to insert a function just like this in personal.xls. However, the problem is that personal.xls saves itself with a stamp but i really want it to save the workbook that is active when i kick off the macro. any easy way to make that happen?
23 April 2004, 11:39 amDick:
Lance
Change ThisWorkbook to ActiveWorkbook, or you can call out the workbook specifically like
Workbooks(”MyBook.xls”).SaveAs
23 April 2004, 8:29 pmJeff:
Is there a way to take the contents of a cell, and make that the filename when a saveAs gets done?
17 August 2004, 4:08 pmSiddiq Chaudhry:
Need experts help, I need to save a chart/plot with current date,time & year as stamp. Long time back it was available in Header Format, whats is procedure now in Excel 2002, XP environtment.
Thanks, Siddiq
4 November 2004, 7:10 amJake:
I am trying to use the VBA Date function in Excel 2003, but am getting the error “Can’t find project or library” — I can’t believe Date is not a standard VBA function, but regardless, I can’t figure out which library to install.
28 January 2005, 12:11 pmJon Peltier:
Jake -
It’s not uncommon when you have a missing reference for a different module to catch the errors. For me, it’s usually the Strings module that’s the canary in the coal mine. For you this time it’s DateTime.
Go to References on the Tools menu, and look for any reference that says “MISSING” next to it. Clear the checkbox, and if you need the reference, try to browse to find it again.
A quick and dirty fix, in the meantime, is to qualify the errant keyword with its parent module name (e.g., DateTime.Date). It doesn’t fix the problem, just sidesteps it.
- Jon
28 January 2005, 8:38 pmTom:
Fantastic…just what i needed!
Thanks!
Tom
2 June 2005, 5:07 amKevin Mullen:
I needed to save my workbook with date and time in the file name.
I used this code:
Private Sub SaveDatabaseButton_Click()
ActiveWorkbook.SaveAs Filename:= _
“F:\User Form for Lessons Learned\Lessons Learned ” _
& Format(Date, “yyyymmdd “) & Format(Time, “hh.mm.ss”) & “.xls”, _
FileFormat:=xlNormal, Password:=”", WriteResPassword:=”", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
When I click the SaveDatabaseButton, it saves the file with a name like this:
Lessons Learned 20050705 15.35.01.xls
You beaut!
5 July 2005, 2:26 amThanks for giving me the clue about how to do this.
John Western:
Heart felt thank you to all concerned. I have been wanting a button macro that will save by date and time for so long. I found this site by googling, and have added it to my favourites.
Thanks again folks.
Don’t it just make your day?
28 September 2006, 1:18 amNancy:
Thank you! That was the perfect solution for vba in Access working with a Word doc.
10 November 2006, 7:33 amMarvin:
Awesome! This saved me a lot of time at work today! Thanks!
2 March 2007, 12:58 pmXanos3001:
Thank you Kevin Mullen !
10 April 2007, 12:14 pmWorks perfectly.
polly12:
Could you pls help me? I used this code but it shows this error:
Compile error: Expected: line number or label or statement or end of statement
for the part that i write the file name and in particular for the two points after C, C:My Documents…
Could you pls write me the whole code?
I type:
Sub Speichern()
25 April 2007, 9:10 amActiveWorkbook.SaveAs Filename:= _
“C:\Documents and Settings\My Documents\Point” _
& Format(Date, “yyyymmdd “) & Format(Time, “hh.mm.ss”) & “.xls”, _
FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
Dick Kusleika:
polly12: Your code worked for me. That error generally means you forgot some quotes somewhere. It sees the colon (:) and expects a label.
25 April 2007, 12:19 pmpolly12:
Thanks for answering!
But then why doesnt it work for me? I am not such good in Codes, should i have some prerequisites in my folder that i have not considered?
26 April 2007, 3:55 amDick Kusleika:
polly12: You can send the workbook to dkusleika@gmail.com if you like and I’ll look at it.
26 April 2007, 6:41 ampolly12:
Ok i have sent it!
Thanks again!
27 April 2007, 12:18 amTMaz:
insert a module, paste the code below and call your procedure Speichern
Public Declare Function WNetGetUser Lib “mpr.dll” Alias “WNetGetUserA” (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long
Function Username() As String ‘returns the network userid
Dim X As String
Dim User As String
Dim leng As Long
Dim Y As Long
Dim TestChar As String
Dim StringCheck1 As Boolean
Dim StringCheck2 As Boolean
Dim TempUserName As String
On Error GoTo oops:
User = Space(255)
leng = Len(User)
Y = WNetGetUser(X, User, leng)
Username = Trim(User)
Username = UCase(Left(Username, 7))
TempUserName = “”
For i = 1 To 7
TestChar = “”
TestChar = Mid(Username, i, 1)
StringCheck1 = TestChar Like “[A-Z]”
StringCheck2 = TestChar Like “#”
If StringCheck1 = True Or StringCheck2 = True Then
TempUserName = TempUserName & TestChar
End If
Next i
Username = “”
Username = UCase(Trim(TempUserName))
Exit Function
oops:
Username = “XXXXXXX”
End Function
Public Sub Speichern()
Dim strFileName As String
strFileName = “C:\Documents and Settings\” & Username & “\My Documents\Point” & Format(Date, “yyyymmdd “) & Format(Time, “hh.mm.ss”) & “.xls””
ActiveWorkbook.SaveAs strFileName
End Sub
28 April 2007, 7:51 ampolly12:
Thank you very very much!!!
30 April 2007, 12:32 amBrad:
I need help. I dont know much about macros. All I know is that if I am in excell I can click in create macro and then click on items and it will create something that will automatically do it. We sell car washes and our desktop interfaces with the car wash, and captures the numbers in another program. The manufactor wrote an excell sheet that when opened it automatically goes out and pulls these numbers over to excell. I am trying to create a macro that will automaticlly run his excell sheet ( that pulls that days numbers into it) and then saves it by the date. Basically I want to create a daily record of the numbers, by running his excell sheet which automatically pulls the current days numbers, and I want to save that sheet to todays date. He is going to have his software reset his numbers at midnight, so I thought if I could write a macro and put it in the schedueler to run at 11:50p.m. then I could capture the sheet, save it by that day and then his numbers would reset at midnight and then do it all over again the next day, therefore giving a folder full of excell sheet for each day. Can anybody help. You may have to spell it out exaclty, becasue I dont know much about macros!!!!
6 September 2007, 8:14 amThanks!!
james:
hi, i have just been trying this on excel 2003. for me, it is definietly CASE-SENSITIVE, so yyyymmdd doesn’t work, but YYYYMMDD does the trick.
31 August 2008, 8:50 pmmykL:
hi, i’ve used the code below and need the output “filename_20081010.xls” but the filename that appears is “filename_10/10/2008.xls”
can anybody help?
SaveFile1:
10 October 2008, 12:27 amSavePath = Application.GetSaveAsFilename(InitialFileName:=”filename” & Format(Date, yyyymmdd) & “.xls”, filefilter:=”Microsoft Excel Files (*.xls), *.xls”, Title:=”Save Path”)
If SavePath = False Then
MsgBox (”Please browse desired path to save your file” & Chr(10) + Chr(13) & “and enter your desired filename”)
GoTo SaveFile1
Else
Workbooks.Application.ActiveWorkbook.SaveAs SavePath
End If
MsgBox (”Your file has been successfully saved.”)
Dave:
To myKL:
Use:
Format(Date, “yyyymmdd”)
10 October 2008, 7:43 pm