Copying Data between Excel Workbooks

Add-in Express™ Support Service
That's what is more important than anything else

Copying Data between Excel Workbooks
Performance really slow 
George Spears




Posts: 79
 Joined: 2010-05-06
Hello, Using Add-In Express, I am needing to copy non-contiguous rows from sheet1 of workbook 'A' to sheet 1 of workbook 'B'. I am calling this routine from a RibbonTab. for every row, it takes almost 2 seconds. Rather than making the code overly complex for this example, I am copying the same row 10 times, as opposed to 10 random rows. The code below takes 18 seconds to run on a reasonably fast computer.

code snippet below.
I added excel2000 to the uses clause. 


var
  WS1, WS2: ExcelWorksheet;
  j : Integer;

begin
   ExcelApp.Connect;
   WS1 := ExcelApp.ActiveSheet as ExcelWorksheet;
   ExcelApp.WorkBooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
   WS2 := ExcelApp.ActiveSheet as ExcelWorksheet;

  for j := 1 to 10 do
    begin
     WS1.Range['A6', 'A6'].EntireRow.Copy(WS2.Range['A12','A12'].EntireRow);
    end;
    ShowMessage('Done');


Any help appreciated.
Posted 08 Apr, 2015 05:52:16 Top
Andrei Smolin


Add-in Express team


Posts: 18818
 Joined: 2006-05-11
Hello George,

You can try getting the ranges representing the rows outside of the loop. Also, you can set ExcelApp.ScreenUpdating := true before the loop; set false to this property after the loop.


Andrei Smolin
Add-in Express Team Leader
Posted 08 Apr, 2015 08:12:47 Top
George Spears




Posts: 79
 Joined: 2010-05-06
Just found one additional data point. When exiting Excel, I was getting an error message "The picture is too large and will be truncated." Upon investigating that, it appears that there is another program monopolizing the Clipboard. When I run this same dll on another computer, it runs fine... 108 ms. I uninstalled TeamViewer (which is a known culprit) but still have not identified the problem...

If you have any other thoughts, please let me know.
Posted 08 Apr, 2015 08:41:35 Top
Andrei Smolin


Add-in Express team


Posts: 18818
 Joined: 2006-05-11
I'd start with turning all COM add-ins off.


Andrei Smolin
Add-in Express Team Leader
Posted 08 Apr, 2015 08:45:40 Top
George Spears




Posts: 79
 Joined: 2010-05-06
Solved: The issue WAS related to the "Picture too large. Will be truncated" error when closing Excel. The code I posted works fine. Googling shows many issues with TeamViewer. I uninstalled TeamViewer and this did NOT fix my error. I had to re-install it, then go to options/Advanced, and turn off Clipboard synchronization, then stop/restart TeamViewer. (TeamViewer version 10)
Posted 08 Apr, 2015 09:32:13 Top
Andrei Smolin


Add-in Express team


Posts: 18818
 Joined: 2006-05-11
Thank you, George!


Andrei Smolin
Add-in Express Team Leader
Posted 09 Apr, 2015 01:50:38 Top