I'm using the following to populate a table. Not included here is a step where the record set is counted to determine the number of rows required. The table is then created and the last step is to populate the cells with data.
'building a set of records from the connection string based on SQL statement
Set myrs = myConnection.Execute(myss)
varRow = 2
Do Until myrs.EOF
varSheetNumber = myrs("Sheet_Num")
varDescription = myrs("Item_Description")
varRoom = myrs("Room")
varFloor = myrs("Floor")
varTitle = myrs("Drawing_Title")
table.SetText varRow, 0, varSheetNumber
table.SetText varRow, 1, varDescription
table.SetText varRow, 2, varRoom
table.SetText varRow, 3, varFloor
table.SetText varRow, 4, varTitle
varRow = varRow + 1
myrs.MoveNext
Loop
'closing dbase connections
dbclose
Set myrs = Nothing
Set myss = Nothing
End If
Now, if i comment out the Table.Settext lines the routine runs in a fraction of a second, even when the recordset contains hundreds of records.
Once I run those lines though, it can take upwards of 10 minutes to populate the table if there are a couple hundred records.
Anyone know why, or have a better way of doing this?
Thanks in advance
Solved! Go to Solution.
Solved by andrewpuller3811. Go to Solution.
I timed it more carefully and it took about 1.6 seconds per row. A 220 row table took about 6 minutes to populate. Lots of noise from my laptop as well, and some general freezing up which subsided once the routine competed
Part of a sub I use to insert a table. I have had up to about 150 rows and it occurs instantly (about 1-2 sec). I think the regeneratetablesuppressed, recompute tableblock, titlesuppressed and headersuppressed are what you need to speed it up.
With acTable
.RegenerateTableSuppressed = True
.RecomputeTableBlock False
.TitleSuppressed = False
.HeaderSuppressed = False
'call to function to check if table style exists.
If AWPJ_TableStyleExists(strTableStyleName) Then
'tablestyle good, so set it to use
.StyleName = strTableStyleName
'else 'style doesn't exist so leave set to default
End If
.SetTextStyle AcRowType.acTitleRow, strTextStylename
.SetTextStyle AcRowType.acHeaderRow, strTextStylename
.SetTextStyle AcRowType.acDataRow, strTextStylename
.SetColumnWidth 0, intCol1
.SetColumnWidth 1, intCol2
.SetColumnWidth 2, intCol3
.SetColumnWidth 3, intCol4
.SetColumnWidth 4, intCol5
.SetColumnWidth 5, intCol6
' Title
.SetCellTextHeight i, j, 3#
.SetCellAlignment i, j, acMiddleLeft
.SetRowHeight i, 8#
.SetCellBackgroundColorNone i, j, True
col.ColorMethod = AutoCAD.acColorMethodByBlock
.SetCellContentColor i, j, col
.SetCellType i, j, acTextCell
strTitleText = "{\f" & strTextStylename & "|b1|i0|c0|p34;\C0;OWNERS DETAILS}"
.SetText i, j, strTitleText
col.SetRGB 255, 0, 0
For j = 0 To .Columns - 1
.SetCellGridColor i, j, 4, col
Next j
' header
i = i + 1
col.SetRGB 255, 0, 0
.SetCellGridColor i, 0, 8, col
.SetCellGridColor i, .Columns - 1, 2, col
col.SetRGB 255, 0, 255
.SetCellGridColor i, j, 4, col
For j = 0 To .Columns - 1
.SetCellBackgroundColorNone i, j, True
col.SetRGB 255, 0, 255
.SetCellGridColor i, j, 4, col
col.ColorMethod = AutoCAD.acColorMethodByBlock
.SetCellContentColor i, j, col
.SetCellTextHeight i, j, 2.5
.SetCellAlignment i, j, acMiddleLeft
.SetRowHeight i, 6.5
.SetCellType i, j, acTextCell
.SetText i, j, strHeaderText(j)
Next
' fill data cells - Row 2 -> last row
' interate through strOwnersTable() to fill table
For i = 0 To .Rows - 3
.SetRowHeight i + 2, 6.5
For j = 0 To TableColumns - 1
.SetCellStyle i + 2, j, "Data"
.SetCellBackgroundColorNone i + 2, j, True
col.ColorMethod = AutoCAD.acColorMethodByBlock
.SetCellContentColor i + 2, j, col
.SetCellTextHeight i + 2, j, 2#
.SetText i + 2, j, strOwnersTable(j + 1, i + 1)
Next j
Next i
.RegenerateTableSuppressed = False
.RecomputeTableBlock True
.Update
End With 'end With acTable
That was EXACTLY what the doctor ordered! Thanks a bunch. 720 pages of drawings listed in 15 seconds!
Thank you very much.
D
One more thing, you seem do be doing the same thing as I- pulling stuff from a table in a database, but your method makes me think of mine as somewhat barbaric! I'm relying on first creating a recordset to count the number of rows needed, then creating the table object with the appropriate number of colums and rows, then populating the title and first row, and finally reconnecting to the database and pulling out the recordset to then populate the cells.
Would you be willing to share a bit more of that sub? I'd love to learn a more elegant approach.
Cheers,
D