How to save Excel 2007 workbooks using tcom

[Jeff H.]

Hi,

I have a set of .csv files with more than 300 columns and have to save
them in .xlsx (Excel 2007 spreadsheets). I used the following code
with tcom 3.9. But I got an error message like this: 0x800a03ec
{SaveAs method of Workbook class failed} while executing "$ebook
SaveAs "$name$xlsx" [expr 51]"...
Does anyone know how to save Excel 2007 workbooks?
Thanks in advance!

Jeff H.

#########################################
package require tcom
set listOfFiles [glob "*.csv"]

set csv ".csv"
set xlsx ".xlsx"

set e [::tcom::ref createobject "Excel.Application"]
$e Visible 1
set workbooks [$e Workbooks]

foreach eitem $listOfFiles {
set lenfile [string length $eitem]
set lenfilewoe [expr $lenfile-4]

set name [string range $eitem 0 8]
set ebook [$workbooks Open "$name$csv"]
$ebook SaveAs "$name$xlsx" [expr 51]
$ebook Saved 1
$ebook Close
}

$e Quit

[drsc...@gmail.com]

Jeff H. wrote:
> Hi,
>
> I have a set of .csv files with more than 300 columns and have to save
> them in .xlsx (Excel 2007 spreadsheets). I used the following code
> with tcom 3.9. But I got an error message like this: 0x800a03ec

It may be because .xlsx is not really a binary file anymore and you may
need other methods or flags when you create the handle. It is plain Xml
file under the hood - I think it started with Office 2007. I
encountered this a while ago but I am not sure how we resolved it. In
any case, Excel can easily open csv files, though.

DrS

[drsc...@gmail.com]

drsc...@gmail.com wrote:
> Jeff H. wrote:
>> Hi,
>>
>> I have a set of .csv files with more than 300 columns and have to save
>> them in .xlsx (Excel 2007 spreadsheets). I used the following code
>> with tcom 3.9. But I got an error message like this: 0x800a03ec
>

I overlooked this:

Excel cannot create more than 256 columns and you say you have more than
300. The solution is to trim them down somehow.

DrS

[Elchonon Edelson]

In fact, the OP is asking about Excel 2007, which supports up to 16k
columns (and 1M rows.)

-EE

--
Because if you don't check your facts, how can you be SURE you're
posting nonsense to usenet?
-- Beable van Polasm

[Mark Janssen]

Why the [expr 51] ?

[Mark Janssen]

Ok 51 seems to indicate xlWorkbookDefault. This fails with the exact
same error for me on Office 2003. xlWorkbookDefault seems to have been
introduced with office 2003. Are you sure you are using a 2007 COM
object? You could check with [$e Version].

Regards,
Mark

[Jeff H.]

> Mark- Hide quoted text -
>
> - Show quoted text -

I used tcom 3.9. I'm not sure if it can handle Excel 2007 workboods
(.xlsx and .xlsm).

BTW,
51 is the FileFormatNum for .xlsx.
-4143 is the FileFormatNum for .xls (i.e. Excel 97-2003).
You can find Enum XlFileFormat (list of FileFormatNum) from a VBA
book.
Another example, 6 is for .csv files.

[Mark Janssen]

The tcom version does not relate to what version of Excel is supported
(that's one of the points of COM). What I wanted to know is if you are
creating a COM object for Office 2007. You can check this by [$e
Version] in your script. One other possibility would be to omit the 51
all together, because then the default will be picked.

Mark