'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

 

[Blade Hunter]

What is in cell B1 on the sheet "MODEL"?

I also wouldn't use variables like Path and Filename as they are reserved words and will give you issues, make them MyFileName and MyPath. Also don't forget to dim Path (Or MyPath as I suggested)

 

[RatExcel]

I bet the path you want to save to does not exist.

 

[Blade Hunter]

I bet the path you want to save to does not exist.

Nice Catch, I didn't even think of that

 

[betapeg]

Can you take my code and change it as you suggested? I can change Filename and Path to what you said, but kind of don't understand the dim Path.

Cell B1 has the name of the data set. The formula inside of cell B1 is

=DATA!A1

The DATA worksheet is nothing but values.

 

[betapeg]

I thought that might have been the issue, but double-checked, triple-checked the path and it is correct. This code was actually working perfectly a couple days ago.

 

[betapeg]

What is in cell B1 on the sheet "MODEL"?

I also wouldn't use variables like Path and Filename as they are reserved words and will give you issues, make them MyFileName and MyPath. Also don't forget to dim Path (Or MyPath as I suggested)

Tried this out as you said. Still getting the 429 error.

Sub Test()

    Dim MyFilename As String


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


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


    ThisWorkbook.SaveAs MyPath & MyFilename




End Sub

 

[RatExcel]

Tried this out as you said. Still getting the 429 error.

Sub Test()

    Dim MyFilename As String


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


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


    ThisWorkbook.SaveAs MyPath & MyFilename




End Sub

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

 

[Blade Hunter]

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

 

[RatExcel]

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