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

Question

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    amp  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

User · Answer

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:=xlOpenXMLWorkbookMacroEnabled
End 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.

User · Answer

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 Explicit  Sub SaveFile    Dim fdate As Date Dim fname As String Dim path As String  fdate   Range  A1   Value path   Application ActiveWorkbook path  If fdate  gt  0 Then     fname    Event    amp  fdate     Application ActiveWorkbook SaveAs Filename  path  amp       amp  fname            FileFormat  xlOpenXMLWorkbookMacroEnabled  CreateBackup  False Else     MsgBox  Chose a date for the event   vbOKOnly End If  End 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

User · Answer

I know this is an old post but I was looking up something similar    I think your issue was that when you use Now    the output will be  6 20 2014     This an issue for a file name as it has     in it  As you may know  you cannot use certain symbols in a file name   Cheers

User · Answer

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

User · Answer

Dim NuevoLibro As Workbook Dim NombreLibro As String     NombreLibro    LibroPrueba   ---Creamos nuevo libro y lo guardamos     Set NuevoLibro   Workbooks Add         With NuevoLibro              SaveAs Filename  NuevaRuta  amp  NombreLibro  FileFormat  52         End With                                                                                                                                             valores para FileFormat                                                           xlsx   51   52 for Mac                                                            xlsm   52   53 for Mac                                                            xlsb   50   51 for Mac                                                            xls   56   57 for Mac

User · Answer

I was struggling  but the below worked for me finally   Dim WB As Workbook  Set WB   Workbooks Open    users path Desktop test xlsx    WB SaveAs fileName     users path Desktop test xls             FileFormat  xlExcel8  Password      WriteResPassword                ReadOnlyRecommended  False  CreateBackup  False

User · Answer

I successfully use the following method in one file   But come up with exactly the same error again    Only the last line come up with error  Newpath   Mid ThisWorkbook FullName  1     Len ThisWorkbook FullName  - Len ThisWorkbook Name    amp       amp   ABC -    amp  Format Date   dd-mm-yyyy    amp    xlsm  ThisWorkbook SaveAs  Newpath

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

Examples related to excel

Examples related to excel-2007

Examples related to archive

Examples related to save

Examples related to vba