Run Time Error When Saving NewWorkbook

Question

So I have tried a few placement issues and I cant seem to figure out why this works in some of my macros but not my current one? Any pointers?

Private Sub CommandButton1_Click()

Dim filelocation1 As String
Dim wbO As Workbook
Dim wsO as Worksheet


filelocation1 = "C:\Users\Ashleysaurus\Desktop\doug" & "\" & Now() & ".xls"

Set wbO = Workbooks.Add

With wbO
    Set wsO = wbO.Sheets("Sheet1")
    wbO.SaveAs Filename:=filelocation1, FileFormat:=56

Not much code. So, there is not much we can come up with other then suggesting the obvious: are the variables declared correctly `wbO` as `Workbook` and `filelocation1` as `String`? Maybe add `Option Explicit` to avoid typos. Check the existence of the location before using it with [Dir](http://stackoverflow.com/questions/11573914/check-if-the-file-exists-using-vba). Also, should `Sheets(1).Activate` actually be `.Worksheets(1).Activate`? — Ralph, Jun 26 '16 at 21:31
@Ralph, I thought it was OK to leave out the obvious stuff but I guess not. — Doug Coats, Jun 26 '16 at 21:40
Check the directory before using it with `Dir` as suggested before. Do you have only English versions of Excel? If not, then `Sheet1` can be a problem as it converts to different names in different language setups. I'd stick to the original code and use `.Worksheets(1)` instead. One last thing: you can setup Excel 2007+ to automatically start with a new `.xlsx` file. If you want to save it as `.xls` you should get at least an error message. Maybe it helps if you specify the template to use before adding a new Excel file: https://msdn.microsoft.com/en-us/library/office/ff835310.aspx — Ralph, Jun 26 '16 at 21:51
It always helps if you tell us what runtime error you are getting and what line you are getting it on... — MatthewD, Jun 27 '16 at 02:00

score 1 · Accepted Answer · answered Jun 27 '16 at 04:19

1

You cannot use / in a filename

"C:\Users\Ashleysaurus\Desktop\doug" & "\" & Now() & ".xls"

returns: "C:\Users\Ashleysaurus\Desktop\doug\6/26/2016 11:15:54 PM.xls"

Use:

"C:\Users\Ashleysaurus\Desktop\doug" & "\" & Format(Now, "yyyy-mm-dd hh-mm") & ".xls"

returns: "C:\Users\Ashleysaurus\Desktop\doug\2016-06-26 23-15.xls"

Private Sub CommandButton1_Click()

    Dim filelocation1 As String
    Dim wbO As Workbook
    Dim wsO As Worksheet


    filelocation1 = "C:\Users\Ashleysaurus\Desktop\doug" & "\" & Format(Now, "yyyy-mm-dd hh-mm") & ".xls"

    Set wbO = Workbooks.Add

    With wbO
        Set wsO = wbO.Sheets("Sheet1")
        wbO.SaveAs Filename:=filelocation1, FileFormat:=56

    End With
End Sub

answered Jun 27 '16 at 04:19

You might want to elaborate on your post that "it depends". It depends on your country / region setting and also on the file-system you are using to save. Of course, `C:\`` indicates a standard Windows setup. Yet, `/ ` are acceptable under certain circumstances: (1) saving to a Linux / Samba share or (2) saving to a website such as a SharePoint site. – Ralph Jun 27 '16 at 15:28
That's interesting. My answer was rushed and does need editing. It was merely intended to address the OP's question. I'm going to have to give some thought to including general file naming conventions from other platforms. Thank you. – Jun 27 '16 at 15:56
this looks right to me, will makr as answer when i get home and verify my idiocy – Doug Coats Jun 27 '16 at 17:14
Aaaaaaaaaaaaaaaand of course its always the simplest solution. Thanks – Doug Coats Jun 27 '16 at 22:06
Lol...if it was a snake it would have bite you!! – Jun 27 '16 at 23:05

Run Time Error When Saving NewWorkbook

1 Answers1