Save File with Date

Posted on by Dick Kusleika

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 DocumentsMyFile" & 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.

50 thoughts on “Save File with Date”

  1. 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?

  2. Lance

    Change ThisWorkbook to ActiveWorkbook, or you can call out the workbook specifically like

    Workbooks(“MyBook.xls”).SaveAs

  3. Is there a way to take the contents of a cell, and make that the filename when a saveAs gets done?

  4. 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

  5. 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.

  6. 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

  8. 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 LearnedLessons 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!
    Thanks for giving me the clue about how to do this.

  9. 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?

  13. 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()
    ActiveWorkbook.SaveAs Filename:= _
    “C:Documents and SettingsMy DocumentsPoint” _
    & Format(Date, “yyyymmdd “) & Format(Time, “hh.mm.ss”) & “.xls”, _
    FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
    , CreateBackup:=False
    End Sub

  15. 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?

  18. 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 DocumentsPoint” & Format(Date, “yyyymmdd “) & Format(Time, “hh.mm.ss”) & “.xls””
    ActiveWorkbook.SaveAs strFileName

    End Sub

  20. 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!!!!
    Thanks!!

  21. 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.

  22. 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:
    SavePath = 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.”)

  24. Hi,

    I created the following macro:

    Sub Do_Nothing()
    ‘
    ‘ Do_Nothing Macro
    ‘ Macro recorded 10/22/2008 by Scott Henry Harris
    ‘
    ‘ Keyboard Shortcut: Ctrl+b
    ‘
    ActiveWorkbook.SaveAs Filename:=(“Offering_Sheet_”) & Format(Date, “yyyymmdd”) & Format(Time, “hh.mm.ss”) & “.xls”, _
    FileFormat:=xlNormal, Password:=”password”, WriteResPassword:=””, _
    ReadOnlyRecommended:=False _
    , CreateBackup:=False
    End Sub

    And received the following error:
    Runtime error 424: Object Required

    Can anyone help me with this?

    Thanks

  26. Hello all. I too am in need of some assistance. I am using Excel 2003 SP3. I tried the following code

    Private Sub Save()
    ActiveWorkbook.SaveAs Filename:= _
    “\ServerPUBLICTeamFolder” _
    & Format(Date, “YYYYMMDD “) & Format(Time, “HH.MM.SS”) & “.xls”, _
    FileFormat:=xlNormal, Password:=””    , WriteResPassword:=”“, ReadOnlyRecommended:=False _
    , CreateBackup:=False
    End Sub

    As well as this code

    Sub Save()
    ActiveWorkbook.SaveAs “\ServerPUBLICTeamFolder” “& Format(Date, “YYYYMMDD “) & “.xls”, _
    , CreateBackup:=False
    End Sub

    In both cases, I received the following error when the closed quotes were reached in (Date, “YYYYMMDD “)

    Compile error:
    Expected: list separator or )

    Additionally, I do not even think that there is a Time or Now function since if I just try to use them in some code, I receive an error. Is there a specific add-in i need to look for?

    Any assistance would be appreciated.
    Thanks.

  27. Andrew, do you have a space after YYYYMMDD? If so, I think you should get rid of it, like

    Format(Date, “YYYYMMDD”) & ” ” & Format(Time, “hh.mm.ss”) & “.xls”

  29. This looks like exactly what I need but I’m just missing one little piece. Each day I have to run reports for previous days and I was wondering if it was possible to subtract a day from the Date function. So for example today (5/1/09) I need to save the file Report 043009.xls. Is there any way to do something like a Date – 1?

  30. I am looking to save each file by a index, if you will. What it is, that I need to save the file by the name of a value in a field in my excel file. Say this value is stored in cell B7, let it be 8176. I need to be able to save the file as Filename-8176.xls. Please help me on how can this be acheived.
    Also, if I need to call this file out from a folder having many files (naming convention same as above), by a value in another field, say C7, let it be 8177.
    I am a student working on a project, to optimize rectangular panels in an area filled with flaws which cannot be used within th panels. I need to be able to call out all the files an analyze them for the optimization (nesting). The flaws are mapped using excel given the location of the flaws, and each piece is given a number, this number is B7, so its unique and I need it to call out the map when running the optimization.
    I am not great at coing, but do fine with help tool and googling my way through the syntax, etc. I have the logic in mon and dont think coding that will be a problem. But I do need some way to save the excel file by a unique no. which is taken from a field in the workbook being saved.
    Any help will be greatly appreciated. Thanks.

  31. I am looking to save each file by a index, if you will. What it is, that I need to save the file by the name of a value in a field in my excel file. Say this value is stored in cell B7, let it be 8176. I need to be able to save the file as Filename-8176.xls. Please help me on how can this be acheived.
    Also, if I need to call this file out from a folder having many files (naming convention same as above), by a value in another field, say C7, let it be 8177.
    I am a student working on a project, to optimize rectangular panels in an area filled with flaws which cannot be used within th panels. I need to be able to call out all the files an analyze them for the optimization (nesting). The flaws are mapped using excel given the location of the flaws, and each piece is given a number, this number is B7, so its unique and I need it to call out the map when running the optimization.
    I am not great at coing, but do fine with help tool and googling my way through the syntax, etc. I have the logic in mon and dont think coding that will be a problem. But I do need some way to save the excel file by a unique no. which is taken from a field in the workbook being saved.
    Any help will be greatly appreciated.

  33. Hello I tried to using different code and I keep getting errors when I run the macro. I just want to hit a save button that I created and have a file name with text from Cell L28 and the current date and time .xls. I would also like it to save to a specific folder.

    Does anybody know how I can do this?

    Thanks,

    Chris

  34. This is the folder I want to save the files to

    C:Documents and SettingsChris.CHRIS-DEC7A5D2EDesktopCryoStuff

    When I click on the save file button I want the save as screen to automatically pop up with this folder open with the file name already there.

    File name would be L28_Date_Time.XLS

    Thank you very much

    Chris

  35. Hi,

    To the people who said that it didn’t work and they got the following error:

    Compile error: Expected: line number or label or statement or end of statement

    I had that and when I had copied and pasted it had weird quotation marks that looked like they were in italics. I simply deleted those and replaced them with normal quotation marks and it worked perfectly.

  37. Hi there everyone,
    Need a little assist. How can i save a xls file with date as part of the filename but most of all saved with Autofit Selected in VB code. The problem is,everytime i open the file i have to do a autofit seletion. I can’t seem to work it out. Thanks in advance to anyone.

  38. Hi Steve,

    as far as saving with the date, you can use the code shown above. For the autofit, you could add something along the lines of Worksheets("Sheet1").Columns("C:I").AutoFit in the Workbook_BeforeSave event or in the Worksheet_Activate event

  39. Olá Steve blz..

    Amigo fiz uma planilha no excell que tudo que faz nela ela salva automatico sem necessidade de esta na frente do pc
    Só que preciso que tenha uma linha de programação que faz salvar os dados em um determinado local com data e hora.
    Ex; C: meusprojeto
    Pode me ajudar por favor.
    Parabéns pelo Site ,muito bomm.

  40. Hey Everyone…VERY new to Excel macros, much less VB, so I have a question related to having a macro save a workbook with a specific filename based on the current date. Here is my code so far…

    Windows(“Graph2.txt”).Activate
    Sheets(“Graph2?).Select
    Sheets(“Graph2?).Move After:=Workbooks(“Graph1.txt”).Sheets(1)
    Windows(“Graph3.txt”).Activate
    Sheets(“Graph3?).Select
    Sheets(“Graph3?).Move After:=Workbooks(“Graph1.txt”).Sheets(2)
    Windows(“Graph4.txt”).Activate
    Sheets(“Graph4?).Select
    Sheets(“Graph4?).Move After:=Workbooks(“Graph1.txt”).Sheets(3)

    What this does is takes 4 individual data (.txt) files (that are already opened with another macro and have dedicated file names of Graph1.txt, Graph2.txt, Graph3.txt, and Graph4.txt) and combines them into one workbook called Graph1.txt, so there are 4 different sheets in one file instead of 4 individual files. When I insert the code…

    ActiveWorkbook.SaveAs “S:Organized Data” & Format(Now, “yyyymmdd”) & “.xls”

    It saves the file correctly, but also changes the sheet name (I assume the currently active sheet [or as I call them…”tabs”]) of “Graph1? to match the filename (specifically, the first “tab” or sheet is changed from “Graph1? to 20111003, for example). How do I keep the file-saving-code without changing one of the sheet names to the filename? I am sure there are easier ways for me to accomplish some of this stuff, but I do need the sheets to keep there generic names of Graph1, Graph2, Graph3, and Graph4 for use within future macros.
    Thanks
    TJ

  41. TH Phe: Because the file is originally a text file, that’s how Excel will save it. With text files, the *only* sheet is renamed to the same name as the file. If you close the file and reopen, all the sheets except the activesheet will be gone. Here’s what you need.

    ActiveWorkbook.SaveAs “S:Organized Data” & Format(Now, “yyyymmdd”) & “.xls”, xlWorkbookNormal

  42. Thanks, Dick. That’s the stuff. I actually got around the problem by saving as a specific filename in a specific location (i.e. “CurrentData”), then once the other macros have executed, the user is required to save to a specific filename and location based on the date, but I like this better! Thanks!

  43. Can anyone tell me how to upload data in docs.google.com using excel macros? please help me i need it very badly.

    Thanks in advance.

  44. ActiveWorkbook.SaveAs Filename:=(“Offering_Sheet_”) & Format(Date, “Long Date”)& “.xls”

    ActiveWorkbook.SaveAs Filename:=(“Offering_Sheet_”) & Format(Date, “Short Date”)& “.xls”

  45. Thank you so much, I was struggling with naming my CSV file and this has helped me to resolve my issue.

    Sarah

  46. Hi All nice thred i am trying to save a file to a sub folder on a drive C:\Backup

    every time i save it it goes to root of c: with name beginning drive but not in backup folder.

  48. Hello,

    I receive an email with multiple attachments with varying names. How can i keep just the text and add the date.

    What i receive:

    Share Class A Daily Net Asset Value_20150417_175739236_54724170.xls

    What i would like to save these files as.

    Share Class A Daily Net Asset Value_04172015

  49. Natalia: From within Outlook?

  50. Hi Dick,

    Thank you for your response.

    I have never written a macro so im just trying to get around it.

    I pressed F8 to run the code but nothing happened.

    Im going to play with it and go through each of the steps.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.