automating autocad to excel?

[msstrang]

first off, let me just say i love this forum.
i can usually figure out about 95% of what i need to do, and everyone here is always ready to help me with the other 5%, which is great!

i'm running autocad 2002, and need only a little help.
i've created a VBA that takes data from a sheet in a workbook in Excel and places that data into a test report at the corresponding locations.

and from this forum i found a lisp code that sends all text from an autocad drawing to a txt file that i then open in excel. this is the sheet from which i get my data in the above paragraph.

is there any way to skip the middle step of me opening the txt file in excel, and then selecting it to be delimited by "spaces" and the "%". it would be great if there was either a script file i could run or a lisp that would save this data from autocad to excel. anybody got anything?

[Murph]

Do you want LISP or VBA?

[msstrang]

hmmm....
i don't really have a preferance.
it would be nice if it were in VBA because i feel like it might be easier to have my page formatted in the manner i stated in my original post.

however autolisp would also be nice if i could have the data formatted a particular way, because it would be sent to a specific file and hopefully open it. i don't know. i'm a novice when it comes to communicating between autocad and excel, really.

[Murph]

Second question:

Are these "schedules" in CAD that you want reproduced in Excel?
For example, is the text arranged in columns and rows in CAD, OR
do you just want to populate a spreadsheet with all the text in a drawing, one piece of text per row in column A.

The reason I ask is, I have many routines for getting text out of CAD and into Excel. One asks you for two points to do a crossing window then a letter for the column to insert it into. Then they are sorted from highest Y value to lowest. Makes it very easy to reproduce CAD only schedules in Excel.

If you could, please give me an example of what you are trying to do and I can get you code to modify to achieve your goals.

[msstrang]

If there is a way you can suggest to shorten this process by using more VBA, LISP, or Script files that would be greatly appreciated.

currently my process consists of the following steps:

1. I open an autocad specification that I want to create a quality control test document for. I use the following .lsp code by typing TXTEX in the command prompt for autocad and it selects all the text in the drawing and saves it to a .txt file called dtext.txt

(defun c:txtex (/ et)
(setq fl (open "dtext.txt" "w")
et (entnext)
)
(while et
(setq el (entget et)
tp (cdr (assoc 0 el))
)
(if (or (= tp "TEXT") (= tp "MTEXT"))
(write-line (cdr (assoc 1 el)) fl)
)
(setq et (entnext et))
)
(close fl)
)

2. Next, I open a new session of EXCEL. Then I go to FILE-OPEN-dtext.txt

3. Then Excel Opens the Text import wizard.

4. I select that the text needs to be Delimited and select NEXT.

5. I select the delimiters to be the SPACE and OTHER and in the input box for other I enter "%". I leave the box checked that states "Treat consectutive delimiters as one". I then select NEXT.

6. Then I have to select all of the columns individiual one by one and change the "Column Data Format" to "Text". I then select OK.

7. I then copy and past this into a page called "dtext" into a Workbook in Excel that contains a Macro that searches for text in the "dtext" excel page and places this found data in specific cells on a second page to create a Test report.

Wow, that's a bunch of stuff. I've done it so much, it doesn't seem like that much. What I think is possible to do is to automate steps 2,3,4,5,6 and 7 somehow, but I can't figure out how. I don't even think it would be that hard, but i've hit a brick wall here.

any help would be greatly appreciated.

thanx.
marcus

[Murph]

This will place all text in it's own row in column A. You will have to tinker with it to get it into the correct locations to run your other macro.
All of this code goes into a Module in Excel (to get to the VBA editor press Alt+F11). You will have to go to Tools, References and add a reference to your version of AutoCAD.

See what you can do with this and ask any questions you may have.



Public Sub GetText()
Dim objSelSet As AcadSelectionSet
Dim objEnt As AcadText
Dim intType(1) As Integer
Dim varData(1) As Variant
Dim intRow As Integer
Dim varAtts As Variant
Dim MyVar As Range
Dim DB As Workbook
Dim CP As Worksheet
Set DB = Application.ActiveWorkbook
Set CP = DB.ActiveSheet
On Error GoTo Err_Handler
Set objAcad = Nothing
Set objSelSet = vbdPowerSet("gettext")
intType(0) = 0
varData(0) = "TEXT,MTEXT"
objSelSet.Select acSelectionSetAll, _
filtertype:=intType, filterdata:=varData
intRow = 1
For Each objEnt In objSelSet
With objEnt
CP.cells(intRow,1).Value = objEnt.TextString
End With
intRow = intRow + 1
Next objEnt
ThisDrawing1.ActiveSpace = acPaperSpace
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Number & " " & Err.Description
End Sub

[msstrang]

thanks, but that's not quite what i was looking for.
i did, however, figure it out using VBA.

again, i appreciate your effort. now my issue is exploding dimensions using a lisp, because every lisp i write to explode dimensions does not work, which is funny because it seems like a very easy lisp to write.

any suggestions?

[hendie]

see my answer in your other post