Acad Table — Array — Excel Sheet

EXCEL — ARRAY — ACAD TABLE

Two way transfer of data to/from autocad table from/to excel worksheet using arrays.

Excel is the BOM (or cut-list) that Autocad has always needed, but editing tables in Autocad is slow and awkward. Being able to dump them back to excel, edit, then re-load them to the same table is sometimes much faster than editing in place.

We need 4 basic methods.

Excel range to array
Array to excel range
Autocad table to array
Array to autocad table

We will make functions and return the newly made or modified object. We will take object arguments for the input. We will keep these as single purpose as possible to be used by multiple calling programs.

Excel makes it easy to transfer sheet contents to an array or vice versa. It is simply

Array = range
Or
Range = array

There are some details. Acad Table requires a loop both to get and put data. When we do Array = Range then excel sizes the array automatically. We defined the range. When we make the array from the acad table, we Redim array (1 to rows, 1 to cols).

The four main sub/functions are –

Function xl_to_arr (rng as range) as variant
Sub arr_to_xl (arr as variant, rng as range)

Function acadtbl_to_arr(tbl as acadtable) as variant
Sub arr_to_acadtbl (arr as variant, tbl as acadtable)

If you use this method to delete rows (in autocad), keep the same number, re-sort rows, this works great as-is. You are just changing data. If you add rows or columns, you will likely have formatting problems. You can manually fix them. To do so programmatically, you would copy the formatting of the row above or column to the left. Its worth doing, but definitely non-trivial. Autocad tables have nearly 100 methods and properties. The vba help files for tables seem like many just appear to be stubs. For me, changing the alignment, tbl.SetCellAlignment r, c, acMiddleLeft Right or Center, and changing the texheight, SetTextHeight acDataRow (as opposed to Title or Header) dblvalue, do the job. Alignment is set per cell, and textheight only has to be set once if all rows are data rows. Non-trivial but worth doing.

This would be a good place to have a long discussion about arrays, but i am going to table that til i have my information better organized. Static arrays are not very useful, but all texts start there. Dynamic arrays are the norm. They can also accept assignment, which is not covered at all in most texts. I dont know when vba changed that, but they must have. There is no reason i have found not to use them all the time. Autocad uses variant arrays in their help when dynamic arrays would work fine. A variant data type can contain an array, and is the only choice most of the time to read and write to excel. Also the data in a table is going to be a combination of strings, integers and doubles, so a dynamic array of a single type wont work. I have a post in mind that is nothing but arrays.

An array made from an excel sheet always has a lower index of 1, no matter what the option base is. Autocad tables always have first row and column index of zero. Keep that straight and your loops will be simpler. I save the array from autocad with a base of 1.

For r = 1 To rows
For c = 1 To cols
arr(r, c) = tbl.GetText(r – 1, c – 1)
Next c
Next r

Traditional row and column variables for looping thru a table are i and j. I prefer r and c, row and column, for legibility. Rows always come first. (r,c) as distinct from excel sheet nomenclature (“A1”). Autocad tables are (r,c)

The four main subs are here, followed by the two calling programs, tbl_to_xl and xl_to_tbl. There is also a get_table function to return the object table from autocad by user selection. This could be selected other ways, such as by a location of the upper left of the table, if it is always at the same location. And last, there is an array report that writes to the debug window that i was using while developing and did not want to erase.

I can run both sides of the program from the code window or a button on a form. I have a sheet in excel with the proper name.

'the four main function subs
Function xl_to_arr(rng As Range) As Variant
     
     xl_to_arr = rng
     
End Function


Sub arr_to_xl(arr As Variant, rng As Range)
    Dim rows As Integer, cols As Integer
    rows = UBound(arr, 1) - LBound(arr, 1) + 1
    cols = UBound(arr, 2) - LBound(arr, 2) + 1
    
    'resize the range to be same as array
    Set rng = rng.Resize(rows, cols)
    
    rng.Value = arr
    'data is on the sheet
    
End Sub

Function acadtbl_to_arr(tbl As AcadTable) As Variant
    Dim r As Integer, c As Integer
    Dim rows As Integer, cols As Integer
    
    rows = tbl.rows
    cols = tbl.Columns
    
    Dim arr As Variant
    ReDim arr(1 To rows, 1 To cols)
    
    For r = 1 To rows
        For c = 1 To cols
            arr(r, c) = tbl.GetText(r - 1, c - 1)
        Next c
    Next r
    
    acadtbl_to_arr = arr

End Function


Sub arr_to_acadtbl(arr As Variant, tbl As AcadTable)

    Dim rowLbound As Integer, rowUbound As Integer
    Dim colLbound As Integer, colUbound As Integer
    Dim rows As Integer, cols As Integer
           
    rowLbound = LBound(arr, 1)
    rowUbound = UBound(arr, 1)
    colLbound = LBound(arr, 2)
    colUbound = UBound(arr, 2)

    rows = rowUbound - rowLbound + 1
    cols = colUbound - colLbound + 1
 
    'resize the autocad table
    tbl.rows = rows
    tbl.Columns = cols
  
  Dim r As Integer, c As Integer
  
If rowLbound <> 1 And colLbound <> 1 Then
MsgBox "Lbound not eq 1 in arr to acadtbl, exiting"
Exit Sub
End If

     For r = 1 To rows
         For c = 1 To cols
             If Not IsEmpty(arr(r, c)) Then
                tbl.SetText r - 1, c - 1, arr(r, c)
             End If
          Next c
       Next r

    acadApp.Update
     
End Sub

i use a couple globals in the calling programs.


Public g_tbl As AcadTable
Public g_arr As Variant

Sub tbl_to_xl()
    Call Connect_Acad
    
    Set g_tbl = get_table
     
     If g_tbl Is Nothing Then
     'MsgBox "table is nothing"
     Exit Sub
     End If
         
    g_arr = acadtbl_to_arr(g_tbl)
    
    Call arr_report(g_arr)
        
    Dim ws1 As Worksheet, rng As Range
    Set ws1 = ThisWorkbook.Sheets("Chan_List")
    Set rng = ws1.Range("A1")
        
    Call arr_to_xl(g_arr, rng)
    
    ws1.Activate
End Sub

Sub xl_to_tbl()
Call Connect_Acad

    If g_tbl Is Nothing Then
     Set g_tbl = get_table
     End If
 
    Dim ws1 As Worksheet, rng As Range
    Set ws1 = ThisWorkbook.Sheets("Chan_List")
    Set rng = ws1.Range("A1")
    Set rng = rng.CurrentRegion
     
    g_arr = xl_to_arr(rng)
    
     Call arr_report(g_arr)
    
    Call arr_to_acadtbl(g_arr, g_tbl)
End Sub


Function get_table() As AcadTable
        Dim pt() As Double
        Dim obj As Object
        On Error Resume Next
        
        acadDoc.Utility.GetEntity obj, pt, "Select a table"
    
    If Err <> 0 Or obj.EntityType <> acTable Then
        Err.Clear
        MsgBox "table not selected"
        Exit Function
    End If
    
    On Error GoTo 0
      
    If obj.EntityType = acTable Then
       Set get_table = obj
    End If
    
End Function


Sub arr_report(arr As Variant)
    
    Dim rowLbound As Integer, rowUbound As Integer
    Dim colLbound As Integer, colUbound As Integer
    Dim rows As Integer, cols As Integer
    
    rowLbound = LBound(arr, 1)
    rowUbound = UBound(arr, 1)
    colLbound = LBound(arr, 2)
    colUbound = UBound(arr, 2)
    
    rows = rowUbound - rowLbound + 1
    cols = colUbound - colLbound + 1
    
    Debug.Print "arr: " & IsArray(arr)
    Debug.Print "arr: " & VarType(arr)
    Debug.Print "arr: " & TypeName(arr)
     
    Debug.Print "row : " & rowLbound & " to "; rowUbound
    Debug.Print "col : " & colLbound & " to "; colUbound
    Debug.Print "rowcount: " & rows
    Debug.Print "colcount: " & cols
    
    Debug.Print
 
End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.