TWAPI - attempting to replace TCOM for Excel interface
Jeff Godfrey
I have an app that currently uses TWAPI (3.0.29) and TCOM. Since TCOM
is only used to convert a CSV to an XLS file via a COM connection to
Excel, I thought I'd replace it with TWAPI's COM functionality in order
to eliminate a dependency.
Unfortunately, I seem to have a problem with one specific call in the
Excel COM interface. The following stripped-down sample code
illustrates the problem:
proc CSVtoXLS {file} {
set excel [::twapi::comobj Excel.Application]
#set excel [::tcom::ref createobj Excel.Application]
$excel Visible 0
set workbooks [$excel Workbooks]
$excel DisplayAlerts [expr 0]
set xlsType [expr 1]
set file [file nativename $file]
set xlsFile [file rootname $file].xls
$workbooks Open "$file"
set workbook [$workbooks Item [expr 1]]
$workbook SaveAs "$xlsFile" $xlsType
$workbook Close
$excel Quit
}
Notice at the top of the proc, there is code for both a twapi and a tcom
"excel" object. The code works as expected with the tcom-based object,
but fails when switched to the twapi-based object.
The failure is with the following line of code, which should save the
current workbook as a XLS file:
$workbook SaveAs $xlsFile $xlsType
The error is as follows:
Unsupported or invalid type information format in parameter while executing:
"::twapi::IDispatch_Invoke {5606044 IDispatch} {1925 0 1 24 {{12 17}} ..."
I know that data types are critical with COM interaction, and TCOM has
always required that numeric values be *forced* to be numeric - hence
the seemingly odd [expr ?] syntax above.
TWAPI's documentation doesn't seem to make any mention of how to
properly handle data types, but I haven't found any variation of the
above that works properly.
Anyway, is there a way to recode the offending line to work properly
with TWAPI or does this look like a TWAPI bug?
Thanks,
Jeff Godfrey
APN
spam bucket somewhere. Support for enumerated types was added to twapi
post-3.0 and I suspect that is the problem you are having.
(tcl) 63 % set file [file nativename [file normalize $file]]
C:\src\twapi\twapi\tcl\x.csv
(tcl) 64 % $workbooks Open "$file"
::twapi::Automation::o#14
(tcl) 65 % set xlsFile [file rootname $file].xls
C:\src\twapi\twapi\tcl\x.xls
(tcl) 66 % set workbook [$workbooks Item [expr 1]]
::twapi::Automation::o#17
(tcl) 67 % $workbook SaveAs "$xlsFile" $xlsType
(tcl) 68 % $workbook Close
(tcl) 69 % $excel Quit
(tcl) 70 % $workbook -destroy
(tcl) 71 % $excel -destroy
which seems to work (with 3.0.30 or 3.1.x) both of which are only in
the repo. I'll try to get a "development" release out for you to try
out this weekend.
/Ashok
Jeff Godfrey
> I'd sent you a private email in response, but that must have hit some
> spam bucket somewhere. Support for enumerated types was added to twapi
> post-3.0 and I suspect that is the problem you are having.
>
> (tcl) 63 % set file [file nativename [file normalize $file]]
> C:\src\twapi\twapi\tcl\x.csv
> (tcl) 64 % $workbooks Open "$file"
> ::twapi::Automation::o#14
> (tcl) 65 % set xlsFile [file rootname $file].xls
> C:\src\twapi\twapi\tcl\x.xls
> (tcl) 66 % set workbook [$workbooks Item [expr 1]]
> ::twapi::Automation::o#17
> (tcl) 67 % $workbook SaveAs "$xlsFile" $xlsType
> (tcl) 68 % $workbook Close
> (tcl) 69 % $excel Quit
> (tcl) 70 % $workbook -destroy
> (tcl) 71 % $excel -destroy
>
> which seems to work (with 3.0.30 or 3.1.x) both of which are only in
> the repo. I'll try to get a "development" release out for you to try
> out this weekend.
>
> /Ashok
Ashok,
After seeing your mention of a private email here, I looked again and...
Yep, it's there - just lost in the clutter of my inbox. Sorry about that.
Anyway, the above looks great. I'd be happy to play with a development
release if you can make it available.
I've responded to your private email as well.
Thanks again.
Jeff
APN
and 3.0 release branch support enumerated types (which was the issue).
It will be included in the next 3.0 patch.
/Ashok