Solved

Runtime Error '1004' - SaveFileAs

Posted on 2007-11-19
15
1,160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

730 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