Performance issued with VBA Code

[Rama]

Hi All,

I have a VBA code which generates a word document from oracle data base.
i get the data from oracle 1800 tables, at the end the document will be
having 450 pages.

It runs fast upto first 100 tables then it will slow down, i go to back
end to get the data from each table.

if i comment out the code in both the for loops it completes in 3 minutes.
other wise it takes 2 hours.

so i guess, creating the table headers and populating the data into table
is a bottle
neck in the following function.

any idea or suggestions are welcome.
please see the sample function:

Public Function GenerateTable(Obj As TableObj, TADO As Object)
Dim x, j As Integer
Dim FieldText As String
Dim Width As Double
Dim ModBitFlag As Variant
Dim ModBitSet As Integer
Dim myRange As Object
Dim ModBitArray(1) As Long
Dim nSavePoint As Integer

nSavePoint = 0
' ActiveDocument.Styles(wdStyleHeading1).Font.Bold = False
' .Bold = True
' .Name = "Arial"
' .Size = 24

Set myRange = RamaObj.Range
myRange.Collapse (wdCollapseEnd)
With myRange
.InsertParagraphBefore
.Style = wdStyleNormal
' .Style = wdStyleHeading4
' TObj.DataStyle
End With

Set OTable = RamaObj.Tables.Add(myRange, Obj.NumRecs + 1, Obj.NumCols,
wdWord9TableBehavior, wdAutoFitFixed)
OTable.LeftPadding = 1
OTable.RightPadding = 1
OTable.Borders.InsideLineStyle = wdLineStyleSingle
OTable.Borders.OutsideLineStyle = wdLineStyleSingle
OTable.Rows.Alignment = Obj.TableAlignment
OTable.Rows.HeightRule = wdRowHeightAuto
OTable.Rows.Height = 8

'-------------------------------------------------
' initialize table and populate column headers
'-------------------------------------------------
For x = 0 To Obj.NumCols - 1
Width = CDbl(Obj.Cols(x).Width)
OTable.Columns(x + 1).SetWidth ColumnWidth:=InchesToPoints(Width),
RulerStyle:=wdAdjustNone
If Obj.Cols(x).DispHead = 1 Then
OTable.Cell(Row:=1, Column:=(x + 1)).Range.InsertAfter
Text:=Obj.Cols(x).Head
'OTable.Cell(Row:=1, Column:=(x + 1)).Range.Style = Obj.HeadStyle
End If
Next

'RamaObj.Save

'-------------------------------------------------
' Populate Table data
'-------------------------------------------------
TADO.MoveFirst
j = 2
While Not TADO.EOF
nSavePoint = nSavePoint + 1
UserForm1.CurrentCTRow.Text = "" & j - 1
DoEvents ' Refresh dialog
ModBitFlag = TADO(Obj.ModOffset)
ExtractModBit ModBitModulo, ModBitFlag, ModBitArray

For x = 0 To Obj.NumCols - 1
FieldText = "" & TADO(Obj.Cols(x).Offset)
ModBitSet = TestModBit(ModBitModulo, Obj.Cols(x).Offset,
ModBitArray)
If FieldText <> "" Then
OTable.Cell(Row:=j, Column:=x + 1).Range.InsertAfter FieldText
' OTable.Cell(Row:=j, Column:=x + 1).Range.Style =
Obj.DataStyle
' If ModBitSet Then
' OTable.Cell(Row:=j, Column:=x + 1).Shading.Texture =
wdTexture15Percent
' End If
End If
Next

j = j + 1

If nSavePoint = 10 Then
'RamaObj.Save
nSavePoint = 0
End If


TADO.MoveNext
Wend
OTable.Rows.HeightRule = wdRowHeightAuto
RamaObj.Save

End Function

 

[Jezebel]

Instead of formatting the table on each call, predefine the table - with all
formatting applied - as an autotext entry. Then simply insert the autotext
entry ready to go.

Another approach is to insert the data as plain text, then subsequently go
through and use the 'Convert Text to Table' function. Also consider doing
the whole exercise in Excel: it's *much* faster with bulk data, and the
formatting is pretty much as sophisticated, at least for tabulated data.

A couple of minor points that will also improve performance, although not
greatly:

1) Don't use integers. Longs are faster. (Windows is a 32 bit system, so
integers mean extra work, chopping the value back to 16 bits.)

2) Do ...Loop is faster than While ... Wend

3) Instead of 'until TADO.EOF' it is quicker to use something like

pCount = TADO.RecordCount
For i = 1 to pCount
....

If you use .EOF, the DBEngine has to check the recordset every time.