Solved

Runtime Error '1004' - SaveFileAs

Posted on 2007-11-19
15
1,156 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:martywal
  • 5
  • 4
  • 3
  • +3
15 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 20311661
!) Record a new macro
2) "Save As" the file
3) Stop recording
4) Open the module and view the VBA code
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 20311662
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
 

Author Comment

by:martywal
ID: 20311694
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 20311717
Does simply using this work?

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

Expert Comment

by:Badotz
ID: 20311728
1) Record a new macro
2) "Save As" the file
3) Stop recording
4) Open the module and view the VBA code
0
 

Author Comment

by:martywal
ID: 20311745
No, doesn't work either....
:-~
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 20311746
Badotz,

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

Wayne
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 20311750
Try this....

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

Assisted Solution

by:EuroHammer
EuroHammer earned 200 total points
ID: 20311756
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
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 300 total points
ID: 20311758
....and if that fails, what does this return?

MsgBox Range("A1").Value & ".xls"
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20311769
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
 
LVL 29

Expert Comment

by:Badotz
ID: 20311775
YES BUT THE CODE IS GUARANTEED TO WORK!!!!!
0
 

Author Comment

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

Author Comment

by:martywal
ID: 20311939
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
 
LVL 3

Expert Comment

by:webavt
ID: 24485928
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now