Prev: In what order do installed AddIns open?
Next: Move lines from one sheet to another
From: simonc on 26 Mar 2010 06:03
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.


From: Peter T on 26 Mar 2010 07:10
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" <simonc(a)discussions.microsoft.com> wrote in message
news:59BADC99-CC22-46AF-9EEF-23A1FBF323C8(a)microsoft.com...
>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.
>
>


From: simonc on 26 Mar 2010 08:34
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?

"Peter T" wrote:

> 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" <simonc(a)discussions.microsoft.com> wrote in message
> news:59BADC99-CC22-46AF-9EEF-23A1FBF323C8(a)microsoft.com...
> >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.
> >
> >
>
>
> .
>
From: Barb Reinhardt on 27 Mar 2010 18:46
This should help you.

http://www.rondebruin.nl/saveas.htm
--
HTH,

Barb Reinhardt



"simonc" wrote:

> 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.
>
>
 | 
Pages: 1
Prev: In what order do installed AddIns open?
Next: Move lines from one sheet to another