settext runs slowly

[mhardine]

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

[mhardine]

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

[andrewpuller3811 · Accepted solution]

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

[mhardine]

That was EXACTLY what the doctor ordered! Thanks a bunch. 720 pages of drawings listed  in 15 seconds!

Thank you very much.

D

[mhardine]

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