'ThisWorkbook.SaveAs Path & Filename' getting RUN-TIME ERROR 429

[betapeg]

The following code is designed to save an excel file by naming it whatever is in cell B1 & today's date to the specified directory. The code stops at the line colored red and returns a "RUN-TIME ERROR 429 - ActiveX COMPONENT CAN'T CREATE OBJECT". I am assuming a missing dll or something in Tools - References not being checked, but dunno how to fix dll or what needs to be checked in References. Any help would be much appreciated.

==============================================

Sub SaveAs()

Dim Filename As String

Filename = Sheets("MODEL").Range("B1").Text
Filename = Filename & " " & Format(Date, "yyyy-mm-dd") & ".xlsm"

Path = "C:\Users\Name\Documents\Save Here\Results\"

ThisWorkbook.SaveAs Path & Filename

End Sub

 

[betapeg]

You do not have path separator "\" at the end of your path.

Same 429 error code.

Sub SaveAs()

    Dim Filename As String


    Filename = Sheets("MODEL").Range("B1").Text
    Filename = Filename & " " & Format(Date, "yyyy-mm-dd") & ".xlsm"


    Path = "C:\Users\Nathan\Documents\Variable Annuities\Results\"


    ThisWorkbook.SaveAs Path & Filename


End Sub

 

[betapeg]

One more thing - maybe in the filename you have one or more of the characters that are not allowed in the filename.

The name is cell B1 is...

Perspective L

No special characters other than a "space" between Perspective and L.

 

[Blade Hunter]

Another thing, saving as XLSM you may need to specify it is a macro enabled workbook format.

Let me know the result of the code I posted before.

 

[betapeg]

Please run this for me:

Sub Test()
    Dim MyPath as string, MyFilename As String
    MyFilename = Sheets("MODEL").Range("B1").Text & " " & Format(Date, "yyyy-mm-dd") & ".xlsm"
    MyPath = "C:\Users\Nathan\Documents\Variable Annuities\Results"
    If Not Dir(MyPath, vbDirectory) = vbNullString then
        debug.print "Folder Exists"
    Else
        debug.print "Folder does NOT exist"
    End if
    debug.print MyFileName
'    ThisWorkbook.SaveAs MyPath & MyFilename
End Sub

then enter the debug window with CTRL-G and copy the results then paste into a reply

CTRL G. I don't know if this is an output of what you had me run. In fact, when I click run, nothing seems to happen. When I click some other macro, it runs.

Folder Exists
Perspective L 2015-01-26.xlsm
Folder Exists
Perspective L 2015-01-26.xlsm
Folder Exists
Perspective L 2015-01-26.xlsm

 

[betapeg]

Another thing, saving as XLSM you may need to specify it is a macro enabled workbook format.

Let me know the result of the code I posted before.

When I hit run, nothing seems to happen. Also, should I place a "\" right after Results in the code you posted for me? I tried both with and without the "\" right after the Results word.

 

[Blade Hunter]

Try this:

Sub Test()
    Dim MyPath as string, MyFilename As String
    MyFilename = Sheets("MODEL").Range("B1").Text & " " & Format(Date, "yyyy-mm-dd") & ".xlsm"
    MyPath = "C:\Users\Nathan\Documents\Variable Annuities\Results\"
    ActiveWorkbook.SaveAs Filename:=MyPath & MyFilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

 

[Blade Hunter]

When I hit run, nothing seems to happen. Also, should I place a "\" right after Results in the code you posted for me?

No those results you posted were fine . All it does is send output to that debug window. It appears to do nothing unless you have that window open

 

[betapeg]

Try this:

Sub Test()
    Dim MyPath as string, MyFilename As String
    MyFilename = Sheets("MODEL").Range("B1").Text & " " & Format(Date, "yyyy-mm-dd") & ".xlsm"
    MyPath = "C:\Users\Nathan\Documents\Variable Annuities\Results\"
    ActiveWorkbook.SaveAs Filename:=MyPath & MyFilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Wow, you're amazing. It saved to the directory perfectly. Man, I don't know how else to thank you. Thank you so much :D I spent all day trying to fix this (now 5:15PM). Now I can go home ^_^

 

[Blade Hunter]

Glad I could help