VBA writing Table

[grobnik]

Hi to all,

I facing off to write several Table Cells with the below procedure, but seems that the execution time it's very slow (and it's not tied with machine where Autocad is running).

I'm using Autocad 2022, with a table drawing of more or less 30 rows and 4 columns.

Population source it's an excel file not linked, but data are read from a VBA procedure.

Please is there a way to speed up the procedure ?

Here the code

LastRow = ObjExcel.Sheets("INDICE").Range("A3").End(xlDown).Row
For MyExRow = 3 To LastRow
        MyShNum = ObjExcel.Sheets("INDICE").Cells(MyExRow, 1).Value
        MyDescription = ObjExcel.Sheets("INDICE").Cells(MyExRow, 2).Value
        MyDATE = ObjExcel.Sheets("INDICE").Cells(MyExRow, 3).Value
        Call MyTable.SetCellValue(MyTRow, 0, MyShNum)
        Call MyTable.SetCellValue(MyTRow, 1, MyDescription)
        Call MyTable.SetCellValue(MyTRow, 2, MyDATE)
        MyDATE_B = ObjExcel.Sheets("INDICE").Cells(MyExRow, 4).Value
        If MyDATE_B <> "" Then
            Call MyTable.SetCellValue(MyTRow, 3, MyDATE_B)
        End If
        MyDATE_C = ObjExcel.Sheets("INDICE").Cells(MyExRow, 5).Value
        If MyDATE_C <> "" Then
            Call MyTable.SetCellValue(MyTRow, 4, MyDATE_C)
        End If
        
        MyTRow = MyTRow + 1
Next

[norman.yuan · Accepted solution]

You did not say, but it seems that your Acad VBA code communicates with external Excel application via COM API, which would be the main reason of slowness. Also the slowness may also affected by both applications (AutoCAD and Excel) when one of them or both of them changed to different versions, if you saw the slowness changed in comparison of the previous versions. There is not much one can do to the slow communication between 2 "out-process" applications.

However, if you are interested in finding out which part of code play bigger role in the slowness - AutoCAD updating table, or AutoCAD getting data from Excel app (out-process communication), separating the code of obtaining data and updating table would give your chance to test/tell where the slowness is from mainly; by the way it is also a coding practice of not mixing code for different functionality in the same routine. That is, you can re-organize your code like:

1. define a class and a collection, or simply and array of one or multiple dimensions to hold the data from external source;

2. read all data from external source at once;

3. Update the table with the data collection/array (so that the code does not read 1 cell from Excel sheet, update 1 cell the drawing table.

This way, you can measure which part cause the major slowness, and decide what to do (or not to do). Since the slowness is most likely because of reading sheet data from external Excel app, you could use other techniques to get data from Excel sheet, such as using MS Access DB Engine to read *.xlsx file; or convert the data in *.xlsx into *.csv format, so that you can easily read them as text; or use OpenXML (or whatever third party components available) to read data in the sheet. All these techniques would spare you from having to run Excel app and communicate via "out-process: COM API, thus speed up your data reading significantly.

[grobnik]

@norman.yuan 

Thank you very much for your suggestions, I'll try to have familiarity with other way to get data from excel and write data into the drawing table.

Could you help me creating a collection? Shall be at least 4 or more columns and several rows.

Other issue, reason of slow, could be related to the table which has been divided in more parts but the table object it's the same.

Thank you. 

[norman.yuan]

To store data in a collection, you basically create a class with fields/properties to hold each row of data in spreadsheet. When reading data from sheet, you instantiate a class for each row, and add the class instance into a collection.

In VBA editor, you add a "class module", name the class properly (say, TableRow), then simply add a few fields, like

Option Explicit

Public Field1 As String
Public Field2 As Integer
Public Field3 As Double

Ideally, you should add the field as private and expose them as properies with "Public Property Set/Get....()"

Then in your main process code module, you would have code like:

Option Explicit

private tableRowData As Collection

Public Sub FillUpTable()

  '' Code to connect to Excel app

  Dim excelApp As Excel.Application

  Set excelApp=........

  ''  because of the separation of the following 2 subroutines,

  ''  you could easily test which one takes more time

  ''  and you can change the code in the GetDataFromExcel() method

  '' with different approach to retrieve data, but how AutoCAD updates

  '' the table is not affected, as long as the data is held in a collection of TableRow class.

  Set tableRowData=GetDataFromExcel(excelApp)

  UpdateTable(tableRowData)

End Sub

private Function GetDataFromExcel(excelApp As Excel.Application) As Collection

  Dim col As New Collection

  Dim sheet As Excel.WorkSheet

  Set sheet=....''(find the data sheet)

  Dim i, j As Integer

  Dim rowData As TableRow

  For i=0 to ...

    Set rowData=New TableRow

    For j = 0 to ...

       Select case j

         Case 0

           rowData.Field1=sheet.Cells(i, j).Value.

        Case 1

          rowData.Field2=sheet.Cells(i, j).Value.

       Case 2

         ... ...

       End Select

    Next

    col.Add(rowData)

  Next

  Set GetDataFromExcel = col

End Function

Private Sub UpdateTable(tableData As Collection)

  Dim tbl As AcadTable

  Set tbl=..... '' find the target table

  Dim rowData As TableRow

  For Each rowData in tableData

    '' Update Table's row that corresponds to the data from spread sheet

  Next

End Sub

[grobnik]

@norman.yuan Thank you again, I'll try to apply your suggestion. Another little question from Table side. Is there a way to write data into a Table group of cells that are progressive ? I mean write data from table column 0 to 3 and in the same time from row 0 to 10 ? for example ?.

Thank you

[rmcefr]

Hello Sir,

I have the same issue, but i am reading data from MS SQL Server tables, 

I tried all mentioned comments but unfortunately, still work very slow,

is there another actions we could apply?

best regards

[Ed__Jobe]

---

@rmcefr wrote:

Hello Sir,

I have the same issue, but i am reading data from MS SQL Server tables,  

---

How are you reading from mssql? Show your code using the link in my signature.