CODEWITHSUNDEEP
vbaexcel
Clauric
Clauric

Reputation: 1886

Error returned by SaveAs with variable file name/location

I am trying to use the SaveAs functionality to save a spreadsheet to another location. When I hardcode the name of the file, the file saves fine.

However, as I could be saving about 50 of these files a day, I need to be able to distinguish between the files saved. I tried to set part of the file name to be variable, depending on the timestamp when the file was saved. (yes, I know that I could sort the folder by the date/time saved, but these files are also for use by multiple people, who may not know how to use the functionality in that manner).

This throws up an error of

Run-time error '1004' Method 'SaveAs' objct '_Workbook' failed

The code is:

Set wb = Workbooks.Add
ThisWorkbook.Sheets("Holdings_Summary").Copy Before:=wb.Sheets(1)

x = 2
k = Sheets.Count

While x <= k
    Sheets(x).Delete
    k = Sheets.Count
Wend

z = Format(DateTime.Now, "hh:mm dd:MM:YYYY")
wb.SaveAs "J:\A_Drive\Recon folders\Holdings" & z & ".xlsx"
Workbooks("test1.xlsx").Close savechanges:=True

Upvotes: 0

Views: 960

Answers (2)

Pudding
Pudding

Reputation: 97

As far as i know, the file names cannot contain ":" or ";". So the only mistake i see on your code is

z = Format(DateTime.Now, "hh:mm dd:MM:YYYY")

I tried the code below and it works fine;

Z = Date
z2 = "D:\Users\B_PAMUK\Desktop\" & Z & ".xlsm"
ActiveWorkbook.SaveAs Filename:=z2

You can implement the hours with getting hours and minutes parts and connecting them with dots. I hope that helps ;)

Upvotes: 1

beercohol
beercohol

Reputation: 2587

You can't use a colon in a filename!

Change your "Format" to this:

z = Format(DateTime.Now, "hh-mm dd-MM-YYYY")

Or whatever character you prefer instead of "-"

If you want the names to sort nicely then use YYYY-MM-dd format:

z = Format(DateTime.Now, "YYYY-MM-dd hh-mm")

See here for info on Windows file naming conventions, particularly the section on reserved characters: https://msdn.microsoft.com/en-gb/library/windows/desktop/aa365247(v=vs.85).aspx

Specifically, the colon is reserved for device names, usually drive letters or printers/serial ports.

Upvotes: 1

Related Questions