-
Feb 17th, 2012, 03:13 AM
#1
Thread Starter
New Member
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
-
Feb 17th, 2012, 08:39 AM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|