Runtime Error '1004' - SaveFileAs

Hi Experts,
Any idea what is wrong with this code:

'Save File AS
ActiveWorkbook.SaveAs Filename:= _
Range("A1").Text & ".xls", _
FileFormat:=xlWorkbookNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

I'm getting a Runtime Error '1004'
Application-defined or Object-defined Error.
I'm running this in Excel 2003, and have been playing around with it for ages any help appreciated.
Regards

Martywal
martywalAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
....and if that fails, what does this return?

MsgBox Range("A1").Value & ".xls"
0
 
BadotzCommented:
!) Record a new macro
2) "Save As" the file
3) Stop recording
4) Open the module and view the VBA code
0
 
Wayne Taylor (webtubbs)Commented:
Hi martywal,

try using the Value property instead....
     
     'Save File AS
     ActiveWorkbook.SaveAs Filename:= _
     Range("A1").Value & ".xls", _
     FileFormat:=xlWorkbookNormal, Password:="", WriteResPassword:="", _
     ReadOnlyRecommended:=False, CreateBackup:=False
     
Regards,

Wayne
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
martywalAuthor Commented:
Wayne,
Same problem...
Have tried different paths etc etc.
Using paths instead of referring to a cell with full path and filename but doesn't want to work.
I've done this many times but can't find the issue this time....
0
 
Wayne Taylor (webtubbs)Commented:
Does simply using this work?

ActiveWorkbook.SaveAs Filename:= Range("A1").Value & ".xls"
0
 
BadotzCommented:
1) Record a new macro
2) "Save As" the file
3) Stop recording
4) Open the module and view the VBA code
0
 
martywalAuthor Commented:
No, doesn't work either....
:-~
0
 
Wayne Taylor (webtubbs)Commented:
Badotz,

That's all good and well, but that will result in code remarkably similar to what Martywal already had.

Wayne
0
 
Wayne Taylor (webtubbs)Commented:
Try this....

ThisWorkbook.SaveAs Filename:= Range("A1").Value & ".xls"
0
 
Paul PayneConnect With a Mentor Managing ConsultantCommented:
You could try assigning the file name and then use that in the save as statement.  See below

Sub tstSave()
Dim filename As String

'Assign filename
filename = Range("A1").Text & ".xls"

'Save file as
ActiveWorkbook.SaveAs filename:= _
filename, _
FileFormat:=xlWorkbookNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub
0
 
GrahamSkanRetiredCommented:
What is the text value of the cell which you think has the file path?

Try printing the text (file name?) to the immediate window and then saving the file manually with that  text.
Debug.Print Range("A1").Text & ".xls",
0
 
BadotzCommented:
YES BUT THE CODE IS GUARANTEED TO WORK!!!!!
0
 
martywalAuthor Commented:
OK Guys, looks like I've almost cracked it now.
Will just clean up and get back to.
Thanks so much
0
 
martywalAuthor Commented:
This is what it looked like in the end:
Dim filename As String

'Assign filename
filename = Workbooks("IncomeUpd").Worksheets("Summary").Range("A1").Value
                   
'Save File AS
ActiveWorkbook.SaveAs filename:=filename, _
FileFormat:=xlWorkbookNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The problem was that I was referring to the wrong workbook.
Thanks for your help
:-)
0
 
webavtCommented:
I was working on a similar project converting 2003 workbooks into 2007. You may want to change your variable name.

Dim filename As String

The code below already includes the word 'filename' and having a variable with the same name messes it up. If you don't believe me run it through debug mode and you'll see it's assigning the value of the variable to this code segment...

ActiveWorkbook.SaveAs filename:=filename
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.