Excel SaveAs Fileformat

[MichaelHensley]

I have a script that starts up Excel, opens a CSV file, does a bunch of
stuff to the contents, and then I want to save the results as a normal Excel
workbook. Everything works great except the SaveAs part.

Here is the line:

objExcel.ActiveWorkbook.SaveAs "Foobar.xls", xlNormal

The error is "SaveAs method of Workbook class failed"

I've tried a number of random variations on the theme, to no avail.

Any suggestions would be greatfully received. Thanks!

[y sakuda]

"MichaelHensley" <mhen...@news.postalias> wrote in message
news:euwlY64W...@TK2MSFTNGP03.phx.gbl...

>I have a script that starts up Excel, opens a CSV file, does a bunch of
>stuff to the contents, and then I want to save the results as a normal
>Excel workbook. Everything works great except the SaveAs part.
>
> Here is the line:
>
> objExcel.ActiveWorkbook.SaveAs "Foobar.xls", xlNormal
>
> The error is "SaveAs method of Workbook class failed"
>

In VBS you can't use named constant.
(Only allowed vbCRLF etc.)
If you want to save as CSV must write like this.

Const xlCSV=6
objExcel.ActiveWorkbook.SaveAs "Foobar.xls", xlCSV

y sakuda from JPN

[MichaelHensley]

y sakuda pointed out:

> In VBS you can't use named constant.
> (Only allowed vbCRLF etc.)
> If you want to save as CSV must write like this.
>
> Const xlCSV=6
> objExcel.ActiveWorkbook.SaveAs "Foobar.xls", xlCSV

Got it, thanks!

Note: for those playing along at home, it looks like the value for
"xlNormal" is 1.

[y sakuda]

"MichaelHensley" <mhen...@news.postalias> wrote in message

news:ukPwjM5W...@TK2MSFTNGP03.phx.gbl...

> Note: for those playing along at home, it looks like the value for
> "xlNormal" is 1.
>

Sorry. I misread as you want to Save as CSV.
If you want to save as current version, you don't need to
specify second parameter.
objExcel.ActiveWorkbook.SaveAs "Foobar.xls"

y sakuda from JPN

[Neilson]

Could you please paste your original code here. I'm tryin g to convert my
xls to csv format using similar code. I'm not sure how yours work when you
use objExcel.ActiveWorkbook.SaveAs

Thanks in advance.

Neil

url:http://www.ureader.com/msg/16755389.aspx

[abhi.un...@gmail.com]

hello i am using following script to covert xlsx to csv

xlsconvert.vbs includes following code:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(Wscript.Arguments(0))
objExcel.Application.Visible = False
objExcel.Application.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs Wscript.Arguments(1), 56
objExcel.ActiveWorkbook.Close
objExcel.Application.DisplayAlerts = True
objExcel.Application.Quit
WScript.Quit

i am getting the same error that "SaveAs method of Workbook class failed"

i am executing it with batch file in batch file i have written as:
cscript D:\INFA_AGI_DEV_QA\10.1.0\server\infa_shared\Utilities\putty\xlsconvert.vbs \\10.44.194.42\Sap_bw_dev\interfaces\InBound\BCS\BCS_BW_100_0102017.xlsx \\10.44.194.42\Sap_bw_dev\interfaces\InBound\BCS\BCS_BW_100_0102017_01.csv

[Mayayana]

<abhi.un...@gmail.com> wrote

| On Sunday, April 9, 2006 at 11:09:32...

12 years ago that was posted! How about
getting a real newsreader and connecting to
a usenet server. Google groups is no way
to do newsgroups.

Here's a free newsgroup server:

http://www.eternal-september.org/

If you don't have software you can use
Thunderbird.

[JJ]

On Mon, 22 Jan 2018 13:49:13 -0800 (PST), abhi.un...@gmail.com wrote:
>
> hello i am using following script to covert xlsx to csv
>
> xlsconvert.vbs includes following code:
>
> Set objExcel = CreateObject("Excel.Application")
> Set objWorkbook = objExcel.Workbooks.Open(Wscript.Arguments(0))
> objExcel.Application.Visible = False
> objExcel.Application.DisplayAlerts = False
> objExcel.ActiveWorkbook.SaveAs Wscript.Arguments(1), 56
> objExcel.ActiveWorkbook.Close
> objExcel.Application.DisplayAlerts = True
> objExcel.Application.Quit
> WScript.Quit
>
> i am getting the same error that "SaveAs method of Workbook class failed"
>
> i am executing it with batch file in batch file i have written as:
> cscript D:\INFA_AGI_DEV_QA\10.1.0\server\infa_shared\Utilities\putty\xlsconvert.vbs \\10.44.194.42\Sap_bw_dev\interfaces\InBound\BCS\BCS_BW_100_0102017.xlsx \\10.44.194.42\Sap_bw_dev\interfaces\InBound\BCS\BCS_BW_100_0102017_01.csv

Wrong file format number. Number 56 is for Excel v8 format, not CSV format.
See the Excel Developer Reference.

That error is likely due to newer Office/Excel versions not supporting
saving into older Excel formats other than v97-2003 format.