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

chancea · Accepted Answer · 2013-06-18T16:28:15.937

28

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

edited Jun 18 '13 at 16:28

answered Jun 18 '13 at 16:20

chancea

5,858
3
29
39

9

Thanks for the help! Finally got what I was after with the following: `ActiveWorkbook.SaveCopyAs ("\\filePath\Feed Program\FormFlow To MSExcel\Archive\FeedSampleReport-" & Format(Now(), "mmddyyyy") & ".xlsm")` – Analytic Lunatic Jun 18 '13 at 17:55
11

I used your code very successfully. Thanks! One small point: using file format of xlWorkbookNormal saves the file as an Excel 97-2003 workbook. To save as a current .xlsx workbook you have to use xlOpenXMLWorkbook. My code: – Karl Hoaglund Jul 27 '15 at 03:33
3

instead of using the names (like xlWorkbookNormal), use the numbers. Or else you might not be able to compile with an older Excel some times. See http://www.rondebruin.nl/win/s5/win001.htm – Patrick Lepelletier Jan 08 '17 at 02:29
1

For the [xlFileFormat enumeration](https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat) list – danieltakeshi Dec 06 '18 at 16:59
**Use FileFormat:=51** (or FileFormat:=xlOpenXMLWorkbook) to get .xlsx workbook, which is especially important **when working with large amount of data**. – Jakub Holan Dec 25 '21 at 12:20

score 10 · Answer 2 · answered Sep 11 '15 at 12:33

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 > 0 Then
    fname = "Event " & fdate
    Application.ActiveWorkbook.SaveAs Filename:=path & "\" & 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!

riderBill · Answer 3 · 2015-12-14T04:15:24.410

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.

@PatrickLepelletier. Thanks. It just so happened that I was working in VBA at the time and had just figured this out myself. I don't write VBA code often; I'll probably have to refer to my own answer next time around. — riderBill, Jun 29 '16 at 16:46
@riderBill - I'm glad you enumerated all that. I know what a lot of the formats are, but still there's enough confusion over the names of the constants. I don't know if anywhere Microsoft spells out which file format constant goes with which file extension. — Jon Peltier, Dec 01 '16 at 17:54

score 0 · Answer 4 · edited Jul 16 '22 at 03:06

0

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.

edited Jul 16 '22 at 03:06

Ryan M

18,333
31
67
74

answered Jun 20 '14 at 12:25

Corey

1

score 0 · Answer 5 · edited Sep 07 '16 at 10:05

0

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)) & "\" & "ABC - " & Format(Date, "dd-mm-yyyy") & ".xlsm"
ThisWorkbook.SaveAs (Newpath)

edited Sep 07 '16 at 10:05

Konrad

17,740
16
106
167

answered Jan 20 '15 at 23:14

Isu

127
4
15

score 0 · Answer 6 · answered Aug 14 '18 at 13:44

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

score 0 · Answer 7 · answered Apr 09 '20 at 20:40

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 & 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)
                                                    '*****************************

Please add some explanation to your answer such that others can learn from it — Nico Haase, Apr 09 '20 at 21:03

score 0 · Answer 8 · answered Dec 25 '21 at 12:28

0

When working with large amount of data where .xlsx workbook is needed, use the following syntax

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

(For more FileFormats see documentation.)

answered Dec 25 '21 at 12:28

Jakub Holan

303
1
8

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

8 Answers8

Linked