I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.

I believe the correct file extension for xl2007 workbooks with macros is
..xlsm and unless you specify SaveAs xl2003 format, it will balk at the .xls
three digit extension.

"Elizabeth" wrote:

I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.

FOLDER & "OrderStatusTemp.xls", FileFormat:= _

This is one of the lines I am referring to. There is one more that needs
changing.

FOLDER & "OrderStatusTemp.xlsm", FileFormat:= _


"Elizabeth" wrote:

I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.

I tried your suggestion but I'm now getting an compile error: variable not
defined where I put in xl2007.

"JLGWhiz" wrote:

FOLDER & "OrderStatusTemp.xls", FileFormat:= _

This is one of the lines I am referring to. There is one more that needs
changing.

FOLDER & "OrderStatusTemp.xlsm", FileFormat:= _


"Elizabeth" wrote:

I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.

I changed the file format to xlExcel8 and that fixed the runtime error '1004'
but now I getting a Runtime error '53' on this part of the code:

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xlsm"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xlsm", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xlsm"


"JLGWhiz" wrote:

FOLDER & "OrderStatusTemp.xls", FileFormat:= _

This is one of the lines I am referring to. There is one more that needs
changing.

FOLDER & "OrderStatusTemp.xlsm", FileFormat:= _


"Elizabeth" wrote:

I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.

When you work with two different versions of Excel that have different file
extensions, it means that you have to use the right extension in the right
place. Running the Delete command before the SaveAs command can be
problematic in that the file you are trying to delete is xl2003 with the .xls
extension and you want to save it as xl2007 with a .xlsm extension. If you
have not yet executed the SaveAs, the .xlsm does not yet exist, so you will
get a "File Not Fou7nd" message.
So the delete extension should be .xls and the SaveAs extension should be
..xlsm/

"Elizabeth" wrote:

I changed the file format to xlExcel8 and that fixed the runtime error '1004'
but now I getting a Runtime error '53' on this part of the code:

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xlsm"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xlsm", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xlsm"


"JLGWhiz" wrote:

FOLDER & "OrderStatusTemp.xls", FileFormat:= _

This is one of the lines I am referring to. There is one more that needs
changing.

FOLDER & "OrderStatusTemp.xlsm", FileFormat:= _


"Elizabeth" wrote:

I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.

I saved my original file to .xlsx, that fixed my run time '53' error. I now
getting a Runtime error '9' subscript out of range in this part of the code:

Sheets("DECG").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
'ActiveWindow.LargeScroll ToRight:=-3
Range("J2").Select
Range("A1:BG10000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _

Workbooks("FormatReport2.xlsm").Sheets("Selections ").Range("A11:A13"),
Unique:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Windows("c").Activate (runtime error occurs here)

"JLGWhiz" wrote:

When you work with two different versions of Excel that have different file
extensions, it means that you have to use the right extension in the right
place. Running the Delete command before the SaveAs command can be
problematic in that the file you are trying to delete is xl2003 with the .xls
extension and you want to save it as xl2007 with a .xlsm extension. If you
have not yet executed the SaveAs, the .xlsm does not yet exist, so you will
get a "File Not Fou7nd" message.
So the delete extension should be .xls and the SaveAs extension should be
.xlsm/

"Elizabeth" wrote:

I changed the file format to xlExcel8 and that fixed the runtime error '1004'
but now I getting a Runtime error '53' on this part of the code:

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xlsm"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xlsm", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xlsm"


"JLGWhiz" wrote:

FOLDER & "OrderStatusTemp.xls", FileFormat:= _

This is one of the lines I am referring to. There is one more that needs
changing.

FOLDER & "OrderStatusTemp.xlsm", FileFormat:= _


"Elizabeth" wrote:

I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.

If you saved the file as a .xlsx extension, why are you trying to reference
it with a .xlxm extension? You need to understand what these extensions
mean. They define the type of file. xlsx is for data only, no macro. xlsm
is for files with macros. VBA sees these extensions as different files even
if the rest of the name is the same. So if you have the file saved as xlsx
and try to reference it as xlsm, you will get a message "Subscript out of
range."

"Elizabeth" wrote:

I saved my original file to .xlsx, that fixed my run time '53' error. I now
getting a Runtime error '9' subscript out of range in this part of the code:

Sheets("DECG").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
'ActiveWindow.LargeScroll ToRight:=-3
Range("J2").Select
Range("A1:BG10000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _

Workbooks("FormatReport2.xlsm").Sheets("Selections ").Range("A11:A13"),
Unique:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Windows("c").Activate (runtime error occurs here)

"JLGWhiz" wrote:

When you work with two different versions of Excel that have different file
extensions, it means that you have to use the right extension in the right
place. Running the Delete command before the SaveAs command can be
problematic in that the file you are trying to delete is xl2003 with the .xls
extension and you want to save it as xl2007 with a .xlsm extension. If you
have not yet executed the SaveAs, the .xlsm does not yet exist, so you will
get a "File Not Fou7nd" message.
So the delete extension should be .xls and the SaveAs extension should be
.xlsm/

"Elizabeth" wrote:

I changed the file format to xlExcel8 and that fixed the runtime error '1004'
but now I getting a Runtime error '53' on this part of the code:

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xlsm"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xlsm", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xlsm"


"JLGWhiz" wrote:

FOLDER & "OrderStatusTemp.xls", FileFormat:= _

This is one of the lines I am referring to. There is one more that needs
changing.

FOLDER & "OrderStatusTemp.xlsm", FileFormat:= _


"Elizabeth" wrote:

I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.

Unless you have many files to do this with, it might be faster to do it
manually.

"Elizabeth" wrote:

I changed the file format to xlExcel8 and that fixed the runtime error '1004'
but now I getting a Runtime error '53' on this part of the code:

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xlsm"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xlsm", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xlsm"


"JLGWhiz" wrote:

FOLDER & "OrderStatusTemp.xls", FileFormat:= _

This is one of the lines I am referring to. There is one more that needs
changing.

FOLDER & "OrderStatusTemp.xlsm", FileFormat:= _


"Elizabeth" wrote:

I recently upgraded to Office 2007. Before I ugraded to Office 2007 I was
using a macro that was created in 2003 but I'm not able to use it now. The
macro fails and I get an Runtime error '1004' - Method 'SaveAs' of
Object'_workbook failed.
I changed the file extension within the macro to Excel 2007 (xlsm), but did
not change this part of the code because I don't know what it should be. See
attached code:

ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatusTemp.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile FOLDER & "OrderStatus.xls"
ActiveWorkbook.SaveAs Filename:= _
FOLDER & "OrderStatus.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
fs.DeleteFile FOLDER & "OrderStatusTemp.xls"

Any help you can provide will be helpful.