?
Solved

Runtime Error '1004' - SaveFileAs

Posted on 2007-11-19
15
Medium Priority
?
1,162 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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