Better Copy and Paste in word for tables

[rxs0569]

I wrote a macro that retrieves data from a database, populates a table
using bookmarks and then copies this table and pastes it at the end of
the document before moving to the second row of data.

I am looking for improving the efficiency and performance of this
macro.

1. Is there a better copy and paste algo for tables

2. can i clear the clipboard between each copy and paste. will it
improve the performance.

3. any other suggestions

Some pieces of the macro pasted below

Set oWorkSpace = session.GetWorkSpace
Set oQueryDef = oWorkSpace.GetQueryDef(strQueryName)

' REM execute the query
Set oResultSet = currentsession.BuildResultSet(oQueryDef)
oResultSet.Execute
longNumColumns = oResultSet.GetNumberOfColumns ' no of columns

' Start populating the template table
Row = 1
Status = oResultSet.MoveNext
NumRows = 0
Do While Status = 1
'REm Go to the template table
Selection.GoTo What:=wdGoToTable, Which:=wdGoToFirst, Count:=1,
Name:=""

Row = Row + 1
Dim strBookName As String
For Column = 2 To longNumColumns
'REM Fill it in the bookmark
strBookName = "field" & (Column - 1) ' field1, filed2 etc
UpdateBookmark strBookName,
CStr(oResultSet.GetColumnValue(Column))
Next

'REM Done with the row, Copy the table and paste it below
Selection.Tables(1).Select
Selection.Copy

Selection.EndKey Unit:=wdStory
Selection.InsertBreak Type:=wdPageBreak
Selection.PasteAndFormat (wdPasteDefault)

' Move Next
Status = oResultSet.MoveNext
NumRows = NumRows + 1
Loop

' Clear the first table
CleanAllBookMarks

Exit Sub

 

[Jezebel]

Why bother with the bookmarks. Why not just populate the table directly? --

With ActiveDocument.Tables(1)
For pRow = 1 to oResultSet.GetNumberOfColumns
For pColumn = 1 to oResultSet.GetNumberOfColumns
.Cells(pRow, pColumn) = oResultSet.GetColumnValue(Column)
Next
Next
End with


I don't understand what you're doing with the copy and paste, but clearing
the clipboard will have no effect anyway.

 

[rxs0569]

Well The design of the macro is such that every row corresponds to a new
table. so if there are 50 records then 50 corresponding tables are generated.
So I have a template table where i give the creator the freedom of laying the
data as they wish and apply the formatting as they want. This give me the
flexibility of adapting the macro and the template to any query the user may
have.

Anyway My questions are with performance. I am a newbie in VBA so I am
looking at suggestions at improving performance especially in copy and paste
ad with clipboards

Raj

 

[Jezebel]

OK, if you are creating tables, still better to create them on the fly than
work with one and use copy and paste (and this will give you better
performance also) --

Dim pTable as Word.Table

with activedocument

'Iterate the rows in the source data
For pRow = 1 to oResultSet.GetNumberOfRows

'Insert the table for this row
set pTable = .Tables.Add _
Range:=.Range(.Content.End - 1,
..Content.End), _
NumRows:=1, _
NumColumns:=
oResultSet.GetNumberOfColumns

'Populate the table from the current row
For pColumn = 1 to oResultSet.GetNumberOfColumns
pTable.Cells(pRow, pColumn) =
oResultSet.GetColumnValue(Column)
Next
Next

End With

 

[rxs0569]

Thanks Jezebel. But your approach defeats a primary intent. I want to give
the user the flexibility of creating tables wit their own formatting options.
They may choose to apply background, fonts etc. If i create tables on the fly
I cannot capture these user customizations.

 

[Jezebel]

Define a table style. Let the user customise that as they choose. Apply that
style to each table you create.