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

excel excel-2007 archive save vba
Analytic Lunatic picture Analytic Lunatic · Jun 18, 2013 · Viewed 425.1k times · Source

I have an Excel Workbook that on form button click I want to save a copy of the workbook with the filename being the current date.

I keep trying the the following ActiveWorkbook.SaveAs ("\\filePath\FormFlow To MSExcel\" & Left(Now(), 10)) but am receiving Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed.

Can anyone assist me with this? I'm still very new to developing for Excel.

Answer

chancea picture chancea · Jun 18, 2013

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

Related questions

Append same text to every cell in a column in Excel

How can I append text to every cell in a column in Excel? I need to add a comma (",") to the end. Example: [email protected] turns into [email protected], Data Sample: [email protected] satishmm_2sptc@…

Read More
Closing Excel Application using VBA

I have used the following without success. The active workbook closes, indeed, but the excel window remains open. Application.ActiveWindow.Close SaveChanges:=False ActiveWorkbook.Close SaveChanges:=False Which is the command that terminates the application? EDIT To say a little …

Read More
Excel Date Conversion from yyyymmdd to mm/dd/yyyy

I have been searching for about an hour on how to do this in Excel. I have an Excel file that was created from an old system and I am pulling information from a SQL Server Database, I will be …

Read More