asked on

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

Badotz

!) Record a new macro
2) "Save As" the file
3) Stop recording
4) Open the module and view the VBA code

Wayne Taylor (webtubbs)

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

martywal

ASKER

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....

Wayne Taylor (webtubbs)

Does simply using this work?

ActiveWorkbook.SaveAs Filename:= Range("A1").Value & ".xls"

Badotz

1) Record a new macro
2) "Save As" the file
3) Stop recording
4) Open the module and view the VBA code

martywal

ASKER

No, doesn't work either....
:-~

Wayne Taylor (webtubbs)

Badotz,

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

Wayne

Wayne Taylor (webtubbs)

Try this....

ThisWorkbook.SaveAs Filename:= Range("A1").Value & ".xls"

SOLUTION

. .

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GrahamSkan

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",

Badotz

YES BUT THE CODE IS GUARANTEED TO WORK!!!!!

martywal

ASKER

OK Guys, looks like I've almost cracked it now.
Will just clean up and get back to.
Thanks so much

martywal

ASKER

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
:-)

webavt

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