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
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)
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