Advice on Filling Tables

[Shred Dude]

To fill a Word Table from an array, must looping be involved?

For example, the code below works, but I'm wondering if there is a swifter method to be employed when dealing with larger data sets. Anything similar to Excel's ability to have a range accept an array as its values in one statement?


Public Sub arrayToTable()

Dim myTbl As Word.Table
Dim arrData(1 To 15, 1 To 5)

'Create array for this example
'in practice this might come from an Excel Range
For r = 1 To UBound(arrData, 1)
For c = 1 To UBound(arrData, 2)
arrData(r, c) = r * c
Next c
Next r

ActiveDocument.Tables.Add Selection.Range, UBound(arrData), UBound(arrData, 2)

Set myTbl = ActiveDocument.Tables(1)

For r = 1 To UBound(arrData, 1)
For c = 1 To UBound(arrData, 2)
myTbl.Cell(r, c).Range.Text = arrData(r, c)
Next c
Next r

End Sub

[Tinbendr]

No. Word is fluid and so doesn't work that way. But you can insert the text into the document, maybe in a bookmark, then use convert to table, applying whatever style you need. That is usually significantly quicker.

David

[Frosty]

I agree with David. Word tables have a lot more flexibility, so if that ability were exposed, you'd end up with errors more often than not (in short, the execution of the "merge cells" concept in Word works totally differently than in Excel)

That said, I believe you can just take an excel range, copy it to the clipboard, and paste it into a word document, and Word will take care of formatting it as a table.

Depending on how big your excel range is, this may take some time.

But, in my experience, this will break at some point. Basically, Excel's ability to handle large datasets outstrips Word's ability to display it in a table.

Is there a bigger picture of what you're trying to do? Use word as a prettier format for some kind of reporting-like feature?

[Shred Dude]

Thanks for the ideas. I took the Copy and Paste concept into the routine below which would be launched from Excel. It works, but I'm still interested in dumping an array full of data I've created within a routine directly to a Word Table without having to iterate the array through a loop. Is that possible?

The bigger picture is I'm just trying to learn how to deal with Word in the most efficient manner. The immediate task at hand is I'm generating a five column, 12-56 row table of data that represents a billing schedule I'm appending to a document. At the moment I have predefined the table's header rows and layout in the template, and placed a bookmark on the last (empty) row of the table to make it easy to reference. Then, I iterate the array through a loop adding a row to the table for each row in the array, and populate each of the five cells on that row through an inner loop. If i have 50 rows of 5 cells , I'm figuring that's 250 "writes" to the document. Even with screen updating set to false there's a slight delay. I'm just wondering if there's a way for me to dump all 250 cells in one go into the table. With this method below I suppose I could write the array to an excel sheet in one statement (lightning fast) and then copy / pasteexceltable. Seems like a round about way though.

Public Sub rangeToDoc()
Dim rngData As Excel.Range
Dim theDoc As Word.Document
Dim wdApp As Word.Application


Set rngData = ActiveSheet.Range("thedata")
rngData.Copy


Set wdApp = CreateObject("word.application")

wdApp.Visible = True

Set theDoc = wdApp.Documents.Add
With theDoc.Range

.InsertAfter "Here's the Data"
.InsertAfter vbCrLf
.Collapse wdCollapseEnd

.PasteExcelTable False, True, False

End With

Set wdApp = Nothing
Set rngData = Nothing
Application.CutCopyMode = False

End Sub

[Frosty]

I know of know way to apply an actual array of data to a word table in one fell swoop. If you're running this code from excel, it should be fairly trivial to use that function in excel, and then do your create.object to generate the word. If you're running this code from Word, I'd imagine it wouldn't be worth creating an excel object solely for the purpose of that one method.

Assuming you're running the code from within Excel...I think the biggest time delay would be during the create.object... unless you're using the selection object during your inserts (if you are, start using range objects instead).

After that, you might get some marginal improvements by inserting the total number of rows according to the ubound of your array, and then inserting each cell. But either way, you've got to insert the data into each cell, so I doubt there's much improvement to be had by a different approach.

Major optimizations will fall into figuring out ways to tell Word not to refresh the view during the middle of your routine.

Some additional suggestions:

1. Use normal view instead of page layout/print view (depending on the version). Something along the lines of, with your code...
wdapp.activewindow.view.type = wdnormalview.

Also you can change the zoom percentage to a very small percentage... I've used that trick before to some benefit.

And then, maybe, once you're comfortable with what's going on... do wdapp.visible = false.

2. Don't use the selection object. Figure out how to use the range object instead... every adjustment of the selection object can trigger word to try and repaint (or at least refigure out where the cursor should be, etc), so you get a lot of slow down from that.

Something along the lines of

dim oCell as Cell
dim oRow as Row

for each oRow in oMyTable.Rows
for each oCell in oRow.Cells
oCell.Range = myArray(x,y)
x = x + 1
Next
y = y + 1
Next

If you're trying to optimize, the application.screenupdating line is a bit of a crutch designed to avoid repaints... but if you program correctly, you shouldn't be triggering to many repaints anyway.

I'm sorry I can't provide a real code sample, as I'm not at a development machine at the moment. Hope this points in the right direction.

[Shred Dude]

After that, you might get some marginal improvements by inserting the total number of rows according to the ubound of your array, and then inserting each cell. But either way, you've got to insert the data into each cell, so I doubt there's much improvement to be had by a different approach.


I'll try that, I've been adding a row to the table at each iteration of the loop and then using .rows.last... to fill in each cell for that row.

I think you've confirmed for me my suspicion that there wasn't a way to fill all the cells in one go.

I think I'll run some timers against dumping the array to excel range and then using pasteexceltable to see if that makes any difference. The project I'm working on originates from Excel, with a userform capturing several inputs/settings that ultimately effect the data contained in this table I'm creating for the Word Document. I'm OK with having to use create.object to get Word fired up in this case as I really need to be in Excel to begin the process.

I'm guessing the difference in approaches will be marginal at best in current example. I'm just trying to use it as learning exercise.

[Frosty]

Well, as a learning exercise, I don't think your data set is big enough to see a big improvement, but you can see some (very) marginal improvements by explicitly referencing objects in a collection rather than letting vba do it for you using the .last type stuff.

just an example of what I mean, although I'm sure you already know it...
i = rows.count
set myRow = rows(i)

should be slightly faster than

set myRow = rows.last

But in small data sets, you probably won't notice the difference.

The biggest bang for your buck will probably be hiding word or using normal view with a very small zoom percentage if you're unable to remove all of the instances of word desiring to repaint the image.

Oh... and, you can always grab the existing word session if it exists (trying GetObject before CreateObject, and ignoring the error), although that may or may not have it's own issues, depending on how many addins of Word you usually have.

Let me know how it goes, I'm curious.

[Shred Dude]

Frosty:

I threw this together to compare the two methods of filling a Word Table, Cell-by-Cell, and pasting a copied range from Excel.

I didn't incorporate your suggestion around repainting the screen and zoom level, maybe you can throw that in?

As this stands now, it appears that pasting from Excel is significantly faster, even on this small data set.

Run this routine from Excel...

Public Sub arrToDoc()
Dim arrData(1 To 15, 1 To 5)
Dim r As Integer, c As Integer
Dim StopWatch As Double
Dim wdApp As Word.Application
Dim myTbl As Word.Table
Dim myRange As Word.Range

'make the test array
For r = 1 To UBound(arrData, 1)
For c = 1 To UBound(arrData, 2)
arrData(r, c) = r * c
Next c
Next r

'Start Word
Set wdApp = CreateObject("word.application")
wdApp.Visible = True

'add a document
wdApp.Documents.Add

'Header for first test
With wdApp.ActiveDocument.Range
.InsertAfter "Here's the Data added to a Word Table Cell by Cell"
.InsertAfter vbCrLf
End With


Set myRange = wdApp.ActiveDocument.Content
myRange.Collapse Direction:=wdCollapseEnd

'#############
'add a table
StopWatch = Timer

wdApp.ActiveDocument.Tables.Add Range:=myRange, _
NumRows:=UBound(arrData, 1), NumColumns:=UBound(arrData, 2)

'write array to the document's table cell by cell
Set myTbl = wdApp.ActiveDocument.Tables(1)
wdApp.ScreenUpdating = False
With myTbl
'Fill the Table
For r = 1 To UBound(arrData, 1)
For c = 1 To UBound(arrData, 2)
.Cell(r, c).Range.Text = arrData(r, c)
Next c
Next r
'Format the Table
.AutoFitBehavior (wdAutoFitContent)
.Style = "Table Grid"

End With
wdApp.ScreenUpdating = True

Debug.Print "Writing to each Cell took: " & Timer - StopWatch
'##########

'Header for Second Test
With wdApp.ActiveDocument.Range
.InsertAfter vbCrLf
.InsertAfter "Here's the Data Pasted from an Excel Range"
.InsertAfter vbCrLf
End With

Set myRange = wdApp.ActiveDocument.Content
myRange.Collapse Direction:=wdCollapseEnd

'##############
'Move array to Excel and then paste excel range to document as table

StopWatch = Timer
'Move the array to an Excel Sheet
ActiveSheet.Range("a1").Resize(UBound(arrData, 1), UBound(arrData, 2)).Value = arrData()
'Copy the Range created by dumping the Array to the Sheet
ActiveSheet.UsedRange.Copy
'Paste the Range to Word as a Table
myRange.PasteExcelTable False, True, False
'Format the Table
With wdApp.ActiveDocument.Tables(2)
.AutoFitBehavior (wdAutoFitContent)
.Style = "Table Grid"
End With

'Clean up Excel
Application.CutCopyMode = False
ActiveSheet.UsedRange.Clear


Debug.Print "Going to Excel and then pasting Excel Table took: " & Timer - StopWatch

'#################

Set wdApp = Nothing

End Sub

[Frosty]

First report back using your code (Word not open)...
Writing to each Cell took: 0.41796875
Going to Excel and then pasting Excel table took: 0.49609375

But after that, the going to excel and then pasting it back was clearly the faster method, without altering your code.

Here are my adjustments.

At the end of the day, I don't think I can get inserting into the word table cell by cell any faster than the excel, although they actually seem to be comparable... so it ends up being a "pick your flavor" kind of thing.

Couple of notes from my testing:

1. Having ScreenUpdating = True before your first "mark" of the stop watch slowed you down. Changing that property from false to true also triggers a screen refresh, so you were handicapping Word a little there. ;)

2. All optimizing "jiggling" aside... I seem to get the best results on a consistent basis by having word open already, and setting wdapp.visible = false, only showing it at the end of the routine. This saves time using both methodologies, although it saves more time using method #1, since there are more desires to "paint" by the application when you're inserting stuff into each cell rather than a single paste.

3. I don't know if using GetObject will work for your application... but I put it in simply for easy of testing what bits seemed to work better.

4. I got rid of a couple of your linear With statements... I haven't tried to really optimize VBA in a long time, because, in my experience, there's very little bang for the buck, but I vaguely remember that With statements slow things down slightly. But I would never sacrifice ease of reading code for some sort of perceived marginal optimization.

I just found this easier to read

wdapp.ActiveDocument.Range.InsertAfter vbcr & _
"Here's the Data Pasted from an Excel Range" & vbcr
than this

With wdApp.ActiveDocument.Range
.InsertRangeAfter vbcrlf
.InsertRangeAfter "Here's the Data Pasted from an Excel Range"
.InsertRangeAfter vbcrlf
End With

But that's purely a style thing.

Thanks for commenting so clearly... very easy to go through and see what you were doing.

Here's the modified code:

Option Explicit

Public Sub arrToDoc(Optional bTestingOptimizations As Boolean = True, _
Optional bQuit As Boolean = False)
Dim arrData(1 To 15, 1 To 5)
Dim r As Integer, c As Integer
Dim StopWatch As Double
Dim wdApp As Word.Application
Dim myTbl As Word.Table
Dim myRange As Word.Range

'make the test array
For r = 1 To UBound(arrData, 1)
For c = 1 To UBound(arrData, 2)
arrData(r, c) = r * c
Next c
Next r

'use current instance, if available
On Error Resume Next
Set wdApp = GetObject(Class:="Word.Application")
If Err.Number <> 0 Then
Err.Clear
'Start Word
Set wdApp = CreateObject("word.application")
End If
On Error GoTo 0

'open a new document to work on
wdApp.Documents.Add

'do these optimize?
If bTestingOptimizations Then
wdApp.Visible = False
Else
wdApp.Visible = True
wdApp.ScreenUpdating = False
End If

'Header for first test
wdApp.ActiveDocument.Range.InsertAfter _
"Here's the Data added to a Word Table Cell by Cell" & vbCr

Set myRange = wdApp.ActiveDocument.Content
myRange.Collapse Direction:=wdCollapseEnd

'############# METHOD #1
'start our timer
StopWatch = Timer

'get our new table
Set myTbl = wdApp.ActiveDocument.Tables.Add(Range:=myRange, _
NumRows:=UBound(arrData, 1), NumColumns:=UBound(arrData, 2))

'write array to the document's table cell by cell
With myTbl
'Fill the Table
For r = 1 To UBound(arrData, 1)
For c = 1 To UBound(arrData, 2)
.Cell(r, c).Range.Text = arrData(r, c)
Next c
Next r
'Format the Table
.AutoFitBehavior (wdAutoFitContent)
.Style = "Table Grid"

End With

'##########
'mark our time
Debug.Print "Writing to each Cell took: " & Timer - StopWatch

'we don't need a refresh here,
If Not bTestingOptimizations Then
wdApp.ScreenUpdating = True
End If

'Header for Second Test
wdApp.ActiveDocument.Range.InsertAfter vbCr & _
"Here's the Data Pasted from an Excel Range" & vbCr

Set myRange = wdApp.ActiveDocument.Content
myRange.Collapse Direction:=wdCollapseEnd

'############## METHOD #2
'Move array to Excel and then paste excel range to document as table

StopWatch = Timer
'Move the array to an Excel Sheet
ActiveSheet.Range("a1").Resize(UBound(arrData, 1), UBound(arrData, 2)).Value = arrData()
'Copy the Range created by dumping the Array to the Sheet
ActiveSheet.UsedRange.Copy
'Paste the Range to Word as a Table
myRange.PasteExcelTable False, True, False
'Format the Table
With wdApp.ActiveDocument.Tables(2)
.AutoFitBehavior (wdAutoFitContent)
.Style = "Table Grid"
End With

'Clean up Excel
Application.CutCopyMode = False
ActiveSheet.UsedRange.Clear

'mark our time used for the excel process
'#################
Debug.Print "Going to Excel and then pasting Excel Table took: " & Timer - StopWatch

If bTestingOptimizations Then
wdApp.Visible = True
End If

If bQuit Then
wdApp.Quit False
End If

Set wdApp = Nothing

End Sub

[Shred Dude]

Frosty:

Good catch on me setting the screen updating to false only after adding a table with the timer already ticking. Mistake on my part, not trying to give Word a handicap!

BTW, I like the way you structured the options for this test harness. Made it very easy to manipulate. What I found most interesting was the impact of using Get Object to grab an existing instance versus using a new one. If I comment out the Get Object part, and force it always grab a new instance of word, Method #1 and Method #2 are very close in timings. Otherwise, if there was an instance of Word open, the Excel Method #2 is much faster. Why would that be? Overhead within existing word app of another document being open, and using ActiveDocument?

Allowing it to use Get Object, when Word is already open (two times):

call arrtodoc(true, True)
Writing to each Cell took: 1.10546875
Going to Excel and then pasting Excel Table took: 0.2734375
Writing to each Cell took: 1.21484375
Going to Excel and then pasting Excel Table took: 0.27734375



Notice how Method #2 is always faster if you've started off by using an existing instance of Word with Get Object than if you've started Word with Create Object

Commenting out Get Obejct part, forcing it Create New Instance (two times):

call arrtodoc(true, True)
Writing to each Cell took: 0.40625
Going to Excel and then pasting Excel Table took: 0.35546875
Writing to each Cell took: 0.41015625
Going to Excel and then pasting Excel Table took: 0.35546875




Throwing bOptimzations to False, relying on Screen Updating to false when app is visible, clearly made things take longer. That's an interesting lesson for me regarding the screen repainting in Word. I don't think I've noticed that with my work in Excel. There, if I turn screen updating off it has a dramatic impact even with the app visible.

call arrtodoc(false, true)
Writing to each Cell took: 0.9296875
Going to Excel and then pasting Excel Table took: 0.54296875
Writing to each Cell took: 0.92578125
Going to Excel and then pasting Excel Table took: 0.546875
Writing to each Cell took: 1.3046875
Going to Excel and then pasting Excel Table took: 0.5625





Big lesson here for me is impact of visible=false as opposed to screenupdating = false.

Thanks for your help in delving into this little exercise.

Shred

[Frosty]

Glad to help.

I'm not sure why creating a new instance does such different results than using the existing instance. I found the excel times to be fairly constant (simply dependent on whether the word app was visible or not), while the word times varied hugely depending on various "jiggling" (a lot of which I didn't show you).

There's probably a lot going on there which isn't really important...but suffice to say that my times where considerably different from yours, although the ratios weren't all that different. Bottom line, I don't think you can go wrong in this application by using method #2.

It's good to know that Application.ScreenUpdating= False is simply a *request* whereas myApp.Visible=False is a *command.*

Word will ignore the screenupdating thing, but the object won't ignore the visible instruction.

As additional info... when I was testing creating a new instance of word (by forcing it to .quit each time)... if I re-ran the procedure too quickly, I would get an error... as if Word hadn't fully finished closing yet, and so the GetObject had grabbed the *old* instance, created the new document, and then closed. Just something to note, in the timing of it. I've got a pretty quick machine though.

[fumei]

Comments.

Frosty: "But I would never sacrifice ease of reading code for some sort of perceived marginal optimization. "

Well spoke. Although the multiple string With statement is not a good example. One should NEVER use code like:
With wdApp.ActiveDocument.Range
.InsertRangeAfter vbcrlf
.InsertRangeAfter "Here's the Data Pasted from an Excel Range"
.InsertRangeAfter vbcrlf
End With
Not because it is a With, but because it is THREE separate string instructions. Of course
wdapp.ActiveDocument.Range.InsertAfter vbcr & _
"Here's the Data Pasted from an Excel Range" & vbcr
is better...it is only ONE instruction.

AFAIK, there is no method, in Word, to dump an array into separate table cells. Each cell range is independent, and must be written to as such.

"Big lesson here for me is impact of visible=false as opposed to screenupdating = false."

AND

"It's good to know that Application.ScreenUpdating= False is simply a *request* whereas myApp.Visible=False is a *command.*

Not really. They are both instructions. .Visible = False is an instruction to remove the object from the GUI. Therefore NO further calls are made to the GUI. ScreenUpdating = False is an instruction to block actioning of calls to the GUI, but the calls are still made.

<change of something that affects the GUI>

.Visible = False DO NOTHING
.ScreenUpdating = False send instruction to GUI; once received do nothing

ScreenUpdating is fine if the disappearance of the application would be weird. This is a major reason (as has been stated elsewhere) that Range is better than Selection...no intermediary GUI calls.

BTW: while it may seem that if you can handle the application disappearing completely (.Visible = False), it may be better to do that, there is an overhead to making it Visible. generally speaking, if you are doing actions in the same application, using Visible = False is not so great.

However, if you are doing actions between applications - i.e. you are creating an coded instance - then doing actions with the created instance set as Visible = False can speed things up a fair bit.

[Frosty]

Gerry, you are, of course, correct. They are both instructions. And you clearly have a better understanding of the two as they relate to the OS than I do.

The point I was trying to make, however unclearly, was that once you have used .Visible = False... I've never seen application object become Visible again until I set .Visible = True, no matter what other code I've had between the two instructions.

The same is not true of .ScreenUpdating, in my experience. No matter how "well" you program your routine (avoiding the selection object, etc), Microsoft will repaint the application before you turn .ScreenUpdating back on (either by setting it to true or using .ScreenRefresh method).

In my experience, it generally has something to do with pagination calculation, but I'm pretty sure there are other general triggers as well.

Of course, as Gerry pointed out, there may be other issues with having the application object disappear on you during processing.

As an additional thought for ShredDude... maybe set a flag to see whether you used GetObject or CreateObject... and if you used CreateObject set the wdApp.Visible = False... but if you used GetObject use the below code:

Dim oDoc as Word.Document

'(and then use oDoc for the rest of your references, rather than ActiveDocument)
Set oDoc = wdApp.Documents.Add
oDoc.Windows(1).Visible = False

'do the processing

'show the window
oDoc.Windows(1).Visible = True

In my experience, again, nothing you do will cause that window to become visible... but I find the .ScreenUpdating method to be fairly unreliable in Word, depending on the kind of stuff I'm doing.

Of course, it's a necessary evil at times because you can't just necessarily have everything disappear and pop back up just to save a tenth of a second, but for this application I think it would probably be okay.

[fumei]

The issue of tenth of a second popping is precisely why using Visible on the application you are IN, is not so great. BUT if you are working on another application, then doing its actions while it is not Visible is a very good idea.

Frankly, I rarely use ScreenUpdating, as 99.99% of the actions I do in Word use range.

And yup, Word is not reliable regarding screenupdates, and yup for the most part it stems from pagination.

[Frosty]

I agree, I think if you're going to have something disappear, it needs to a) be worth the time and b) inform the user of what is going on, so they don't think the app has crashed.

Two examples:
1. I regularly open up a separate session of Excel to grab some data, load some information for Word and close Excel. I obviously don't need Excel visible to do this, and as long as I've error trapped if the process of grabbing data fails... I certainly don't need to see Excel at all during this process.

2. I wrote a numbering conversion function at some point, which was fairly elaborate. However, because it interacted with a macro package which clearly utilized the selection object in order to apply formatting, I was trying to speed up the processing of re-formatting 50+ page documents. The best I came up with, at the time, was switching to normal view and the reduced zoom. That worked out, because users could "see" the processing of the document (which could take 30-45 second on 100+ page documents), but I wonder if it wouldn't have been faster to hide the document window and simply show a custom modal user form with a progress bar. Hmmm...

[fumei]

at 30 - 45 seconds, hiding with a progress bar would - generally - be "better" (i.e. more polite, as well as faster).

[Shred Dude]

Fumei & Frosty:

Thanks so much for your continued discussion. Some little tidbits here and there have really helped drive home some points for me while trying to gain a better understanding of working with Word.

The project i'm working on now is shaping up nicely, and running much more efficiently than my first iterations on it.

Thanks for all the input.

Shred

[macropod]

Here's another pointer for improving execution speed when working with Word tables:

If automatic column widths are enabled for a table, the constant re-calculation of the column widths after any string manipulation will slow down the macro’s execution. The impact can be significant with long tables. To avoid this, either set:
MyTable.AllowAutoFit = False
(corresponding to Table|Table Properties|Options|'Automatically resize to fit contents'), or use:
MyTable.AutoFitBehavior wdAutoFitFixed