Extracting MTXT including fields to Excel

[Glen1980]

First off I've never tried to do anything like this before, I have tried using data extraction wizard and it didn't recognize any Mtext objects so maybe I'm peeing in the wind with this idea, but here goes.

 

For every Flat (apartment) I draw I label it with a simple MTxt box stating Plot Number and Flat Type. Now they want us to show floor areas on the drawing as well as my excel spreadsheet schedule. To show the areas I have used fields to take the area of my poly lines then convert into square metres & feet and inches.

 

As I have had to do this once I was wondering if I could 'steal' the mtext and drop it into excel just as values (no hyperlinking.) Individually I can go in and copy and paste and each line comes in nicely on seperate cell which is perfect, but I have to do 30 odd per floor which is quite time consuming. Does anyone have a LISP I could use or one they could point me to that would automatically take all text from a certain layer then put it into an excel sheet?

 

I'm not sure if this is possible or if I am expecting autoCAD magic again

[ReMark]

Maybe the TXTOUT.lsp routine posted by jaberwok in this link would work? Don't know for sure because I did not test it myself.

 

http://forums.augi.com/showthread.php?35671-Can-Mtext-or-Dtext-be-Exported-to-Excel-or-Word

[Glen1980]

Thanks ReMark that has worked!

 

It would have been perfect except for how the guy before me set this drawing up. Each block of MText come sout like this

 

\pxqc;{\fArial|b1|i0|c0|p34;Plots 107 & 112}\PTYPE C/X\P65.01m²\P700ft²

 

It will be quicker than cutting and pasting indivdually though. Is there a way to make the "\p" force text into a new cell?

[ReMark]

Not my bailiwick I'm afraid. I could have another look around to see if I can find something more suitable to your needs if you'd like.

 

You might try the VBA routine as posted by rkmcswain found in this link...http://forums.augi.com/archive/index.php/t-35780.html

[Glen1980]

I keep meaning to teach myself but the gap between the tutorials and what I need is rather large!

 

Thanks for your help ReMark, you have saved me hours:lol:. Pop over to England and I'll buy you a few beers!

[ReMark]

You're entirely welcomed.

 

I can be there by the time you get out of work. Where shall we meet?

[Glen1980]

Have to be central London, these days everywhere else is dead until 10pm!

[Glen1980]

Hah!

 

I went into wordpad ctrl+h removed the text style info and replaced \p with a comma then the csv file automatically split them when opening in excel!

[ReMark]

Excellent! Thanks for the update.

[Bhull1985]

Hey that txtout routine is really nice, works quite well. And it places the file with the name that you designate in the toplevel folder of the first item in the support file search path for the cad profile. Just in case anyone was not able to locate it. I used (findfile "myfile") and it pointed me there correctly.

 

One thing that prevents this from any serious use for our purposes here....is the fact that it does not get the attribute text from the blocks that are in the drawing, which is actually the only text that at least for me, i'd be interested in exporting in such a manner.

 

Would anyone possibly care to lend suggestions on how to modify this code so that it can get the attribute text for each block in the drawing?

Would that require a re-write or would it be possible to add additional conditions onto that code that will search for any blocks in the table, then extract their attribute values , possibly with the tag associated with them? I am willing to do all that I can, but would definitely need someone more experienced to point me in the correct direction to start.

 

This would be a great exercise, and have some uses. Just need the technical assistance to modify it.

Thanks!

[ReMark]

Option 1. Extract attributes...http://www.ellenfinkelstein.com/acadblog/autocad-tutorial-extract-attributes/

 

Option 2. Global attribute extractor by Lee Mac...http://lee-mac.com/macatt.html

[Tyke]

I've been on the road all day and just got back in and I know it's a bit late but here's a VBA solution to Glen's problem. You need to have your Excel workbook open to the worksheet and the cell selected where you want the first value to be entered. Successive entries will be entered in the same column on the next row. In AutoCAD run the following macro, you will prompted to select the MText items until you pick nothing, an item that isn't MText, press Esc, press the space bar or do a right click. It does not pick up the first part of the MText just the text that is displayed on the screen.

 

Public Sub ToExcel()

   Dim objMText      As AcadMText
   Dim varInsPt      As Variant
   Dim Row           As Integer
   Dim Col           As Integer
   Dim i             As Integer
   Dim strMText      As String
   Dim c             As Integer
   Dim ExcelApp      As Object
   Dim ExcelSheet    As Object
   Dim ExcelWorkbook As Object

   Set ExcelApp = GetObject(, "Excel.application")
   Set ExcelWorkbook = ExcelApp.ActiveWorkbook
   Set ExcelSheet = ExcelApp.activesheet

   Row = ExcelApp.ActiveCell.Cells.Row
   Col = ExcelApp.ActiveCell.Cells.Column
   
   i = 1
   Err.Clear
   On Error Resume Next
   
   ThisDrawing.Utility.GetEntity objMText, varInsPt, " Select MText : "
   Do While Err.Number = 0  
       strMText = CStr(objMText.TextString)
       ExcelSheet.Cells(Row, Col).Value = strMText
       Row = Row + 1
       i = i + 1
       ThisDrawing.Utility.GetEntity objMText, varInsPt, " Select MText : "
   Loop
End Sub

Edited September 19, 2013 by Tyke
Removed redundant code

[Bhull1985]

Okay great, hadn't used the first half of LM's program before now but I see that it does exactly that.

Excellent! and thanks.

[Tyke]

Okay great, hadn't used the first half of LM's program before now but I see that it does exactly that.

Excellent! and thanks.

 

You are more than welcome and I hope that it is of use.

[Glen1980]

I've been on the road all day and just got back in and I know it's a bit late but here's a VBA solution to Glen's problem.

 

Thanks Tyke, I'm going to get our IT dept. to install the VBA module when they decide to turn up this morning and I'll definitley try to run this VBA thingumy. Always happy to try something new!

[Tyke]

Thanks Tyke, I'm going to get our IT dept. to install the VBA module when they decide to turn up this morning and I'll definitley try to run this VBA thingumy. Always happy to try something new!

 

Glen, are you only interested in MText or would it be beneficial to be able to select MText or DText?

Do you need any help in creating a VBA macro or how to get the code to work?

[Glen1980]

MText only Tyke. I've never used VBA before, so I had to dig out an old post by Dave & Tiger to find out how to run it. Then AutoCAD tells me VBA isn't installed anymore so I have to DL the module. Unfortunately that means involving IT as I can't be trusted to have admin rights. I'm bad bad person and may thoughtlessly install something I shouldn't

 

As far as creating VBA, I would imagine I'd need lots of help. SO far I haven't been able to create any LISPs of my own other than following tutorials. Even then my typing can be sloppy so most of my time would be spend spell checking and bug finding! Are there any Afralisp type sites for VBA?

[Tyke]

As far as creating VBA, I would imagine I'd need lots of help. SO far I haven't been able to create any LISPs of my own other than following tutorials. Even then my typing can be sloppy so most of my time would be spend spell checking and bug finding! Are there any Afralisp type sites for VBA?

 

Yes, Afralisp (which is also cared for by our David Watson) has a VBA section and some beginneres turorials and sample code. A Google search always turns up something when you get stuck. Use a search string such as "AutoCAD VBA select MText" and will turn up almost 400,000 results, not all of which will be relevant but a start.

[Glen1980]

Yes, Afralisp (which is also cared for by our David Watson)

 

Really?! how does he find the time? I have something else to thank David for, at this rate between you, ReMark and David and everyone else who has helped me I'm going to have to organize quite a large London shin dig to say thank you to everyone!

[Tyke]

Really?! how does he find the time? I have something else to thank David for, at this rate between you, ReMark and David and everyone else who has helped me I'm going to have to organize quite a large London shin dig to say thank you to everyone!

 

Careful what you say, because I pass through London on my way home and you would not be the first forum member that I've shared a beer with in London