Results 1 to 2 of 2

Thread: ActiveX : filling up / coloring MS Table too slow

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    1

    Question ActiveX : filling up / coloring MS Table too slow

    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?

    HTML 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

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: ActiveX : filling up / coloring MS Table too slow

    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:
    Code:
    ... Cell(r,iCol).Select;
    hdlActiveX.Selection.Shading ...
    to:
    Code:
    ... Cell(r,iCol).Shading ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width