Hello!

I searched in many places for clean examples on pasting a large tcl
array into an Excel 2007 spreadsheet. I can do it by creating loops
and adding values in each cell one by one, but this is a slow process.

I found this  http://wiki.tcl.tk/11900 but the example under "Speeding
up Excel Writes" does not work.  Neither does the suggestion just
below it.  I keep getting

(bin) 35 % $range Select
0x800a03ec {Select method of Range class failed}

(bin) 36 % $range -namedarg TextToColumns Destination [::tcom::na]
DataType [::tcom::na] TextQualifier [::tcom::na] ConsecutiveDelimiter
[expr 1] Tab [::tcom::na] Semicolon [expr 1] Comma [::tcom::na] Space
[::tcom::na] Other [::tcom::na] FieldInfo $FieldInf DecimalSeparator
[::tcom::na] ThousandsSeparator [::tcom::na] TrailingMinusNumbers
[expr 1]
0x800a03ec {No data was selected to parse.}

Any direction on where to find a good example(s) would be appreciated.

/sd

On 28 Mrz., 07:17, sd <sham...@hotmail.com> wrote:
> Hello!
>
> I searched in many places for clean examples on pasting a large tcl
> array into an Excel 2007 spreadsheet. I can do it by creating loops
> and adding values in each cell one by one, but this is a slow process.
>
> I found this =A0http://wiki.tcl.tk/11900but the example under "Speeding
> up Excel Writes" does not work. =A0Neither does the suggestion just
> below it. =A0I keep getting
>
> (bin) 35 % $range Select
> 0x800a03ec {Select method of Range class failed}
>
> (bin) 36 % $range -namedarg TextToColumns Destination [::tcom::na]
> DataType [::tcom::na] TextQualifier [::tcom::na] ConsecutiveDelimiter
> [expr 1] Tab [::tcom::na] Semicolon [expr 1] Comma [::tcom::na] Space
> [::tcom::na] Other [::tcom::na] FieldInfo $FieldInf DecimalSeparator
> [::tcom::na] ThousandsSeparator [::tcom::na] TrailingMinusNumbers
> [expr 1]
> 0x800a03ec {No data was selected to parse.}
>
> Any direction on where to find a good example(s) would be appreciated.
>
> /sd

The clipboard supports different formats. Using a clipboard format spy
you will see that e.g. Excel fills the clipboard with different
formats when you copy a selection.

My guess is, that this also works the other way round. So you could
provide the data in a format in the clipboard that Excel understands
and then just automate Excel with TCOM to just paste the content of
the clipboard.

Here are 2 tools that can show you the clipboard formats:
* http://www.ciansoft.com/cflist/default.asp
* http://www.delphidabbler.com/software/cfs/scr

R=FCdiger

Hi,

To paste large amounts of data from tcl to excel via the clipboard, I 
use the dde instead of tcom:

package require dde
clipboard clear
clipboard append "My\tmessage\nover\t2lines"
dde execute Excel System {[PASTE()][SELECT("R[2]C")]}

This will add the data in excel at the active cell, and then move the
active cell 2 rows down.

Peter


On 03/28/2011 07:17 AM, sd wrote:
> Hello!
>
> I searched in many places for clean examples on pasting a large tcl
> array into an Excel 2007 spreadsheet. I can do it by creating loops
> and adding values in each cell one by one, but this is a slow process.
>
> I found this  http://wiki.tcl.tk/11900 but the example under "Speeding
> up Excel Writes" does not work.  Neither does the suggestion just
> below it.  I keep getting
>
> (bin) 35 % $range Select
> 0x800a03ec {Select method of Range class failed}
>
> (bin) 36 % $range -namedarg TextToColumns Destination [::tcom::na]
> DataType [::tcom::na] TextQualifier [::tcom::na] ConsecutiveDelimiter
> [expr 1] Tab [::tcom::na] Semicolon [expr 1] Comma [::tcom::na] Space
> [::tcom::na] Other [::tcom::na] FieldInfo $FieldInf DecimalSeparator
> [::tcom::na] ThousandsSeparator [::tcom::na] TrailingMinusNumbers
> [expr 1]
> 0x800a03ec {No data was selected to parse.}
>
> Any direction on where to find a good example(s) would be appreciated.
>
> /sd
>

On 3/28/2011 12:17 AM, sd wrote:
> Hello!
>
> I searched in many places for clean examples on pasting a large tcl
> array into an Excel 2007 spreadsheet.

In addition to the other responses, you could also save your "large 
array" to a plain CSV file (using tcllib's csv module), and then load 
that file into Excel via COM using either twapi or tcom.

While that may sound more complex, it really boils down to just a (very) 
little bit of code when leveraging the mentioned libraries.

Obviously, this assumes that you want the data in a *new* spreadsheet, 
and not somehow appended to existing spreadsheet data.

Jeff

I put a little example onto http://wiki.tcl.tk/11900, which copies the 
data to the clipboard in CSV format.

No need for Tk (for the clipboard command) and no need for additional 
conversions with the TextToColumns method.

Another advantage: It works with strings containing the CSV separator 
character.

Paul

Am 28.03.11 11:40, schrieb hae:
> On 28 Mrz., 07:17, sd<sham...@hotmail.com>  wrote:
>> Hello!
>>
>> I searched in many places for clean examples on pasting a large tcl
>> array into an Excel 2007 spreadsheet. I can do it by creating loops
>> and adding values in each cell one by one, but this is a slow process.
>>
>> I found this  http://wiki.tcl.tk/11900but the example under "Speeding
>> up Excel Writes" does not work.  Neither does the suggestion just
>> below it.  I keep getting
>>
>> (bin) 35 % $range Select
>> 0x800a03ec {Select method of Range class failed}
>>
>> (bin) 36 % $range -namedarg TextToColumns Destination [::tcom::na]
>> DataType [::tcom::na] TextQualifier [::tcom::na] ConsecutiveDelimiter
>> [expr 1] Tab [::tcom::na] Semicolon [expr 1] Comma [::tcom::na] Space
>> [::tcom::na] Other [::tcom::na] FieldInfo $FieldInf DecimalSeparator
>> [::tcom::na] ThousandsSeparator [::tcom::na] TrailingMinusNumbers
>> [expr 1]
>> 0x800a03ec {No data was selected to parse.}
>>
>> Any direction on where to find a good example(s) would be appreciated.
>>
>> /sd
>
> The clipboard supports different formats. Using a clipboard format spy
> you will see that e.g. Excel fills the clipboard with different
> formats when you copy a selection.
>
> My guess is, that this also works the other way round. So you could
> provide the data in a format in the clipboard that Excel understands
> and then just automate Excel with TCOM to just paste the content of
> the clipboard.
>
> Here are 2 tools that can show you the clipboard formats:
> * http://www.ciansoft.com/cflist/default.asp
> * http://www.delphidabbler.com/software/cfs/scr
>
> R�diger

Paul,
I tried your example but got an error at the last line:

(bin) 17 % $worksheetId Paste
0x800a03ec {Paste method of Worksheet class failed}

I would really like to stick with tcom before I give up on it and try
something else. I'm getting close achieving my goal, but ran into an
awkward problem when it comes to converting a list into columns.
Below is a small example of what's happening.  I'm creating an Excel
workbook and adding three new worksheets in it.  I'm pasting the same
data in all three new worksheets, but the data in the first new
worksheet "NewSheet 1" is not delimited correctly into columns.  All
the data in "NewSheet 2" and "NewSheet 3" is in correct format.  Any
idea why the the first time data is pasted in NewSheet 1 is not
yielding the desired effects?

Thanks
/sd


package require tcom
package require csv

set myList {{1 2 3 4} {5 6 7 8} {9 10 11 12}}
set application [::tcom::ref createobject Excel.Application]
$application Visible 0

# Create inital workbook.
set workbooks [$application Workbooks]
set workbook [$workbooks Add]
set worksheets [$workbook Worksheets]

# create three new sheets, rename them, and enter some data
for {set n 1} {$n <= 3} {incr n} {
    set lastWorksheet [$worksheets Item [$worksheets Count]]
    set worksheet [$worksheets Add [::tcom::na] $lastWorksheet]
    $worksheet Name "NewSheet $n"

    clipboard clear
    clipboard append [csv::joinlist $myList "|"] ;# Use "|" if you use
the Other named arguments...
    set range [$worksheet Range A6]
    $range Select
    $worksheet Paste
    $range -namedarg TextToColumns Other 1 OtherChar "|"
}
 $workbook SaveAs {C:\test.xlsx}
 set application {}

I would really like to stick with tcom before I give up on it and try
something else. I'm getting close achieving my goal, but ran into an
awkward problem when it comes to converting a list into columns.
Below is a small example of what's happening.  I'm creating an Excel
workbook and adding three new worksheets in it.  I'm pasting the same
data in all three new worksheets, but the data in the first new
worksheet "NewSheet 1" is not delimited correctly into columns.  All
the data in "NewSheet 2" and "NewSheet 3" is in correct format.  Any
idea why the the first time data is pasted in NewSheet 1 is not
yielding the desired effects?

Thanks
/sd


package require tcom
package require csv

set myList {{1 2 3 4} {5 6 7 8} {9 10 11 12}}
set application [::tcom::ref createobject Excel.Application]
$application Visible 0

# Create inital workbook.
set workbooks [$application Workbooks]
set workbook [$workbooks Add]
set worksheets [$workbook Worksheets]

# create three new sheets, rename them, and enter some data
for {set n 1} {$n <= 3} {incr n} {
    set lastWorksheet [$worksheets Item [$worksheets Count]]
    set worksheet [$worksheets Add [::tcom::na] $lastWorksheet]
    $worksheet Name "NewSheet $n"

    clipboard clear
    clipboard append [csv::joinlist $myList "|"]
    set range [$worksheet Range A6]
    $range Select
    $worksheet Paste
    $range -namedarg TextToColumns Other 1 OtherChar "|"
}
 $workbook SaveAs {C:\test.xlsx}
 set application {}

I also tried my script today on different machines and it failed:

First problem was, that the clipboard format identifier is not unique 
across machines, but the name of the clipboard format: "Csv".
So you have to get the clipboard format identifier with function 
register_clipboard_format.
I also added an "after 10" command, because on some machines it seems to 
take some time before the clipboard data is visible for the other 
application.

The Wiki page has been updated.

Paul

On 30.03.2011 05:18, sd wrote:
> Paul,
> I tried your example but got an error at the last line:
>
> (bin) 17 % $worksheetId Paste
> 0x800a03ec {Paste method of Worksheet class failed}
>

I don't have any idea, why this happens (ask Microsoft :-)).

But, if I replace the line

set range [$worksheet Range A6]

with

set range [$worksheet Range "A6:A8"]

it works with my Excel 2007.

Hope this helps.

Paul

On 30.03.2011 19:13, sd wrote:
> I would really like to stick with tcom before I give up on it and try
> something else. I'm getting close achieving my goal, but ran into an
> awkward problem when it comes to converting a list into columns.
> Below is a small example of what's happening.  I'm creating an Excel
> workbook and adding three new worksheets in it.  I'm pasting the same
> data in all three new worksheets, but the data in the first new
> worksheet "NewSheet 1" is not delimited correctly into columns.  All
> the data in "NewSheet 2" and "NewSheet 3" is in correct format.  Any
> idea why the the first time data is pasted in NewSheet 1 is not
> yielding the desired effects?
>
> Thanks
> /sd
>
>
> package require tcom
> package require csv
>
> set myList {{1 2 3 4} {5 6 7 8} {9 10 11 12}}
> set application [::tcom::ref createobject Excel.Application]
> $application Visible 0
>
> # Create inital workbook.
> set workbooks [$application Workbooks]
> set workbook [$workbooks Add]
> set worksheets [$workbook Worksheets]
>
> # create three new sheets, rename them, and enter some data
> for {set n 1} {$n<= 3} {incr n} {
>      set lastWorksheet [$worksheets Item [$worksheets Count]]
>      set worksheet [$worksheets Add [::tcom::na] $lastWorksheet]
>      $worksheet Name "NewSheet $n"
>
>      clipboard clear
>      clipboard append [csv::joinlist $myList "|"]
>      set range [$worksheet Range A6]
>      $range Select
>      $worksheet Paste
>      $range -namedarg TextToColumns Other 1 OtherChar "|"
> }
>   $workbook SaveAs {C:\test.xlsx}
>   set application {}

> set range [$worksheet Range "A6:A8"]

Actually, when I put my application to use, it might need to paste
thousands of lines, which can different from one worksheet to
another.  So, to be safe I'll have to do something like:

set range [$worksheet Range "A6:A999999"]

which does the trick where I was having problems on the first set of
data.  All other worksheets work fine without having to resort to the
above trick.  I hate to put such crude patches when I don't know
exactly why they work... they just do.

Thanks again for your help.

/sd

On Mar 31, 12:51=A0am, sd <sham...@hotmail.com> wrote:
> > set range [$worksheet Range "A6:A8"]
>
> Actually, when I put my application to use, it might need to paste
> thousands of lines, which can different from one worksheet to
> another. =A0So, to be safe I'll have to do something like:
>
> set range [$worksheet Range "A6:A999999"]

Just a sidebar, really:

Excel's actual limit, as of Excel2010 is 1,048,576 rows (before Excel
2010, it was 65,536 rows).

Hello,

The needed range can be calculated quite easily from the size of the 
list, so I left it as an exercise to you.

Here is the code to generalize the procedure:

set startRow 6
set endRow [expr $startRow + [llength $myList] - 1]
set rangeStr [format "A%d:A%d" $startRow $endRow]
set range [$worksheet Range $rangeStr]
puts "Selecting range $rangeStr"

Paul

On 31.03.2011 06:51, sd wrote:
>
>> set range [$worksheet Range "A6:A8"]
>
> Actually, when I put my application to use, it might need to paste
> thousands of lines, which can different from one worksheet to
> another.  So, to be safe I'll have to do something like:
>
> set range [$worksheet Range "A6:A999999"]
>
> which does the trick where I was having problems on the first set of
> data.  All other worksheets work fine without having to resort to the
> above trick.  I hate to put such crude patches when I don't know
> exactly why they work... they just do.
>
> Thanks again for your help.
>
> /sd
>
>

I have added two new procedures to my TcomExcel extension (new version 
0.3.2), which implement fast data transfer via the clipboard in both 
directions.
See http://www.posoft.de/html/extTcomExcel.html

Tested on Windows 7 with Excel 97, 2000, 2003 and 2007.

Paul

On 30.03.2011 21:28, Paul Obermeier wrote:
> I also tried my script today on different machines and it failed:
>
> First problem was, that the clipboard format identifier is not unique
> across machines, but the name of the clipboard format: "Csv".
> So you have to get the clipboard format identifier with function
> register_clipboard_format.
> I also added an "after 10" command, because on some machines it seems to
> take some time before the clipboard data is visible for the other
> application.
>
> The Wiki page has been updated.
>

Paul,

Thanks for providing this extension.  I will try to use it by looking
at the examples provided.

/sd

Has anyone been successful at copying an existing worksheet in the
same workbook? I have Sheet1 with lots of charts and other data and I
want to keep it but create another copy of it.  I tried different
variations such as:

${worksheet} Copy [::tcom::na] ${worksheet}

but non worked.

Regards,
/sd

Hi,

TcomExcel has a utility function called CopyWorksheet (in file 
excelUtil.tcl).


Paul

Am 11.04.11 17:26, schrieb sd:
> Has anyone been successful at copying an existing worksheet in the
> same workbook? I have Sheet1 with lots of charts and other data and I
> want to keep it but create another copy of it.  I tried different
> variations such as:
>
> ${worksheet} Copy [::tcom::na] ${worksheet}
>
> but non worked.
>
> Regards,
> /sd
>