Stepping through Table Cells & Replacing the 'End-of-line' Character

[Anonymous]

I have been searching through the this thread and haven't been able to find anything.  

Disclaimer, I have done quite a bit of VBA, but none in AutoCAD so I am looking for starting points and ideas for the following:

I have a table that is being automatically generated by AutoCAD Electrical.  It looks thusly:

Each of the tags, CRxxx have an 'end-of-line' character after them forcing each onto its own line.  Multiple cells have the same problem and I would like to replace the 'end-of-line' character with a ", " (comma + space).  Thusly:

Here's how I see what is needed

1.  I need to be able to select a column in the table - getting user input to select table, column, or cell.

2.  I then need to be able to select cells and step through each cell in the table.

3.  I need to select the text and replace all the 'end-of-line' characters with whatever text I would like, in some cases I just need to delete the line of text altogether.  I will not need to alter the Row/Column/Table - just the text in the cells.

I really am just looking for some starting points, what objects do I need to declare, how do I get around in the object, etc...?

Finally, any one have a good, easily accessed tutorial that would get me started in the basics of VBA for AutoCAD?

Final disclaimer, I am running ACAD 2016 with VBA enabled & installed.  I have tried to get Visual Studio Community(Visual Basic) installed so I could go the .NET route for writing this, but alas it will not install on my Windows 7, SP1 computer - even the IT Admin has failed miserably.  So if I want this done it has to be in VBA...

Thanks in advance!

[norman.yuan · Accepted solution]

if the cells in the table is just simple text, it would be quite easy to get/set cell value with AcadTable.Get[Set]CellValue() method.

Following VBA code show how to obtain cells' value, parse it to remove Carriage Return, New Line Feed value, or both (vbCr, vbLf, or VbCrLf), and replace with ", ". That is, originally, each cell contains string value "AAAAA" & vbcCrLf & "BBBBB" & vbCrLf & "CCCCC" & vbCrLf & "DDDDD":

Option Explicit

Public Sub TableTest()

    Dim tbl As AcadTable
    Set tbl = SelectTable()
    If tbl Is Nothing Then Exit Sub
    
    MsgBox "Table has " & tbl.Columns & " columns and " & tbl.Rows & " rows"
    
    Dim i As Integer
    Dim j As Integer
    
    Dim curValue As String
    Dim newValue
    
    For i = 2 To tbl.Rows - 1
        For j = 0 To tbl.Columns - 1
            curValue = CStr(tbl.GetCellValue(i, j))
            MsgBox "Current value: " & vbCrLf & vbCrLf & curValue

            ''Replace possible vbCr
            newValue = Replace(curValue, vbCr, ", ")

            ''Then replace possible vbLf
            If InStr(1, newValue, ", ") > 0 Then
                newValue = Replace(newValue, vbLf, "")
            Else
                newValue = Replace(newValue, vbLf, ", ")
            End If

            MsgBox "Updated value: " & vbCrLf & vbCrLf & newValue
            tbl.SetCellValue i, j, newValue

        Next
    Next
    
End Sub

Private Function SelectTable() As AcadTable

    Dim ent As AcadEntity
    Dim pt As Variant
    Dim tbl As AcadTable
    
    ThisDrawing.Utility.GetEntity ent, pt, vbCr & "Select the table:"
    If Not ent Is Nothing Then
     If TypeOf ent Is AcadTable Then
        Set tbl = ent
     End If
    End If
    
    Set SelectTable = tbl
    
End Function

To see how this code work, see this video link 

HTH