Solved: Excel 2003 Saveas Issue

[freybe06]

Hi Everyone,

I know there are a million different posts about this error (and I've ready a ton of them) but nothing seems to solve the issue I'm having...

I want to save a file but every time the macro ends, the new file is named "False" and it doesnt have an extension.

Here is the code:


With ActiveWorkbook
Sheets.Copy
ActiveWorkbook.SaveCopyAs Filename = _
"H:\My Documents\Work\Work Automations\PHD\" & "PHDOutput" & ".xls"
ActiveWorkbook.Close False
.Activate
End With


Like I said, the macro does save a new file to the My Documents\...\ folder, but the document name is "FALSE" with no extension. I double-clicked on the file and manually put ".xls" at the end and it opened fine with all the data I needed inside.

Thanks in advance!

[Kenneth Hobs]

Remove Filename if you are not going to use the := that designates a parameter name.

I see no reason for Sheets.Copy.

[freybe06]

I changed it to:


With ActiveWorkbook
Sheets.Copy
ActiveWorkbook.SaveCopyAs = _
"H:\My Documents\Work\Work Automations\PHD\" & "PHDOutput" & ".xls"
ActiveWorkbook.Close False
.Activate
End With


I'm getting a "Compile Error: Expected function or variable"


Also, the "sheets.copy" is because I'm copying the worksheets from another workbook and saving them without the macro thats in the old workbook.

[Kenneth Hobs]

Remove "=" as that is a procedure and it is not returning anything which is what a function does. As you type that out, intellisense will tell if the syntax is correct though compiling is good to do prior to running code.

ActiveWorkbook.SaveCopyAs "H:\My Documents\Work\Work Automations\PHD\PHDOutput.xls"

[freybe06]

Works great now! Thanks Kenneth!

[Lonewolf]

Hi there i try your code and gave me the same results i modified it a little and it works fine now.

here is the code just change the UserName for your username.

With ActiveWorkbook
Sheets.Copy
.SaveCopyAs "H:\Documents and Settings\UserName\My Documents\Work\Work Automations\PHD\" & "PHDOutput" & ".xls"
ActiveWorkbook.Close False
.Activate
End With

hope this helps.