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.