Solved

Runtime Error '1004' - SaveFileAs

Posted on 2007-11-19
15
1,159 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.

770 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