VBA
Discuss AutoCAD ActiveX and VBA (Visual Basic for Applications) questions here.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Export AutoCAD table to Excel, while ignoring text format code

8 REPLIES 8
SOLVED
Reply
Message 1 of 9
cwilliamsMUJRB
235 Views, 8 Replies

Export AutoCAD table to Excel, while ignoring text format code

I'm trying to export the text from some tables in AutoCAD to an Excel spreadsheet. The problem I'm having is that when I use the tableObj.GetText(row, col) method will print the Mtext Format code in the Excel cell. e.g.

 

\A1; {\C256;Rivet}

 

 

Perusing the forums offered no help, so I went to the online help and found the tableObj.FormatValue(row, col, acIgnoreMtextFormat, pVal) method. This should theoretically return the text in the cell without any format. However, I can't figure out what the pVal should be. There is no documentation in the help entry and I can't find anything about it online.

 

Is there a way to modify 'GetText' to remove formatting, or is there a common pVal that I should use, or should I be trying something else entirely?

 

Thanks in advance!

Tags (2)
8 REPLIES 8
Message 2 of 9

In Python, it works with a null, example

 

 

def PyRxCmd_xdoit():
    try:
        axApp = Ax.getApp()
        axDoc = axApp.ActiveDocument
        ent, pnt = axDoc.Utility.GetEntity("\nPick an ent: ")
        if ent.EntityName == "AcDbTable":
            axTable = Ax.IAcadTable(ent)
            bhit, row, col = axTable.HitTest(pnt, (0, 0, 1))
            print(bhit, axTable.FormatValue(row,col, Ax.constants.acIgnoreMtextFormat))
    except Exception as err:
        traceback.print_exception(err)

 

 

 

 

Pick an ent:
Command: True Rm 2124

Python for AutoCAD, Python wrappers for ARX https://github.com/CEXT-Dan/PyRx
Message 3 of 9

Hi @daniel_cadext

 

Thank you for the reply. I'm using VBA (I don't think I mentioned that). I've tried Null, None, Empty, etc., and none of them are working. 

 

If I have 'None' in the pVal field, it gives the error "Compile error: ByRef argument type mismatch" (.FormatValue(row, col, acIgnoreMtextFormat, None)

 

If I have 'Null' in the pVal field, it gives the error "Compile error: Expected Function or variable" (.FormatValue(row, col, acIgnoreMtextFormat, Null)

 

Do you have any suggestions as to what could be causing that error?

 

Thanks again! 

Message 4 of 9

I think it's looking for a string, maybe an empty string or a variant

Python for AutoCAD, Python wrappers for ARX https://github.com/CEXT-Dan/PyRx
Message 5 of 9

Hi @daniel_cadext,

 

An empty string does not work ("") it gives the "Expected Function or Variable" error. I'm now curious if something else is wrong with the code, but it works just fine with .GetText(row, col). 

 

Would love to hear if you have any more ideas!

Message 6 of 9
ed57gmc
in reply to: cwilliamsMUJRB

This is what the help says about the function. "Gets the formatted text string for the specified row and column." It does not return the unformatted text, but the text with the control codes. in C++, a variable name with "p" at the beginning, such as "pVal", indicates a pointer to a variable. You need to declare a variable to hold the returned string. All that said, it's not going to do what you want. Some time ago, I wrote a function using REGEX, to test mtext format codes. You could define your own regex expression to remove all mtext format.

Ed


Did you find this post helpful? Feel free to Like this post.
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
How to post your code.

EESignature

Message 7 of 9
daniel_cadext
in reply to: ed57gmc

"All that said, it's not going to do what you want"

 

it should, I use it in C++ and Python all the time, here's the difference.

 

 

print(axTable.FormatValue(row,col, Ax.constants.acIgnoreMtextFormat))
print(axTable.GetTextString(row,col,0))

 

returns

 

Xerox 1980
{\fMS Sans Serif|b0|i0|c0;Xerox 1980

 

 

Python for AutoCAD, Python wrappers for ARX https://github.com/CEXT-Dan/PyRx
Message 8 of 9
ed57gmc
in reply to: daniel_cadext

Ah, my bad. I was going off of the sentence I quoted. I forgot about the Ingnore argument that strips the output. Nevertheless, regex would work too if he had a need to filter a certain value or phrase. 

The thing that was tripping him up is the pVal argument. You used None but he could supply a variable that will be used to pass to xl. 

Ed


Did you find this post helpful? Feel free to Like this post.
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
How to post your code.

EESignature

Message 9 of 9

Thank you both for the assistance! I finally figured it out after tearing my hair out for a few hours. It appears that the VBA version of .FormatValue does not work as intended (or documented). It is worded as follows: 

RetVal = object.FormatValue(row, col, nOption, pVal)

The method should return a string (according to the documentation) based on the row, column, and nOption. However, as @ed57gmc pointed out, the pVal should be a pointer variable that returns the formatted value of the cell. This means that, according to the documentation, you are getting the formatted value twice (once in the RetVal where you set up RetVal = "...", and once in the pVal). 

 

According to @daniel_cadext the method works in Python and C++ without the pVal. It just simply returns the formatted value.

 

print(axTable.FormatValue(row,col, Ax.constants.acIgnoreMtextFormat))

 

 

However, in VBA the pVal is not optional. It simply won't compile the code without it. And, when you put a variable in the pVal it gives the "Expected Function or Variable" error. After some testing, I realized that the method does not return a value like it's documented, but instead, it returns the formatted value via pVal. 

 

Call tableObj.FormatValue(row, col, acIgnoreMtextFormat, CellVal)

 

My guess is that it was coded differently for VBA and Python and the help documentation has never been updated. 

 

Tldr;

Documentation is vague at best and works differently for VBA and Python/C++.

VBA: 

 

Call object.FormatValue(row, col, nOption, pVal)

 

 

Python/C++:

 

RetVal = object.FormatValue(row, col, nOption)

 

 

Thank you both again!

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

AutoCAD Inside the Factory


Autodesk Design & Make Report