Exporting Mtext from Autocad to Excel 1

[Jetwax]

My boss has tasked me with entering a bunch of text data (tag names, descriptions and addresses) from an autocad drawing he produces (electrical schematic) into MS Excel so that it can be then imported into Control Logix and form the tag database.

The electrical schematic contains a depiction of an IO card with an assortment of tag names and circuitry connecting them with their respective pins.

I am looking for help/suggestions in getting the different Mtext text out of autocad and into a csv file.

The idea solution (as far as I can see) would be to select the text and hit ctrl + (some key) and then the macro would copy the text and paste it into the next row in excel. I can write code to paste into the next row in excel. My problem is that I cant get a macro to run with a ctrl + (some key) and I am unable to write the code to copy the text.

Any help/suggestions/references that you could point me too would be greatly appreciated.

I know there has to be a faster, more accurate of getting these approx 6000 tags out of autocad and into a .csv file.


Thank you very much.

Erica Ashworth

 

[cadcamguy]

Erica,

You will probably have code in hand by this afternoon if you go to the newsgroup

discussion.autodesk.com
then forum autodesk.autocad.customization.vba

or

msnews.microsoft.com
then forum microsoft.public.excel.programming

you don't want to select the text on the screen , you want to loop thru the thisdrawing objects find it all with code and output right to excel...with the push of one button
my vba in acad is a bit rusty..using ProE these days...but you'll get answers on those 2 newsgroupd real fast

 

[Jetwax]

Thank you so much. Got the answer.

Here is a link to an LISP routine that does it

http://new.cadalyst.com/code/tips/CodeSearch_Detail.cfm?ID=1730

(This Site Rocks!)

 

[cadcamguy]

While there is nothing wrong with Lisp, my feeling is in the long run you will have more flexibility with VBA.

I realize there is the "Get the job done" factor. But if there is a need later to port other info to Excel with VBA you could add other routines based on Geometry type.

With VBA you will loop thru ThisDrawing.ModelSpace and do something based on what it finds..in your case text and or mtext..because I believe they are 2 different items in the drawing database

So if you set up the loop thru drawing as a selectcase function then it goes to a subroutine based on item type ; text,mtext later on if you have the need to extract info from a different item; you could build on your existing routine by just adding to the selectcase and another sub.

example...looping thru data base

For Each obj In ThisDrawing.ModelSpace
'Debug.Print q, obj.ObjectName
Select Case obj.ObjectName

Case "AcDbLine"
Set ln = ThisDrawing.HandleToObject(obj.Handle)
aline

Case "AcDb3dPolyline"
Set poly = ThisDrawing.HandleToObject(obj.Handle)
apoly

Case "AcDbArc"
Set ar = ThisDrawing.HandleToObject(obj.Handle)
arc

End Select

and another example opening excel with acad vba code to get text insertion points

Sub test()
Dim text As AcadText
Dim testpoint(1) As Double
Dim wkBook As Workbook
Dim wkSheet As Worksheet

On Error GoTo test_Error

testpoint(0) = 10
testpoint(1) = 10

Set wkBook = Workbooks.Open("C:\urXLFile.xls", True, False)
Set wkSheet = wkBook.Sheets("sheet1")

For Each text In ThisDrawing.ModelSpace
If text.InsertionPoint(0) = testpoint(0) And _
text.InsertionPoint(1) = testpoint(1) Then
wkSheet.Cells(1, 1) = text.TextString
wkSheet.Cells(1, 2) = "X: " & text.InsertionPoint(0)
wkSheet.Cells(1, 3) = "Y: " & text.InsertionPoint(1)
End If
Next text

wkBook.Close True
Set wkBook = Nothing
Set wkSheet = Nothing

test_Exit:

On Error GoTo 0
Exit Sub

test_Error:

Select Case Err.Number

Case 1004

Call MsgBox("XL file not found", vbExclamation,
Application.Name)

Resume test_Exit

Case Else: Resume test_Exit

End Select

End Sub

these are just some code snigglets... one would be able to set this up better to port to Excel..open the sheet in a sub wouldn't be the best of ways as it it open and close
but I think it gets the general idea across that VBA has it advantages over Lisp...the acad and excel forums from above can help in tweaking it in. There is also a VBA forum here
But the other 2 move at a bit faster pace. If you use Outlook config them as newsgroups

Hope this doesn't cause more confusion..since my Acad VBA is a little rusty.. the examples go back a ways

HTH....Bill