SaveAs in Excel97-2003 format

[simonc]

I am working in Excel 2000 on a machine which also has Excel 2007 installed.

In a macro this line is causing Excel to crash:

bk.SaveAs Filename:=excel_output_file, FileFormat:=xlNormal

bk is a workbook and excel_output_file is a string ending with .xls

Is there a problem with the xlNormal value? Is there a value which will
specify Excel 97-2003 format which would work? (Because I opened the workbook
from a .csv file if I leave out the FileFormat parameter it saves in csv
format which I don't want.)

Grateful for advice.

 

[Peter T]

That should work fine in all versions. Are you sure excel_output_file is
valid (apart from the .xls). What error are you getting.

FWIW, I know xlNormal is what you get from the macro recorder, a bit sloppy
as it should really be xlWorkbookNormal. Not that it should make any
difference as both constants should refer to -4143. But talking of
constants, be sure not to include any of the new XL2007 named constants if
working in XL2000, even if not actually calling them in XL2000. If catering
for different versions change them to their intrinsic values.

Regards,
Peter T

 

[simonc]

Thanks for your response. It's hard to say what the error is because it
generates one of those windows that says "Microsoft Excel for Windows has
encountered a problem and needs to close". Annoyingly it leaves an excel
process running, so you have to kill that with Task Manager.

excel_output_file is definitely a valid filename.

However, the problem may be that it can't overwrite the existing file,
because when I deleted the existing output .xls file the macro ran without an
error. There doesn't seem to be an option in SaveAs to overwrite the existing
file. How can you get it to put up a dialogue box to ask if you want to?

 

[Barb Reinhardt]

This should help you.

http://www.rondebruin.nl/saveas.htm