topic Re: Export table using VBA in VBA Forum

Export table using VBA

murugasubi — Fri, 27 May 2022 17:28:31 GMT

Hi Dear,

I want to export the AutoCAD table to excel, I tried the below code which returns the cell value along with some unwanted texts. I want only the cell value. I have attached the drawing and the output. Please help me.

Public Sub transferTableValues_Excel()

Dim tTable1 As AcadTable
Dim tPickedPnt As Variant
Dim tVal As Variant

'select tables
On Error Resume Next 'in case no object or wrong object type is selected
Call ThisDrawing.Utility.GetEntity(tTable1, tPickedPnt, "Select Source-Table: ")

Dim tRow As Long
Dim tCol As Long
For tRow = 0 To tTable1.Rows - 1
For tCol = 0 To tTable1.Columns - 1
tVal = tTable1.GetText(tRow, tCol)

'tVal = tTable1.GetValue(tRow, tCol, 0)
Debug.Print (tVal)
Next
Next

End Sub

The unwanted texts are adding like this: {\fArial|b0|i0|c162|p34; \fArial TUR|b0|i0|c162|p34;}

Thanks

Murugan

Re: Export table using VBA

Ed__Jobe — Fri, 27 May 2022 17:35:37 GMT

Those are mtext formatting codes. You need to use string manipulation functions like Mid() to strip out what you need.

Re: Export table using VBA

murugasubi — Fri, 27 May 2022 17:51:23 GMT

@Ed__Jobe , can you please help me with the VBA code?.. I tried but, I was unable to get it done correctly.

If possible, please help me to export it as CSV file. Thanks, in advance

Re: Export table using VBA

Ed__Jobe — Fri, 27 May 2022 18:54:12 GMT

it requires knowing your data, which I don't. But, example, you could use the MID function to return a part of the string that represents the cell contents. You just need to tell the function where to start and end the sequence. You could also, study the pattern of codes and search the whole string for those parts. You can then use the Left and Right functions to help you figure out what you need. Note that the mtext starts and ends with curyly braces and the codes are separated by vertical pipe characters.

Re: Export table using VBA

grobnik — Fri, 27 May 2022 19:25:57 GMT

@murugasubi Sorry to reply with silly answer but did you tried to export it with tableexport command manually or by a simple lsp. Result will be a csv file without formatting text code.

But if you have a lot of dwg may be complicate do it manually.

(defun c:tabx() (command "tableexport" "" "test.csv") (princ "\nTable Export Complete:") (princ) ) ;_end of defun

Re: Export table using VBA

Ed__Jobe — Fri, 27 May 2022 20:03:15 GMT

You might have a look at this thread. Using the regex in the last post, I came up with this sample below. However, the regex didn't work. You can play around with it to find the right expression.

'You must register Microsoft VBScript Regular Expression 5.5 Sub RegexTestMtext() Dim rg As New RegExp Dim str As String With rg .Global = True .MultiLine = True .IgnoreCase = False .Pattern = "\\[A-Za-z0-9.\/\#\^]+;" End With str = "{\fArial|b0|i0|c0|p34;weight}" If rg.test(str) Then Debug.Print rg.Replace(str, "$1") Else Debug.Print "No Match" End If End Sub

Re: Export table using VBA

Ed__Jobe — Fri, 27 May 2022 20:38:25 GMT

I also found this thread at the Swamp that has a lisp routine that uses regex to strip the format codes. Sorry, but I don't have any more time to test this out. Let us know if you solve your problem.