How to do a "Save As" in vba code, saving my current Excel workbook with datestamp? How to do a "Save As" in vba code, saving my current Excel workbook with datestamp? vba vba

How to do a "Save As" in vba code, saving my current Excel workbook with datestamp?


Most likely the path you are trying to access does not exist. It seems you are trying to save to a relative location and you do not have an file extension in that string. If you need to use relative paths you can parse the path from ActiveWorkbook.FullName

EDIT:Better syntax would also be

ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal


Easiest way to use this function is to start by 'Recording a Macro'. Once you start recording, save the file to the location you want, with the name you want, and then of course set the file type, most likely 'Excel Macro Enabled Workbook' ~ 'XLSM'

Stop recording and you can start inspecting your code.

I wrote the code below which allows you to save a workbook using the path where the file was originally located, naming it as "Event [date in cell "A1"]"

Option ExplicitSub SaveFile()Dim fdate As DateDim fname As StringDim path As Stringfdate = Range("A1").Valuepath = Application.ActiveWorkbook.pathIf fdate > 0 Then    fname = "Event " & fdate    Application.ActiveWorkbook.SaveAs Filename:=path & "\" & fname, _        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=FalseElse    MsgBox "Chose a date for the event", vbOKOnlyEnd IfEnd Sub

Copy the code into a new module and then write a date in cell "A1" e.g. 01-01-2016 -> assign the sub to a button and run. [Note] you need to make a save file before this script will work, because a new workbook is saved to the default autosave location!


It could be that your default format doesn't match the file extension. You should specify the file format along with the filename, making sure the format matches the extension:

With someWorkbook.SaveAs "C:\someDirector\Awesome.xlsm", fileformat:=xlOpenXMLWorkbookMacroEnabledEnd With

OTOH, I don't see an extension on your .SaveAs filename. Maybe you need to supply one when doing this programmatically. That makes sense--not having to supply an extension from the GUI interface is convenient, but we programmers are expected to write unambiguous code. I suggest adding the extension and the matching format. See this msdn page for a list of file formats. To be honest, I don't recognize a lot o the descripions.

xlExcel8 = 56 is the .xls format

xlExcel12 = 50 is the .xlsb format

xlOpenXMLWorkbook = 51 is the .xlsx format

xlOpenXMLWorkbookMacroEnabled = 52 is the .xlsm format

xlWorkbookDefault is also listed with a value of 51, which puzzles me since I thought the default format could be changed.