ActiveX : filling up / coloring MS Table too slow

[Adef]

Hallo there,

filling up a big MS Word table with ActiveX -> VBA is sooooo slow.

How could I boost my performance for the following code?

hdlActiveX = actxserver('Word.Application'); 
... 
hdlActiveX.ActiveDocument.Tables.Add(hdlActiveX.Selection.Range, nbRows, nbCols, 1, 1).Rows.Alignment = 1; 
... 
% create dataCell{nbRows, nbCols}  containing the table values row- and columnwise 
... 
nrTable = ... % an integer selecting a table in the MS Word document  
... 
for r = 1:nbRows    
   for c = 1:nbCols        
       % Write data into current cell       
       hdlActiveX.ActiveDocument.Tables.Item(1).Cell(r,c).Range.Text = dataCell{r, c};        

       % setting a different background color rowwise, if a condtion is met       
       % this is even bigger performance reducer       
       if condition           
            for iCol = 1:nbCols               
                 hdlActiveX.ActiveDocument.Tables.Item(nrTable).Cell(r,iCol).Select;               
                 hdlActiveX.Selection.Shading.BackgroundPatternColor = color;           
            end       
       end     
   end 
end 

Thank you very much in advance

[si_the_geek]

Welcome to VBForums

A big problem is that you are repeatedly doing work inside the loops to find the tables every single time you use them, rather than finding them before the loops and storing them to variables (which you then use inside the loops).

For example, instead of hdlActiveX.ActiveDocument.Tables.Item(1).Cell(r,c)... (which asks Word to determine/guess which document is active, asks it to find the tables within that document, and then asks it to return the first table) it would be faster to have something like: Table1.Cell(r,c)...


Another issue (which is specific to MS Office apps, rather than being general programming) is that you are using the Selection object. That is slow (because it takes extra work to select things), and unreliable (because what is selected can change outside of your control at any moment).

There are very few cases where Selection is needed, and you can usually eliminate it by removing the .Select and Selection, like this:

... Cell(r,iCol).Select;
hdlActiveX.Selection.Shading ...

to:

... Cell(r,iCol).Shading ...