CODEWITHSUNDEEP
excelvba
Doug Coats
Doug Coats

Reputation: 7107

Run Time Error When Saving NewWorkbook

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

Upvotes: 0

Views: 59

Answers (1)

user6432984
user6432984

Reputation:

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

Upvotes: 1

Related Questions