Author Topic: Extracting text to excel in the order it is selected in Autocad  (Read 8977 times)

0 Members and 1 Guest are viewing this topic.

ELOQUINTET

  • Guest
I have alot of shade sizes as text in my drawing that i would to not have to reproduce in excel so I'm wondering if there is a way to extract this and keep it in the order that i selected it? i would like my spreadsheet to have one column for the width and one for the height so if the numbers could be placed like the picture below that would be excellent:

width    height

96           200
45           75

Is this doable?

Guest

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #1 on: August 24, 2007, 02:42:13 PM »
Anything is doable.  I might have something like that kickin' around.  Let me check.

ronjonp

  • Needs a day job
  • Posts: 7526
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #2 on: August 24, 2007, 02:49:05 PM »
Dan,

This might help you.
Here is one that exports the text from top to bottom to a text file:

Code: [Select]
(defun c:textout (/ ss lst fn fno txt)
  (setq ss (mapcar 'vlax-ename->vla-object
   (vl-remove-if
     'listp
     (mapcar 'cadr
     (ssnamex
       (ssget '((0 . "TEXT")))
     )
     )
   )
   )
  )
  (if ss
    (progn
      (setq
lst (mapcar '(lambda (x)
       (cons (vlax-get x 'InsertionPoint)
     (vlax-get x 'TextString)
       )
     )
    ss
    )
lst (vl-sort lst
     (function
       (lambda (e1 e2)
(< (cadr (car e2)) (cadr (car e1)))
       )
     )
    )
fn  (strcat (getvar 'dwgprefix)
    (vl-filename-base (getvar 'dwgname))
    "-text.txt"
    )
fno (open fn "W")
      )
      (foreach txt lst
(write-line (cdr txt) fno)
      )
      (close fno)
      (princ fn)
      (princ)
    )
  )
)

Windows 11 x64 - AutoCAD /C3D 2023

Custom Build PC

ELOQUINTET

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #3 on: August 24, 2007, 02:50:35 PM »
i just started brainstorming ways we can better automate the creation of our shade schedules as they are getting huge. I would ultimately like to develop something more elaborate but this is what i desperately need right now. Our schedule consists of three primary items i need to get from the drawing, room name/number,shade size ,and maybe motor numbers. I tried using the standard data extraction tool in autocad and it extracts the data but it doesn't maintain any kind of order so is virtually useless. If i could select these items in a given order and have the chart automatically generated that would be spectacular. I was going to mention this in the other post but figured you'd look here anyway. thanks

ELOQUINTET

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #4 on: August 24, 2007, 04:50:25 PM »
ron that's interesting but i think i would have to extract the data together otherwise it is too confusing ecspecially for my current project but thanks anyway i'll put that in my toolbox.  ^-^

Fatty

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #5 on: August 24, 2007, 05:26:36 PM »
If your text is looks like table text
(not as Acad table) you can try this one
just select all text you need to be export
by window
Create an Excel file before
Change all to suit

~'J'~

ELOQUINTET

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #6 on: August 27, 2007, 11:01:46 AM »
fatty i'm trying this out but think i'm missing something. i select my text by window and it opens excel but no file i create a new file then tried pasting but get nothing. how does this work?

Fatty

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #7 on: August 27, 2007, 12:08:13 PM »
fatty i'm trying this out but think i'm missing something. i select my text by window and it opens excel but no file i create a new file then tried pasting but get nothing. how does this work?
Ah, sorry about
Forgot to say, create Excel file before :oops:

~'J'~

ELOQUINTET

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #8 on: August 27, 2007, 12:51:23 PM »
wow that is pretty sick fatty you forgot to mention that my file has to be called dat.xls for it to work but i figured it out from the error message but that's no big deal. i see it extracts everything but it's not exactly in order. i think the only way i can get the widths and heights to match is to select width height width height for each shade which i'd rather do than sorting them after the fact. how hard would that be to tweak it so it would work that way rather than a single window? i like the idea though thanks

Fatty

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #9 on: August 27, 2007, 03:20:33 PM »
Sorry, there is not clearly enough for me
Can you upload some pic here to see
what are you talking about? :)

~'J'~

ELOQUINTET

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #10 on: August 27, 2007, 04:09:53 PM »
Fatty,

See if this helps. The first is my location plan and the white text is the width and height of our shades. The second image shows how i'm plugging these numbers into a schedule. Is it becoming clearer now. I am actually almost done manually plugging in these numbers but in the future i would like to automate it. Thanks


Example

Schedule Example

Fatty

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #11 on: August 27, 2007, 04:56:05 PM »
Wow,
Here it is need to chew long!
I'll try

~'J'~

ELOQUINTET

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #12 on: August 27, 2007, 05:07:01 PM »
yeah this job is somewhat custom in that both shades do not go to the floor so if you look at my schedule the sond shade is 18 inches shorter the the one above. i'm making the modifications after i get the numbers in the schedule. this is just a small part of the job, the whole thing is 15 floors so you can imagine how tedious it is. anyway give it a shot and let me know if you need any input. Thank you very much for your efforts i'm excited to see what you come up with.

Swift

  • Swamp Rat
  • Posts: 596
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #13 on: August 27, 2007, 07:33:36 PM »
I have something very similar to what you need, it is in Excel VBA that connects to Autocad and extracts the sums of closed polyline areas and inserts them into the selected Excel cell. I've not used it since Autocad 2005 and Excel 2000 but it might be a good place to start. I'll see if I can dig it up.

Fatty

  • Guest
Re: Extracting text to excel in the order it is selected in Autocad
« Reply #14 on: August 28, 2007, 05:20:15 AM »
yeah this job is somewhat custom in that both shades do not go to the floor so if you look at my schedule the sond shade is 18 inches shorter the the one above. i'm making the modifications after i get the numbers in the schedule. this is just a small part of the job, the whole thing is 15 floors so you can imagine how tedious it is. anyway give it a shot and let me know if you need any input. Thank you very much for your efforts i'm excited to see what you come up with.
What I have so far
How it works
Create copy of your working Excel file
Change in VBA code the name of this file
Change reference to MS Excel in VBAIDE to
your current version
After you'll run sub, first of all select room label
(I don't know what the kind of sort the object it is,
I added 'If' statement for both text and attribute)
second, select one by another the texts in
order left-right, left-right etc, then after last selected
text hit Enter
Then make the same manipulations with other
rooms
At the end push Enter twice
Sorry for the bad explanations, also, translate all
promts

Duh, what the heck is "sond"?

Code: [Select]
Option Explicit
Public xlApp As Excel.Application
Public xlBook As Workbook
Public xlSheet As worksheet
Public strFilePath As String
Dim rowNum As Long
Public iRow As Long

Sub getShadows()
 Dim oSset As AcadSelectionSet
     Dim oEnt As AcadEntity
     Dim varPt As Variant
     Dim oText As AcadText
     Dim oAtt As AcadAttributeReference
     Dim iCnt As Integer
     Dim tmx, ctx
     Dim fcode(0) As Integer
     Dim fData(0) As Variant
     Dim dxfcode, dxfdata
     Dim setName As String
     Dim i As Long, icol As Long
     Dim sond As Double
     
     sond = CDbl(InputBox(vbCr & vbCr & "Enter shadow sond increment:", _
     "Shadow Sizes", "18"))

     fcode(0) = 0
     fData(0) = "TEXT"

     dxfcode = fcode
     dxfdata = fData
     setName = "$TEXT$"
     
     strFilePath = ThisDrawing.Path & "\Dat.xls"     '// <-- change to your suit

     Set xlApp = CreateObject("Excel.Application.11")
     xlApp.Visible = True
     Set xlBook = xlApp.Workbooks.Open(strFilePath, True, False)

     Set xlSheet = xlBook.Worksheets(1)

     On Error GoTo Exit_Sub
     xlSheet.Activate
     xlApp.WindowState = xlMinimized
     
     ' get last cell row in the column "C"
     iRow = xlSheet.Range("C:C").Cells.SpecialCells(xlCellTypeLastCell).Row
     ' start from next row
     iRow = iRow + 1

     Do While True
     For i = 0 To ThisDrawing.SelectionSets.Count - 1
          If ThisDrawing.SelectionSets.Item(i).Name = setName Then
               ThisDrawing.SelectionSets.Item(i).Delete
               Exit For
          End If
     Next i
     
     Dim room As String
     ThisDrawing.Utility.GetSubEntity oEnt, varPt, tmx, ctx, vbCr & "Select room label text" & vbCr & _
     "(Press Enter to Exit loop) :"
     If TypeOf oEnt Is AcadText Then
     Set oText = oEnt
     room = oText.TextString
     ElseIf TypeOf oEnt Is AcadAttributeReference Then
     Set oAtt = oEnt
     room = oAtt.TextString
     End If
   
     Set oSset = ThisDrawing.SelectionSets.Add(setName)

     oSset.SelectOnScreen dxfcode, dxfdata
     '//MsgBox oSset.Count <-- debug only
     Dim coll As Collection
     Set coll = New Collection
     Dim itm(1) As String
     coll.Add room
     Erase itm '<--optional
     
     For i = 0 To oSset.Count - 1 Step 2
     Set oEnt = oSset.Item(i)
     Set oText = oEnt
     itm(0) = oText.TextString
     Set oEnt = oSset.Item(i + 1)
     Set oText = oEnt
     itm(1) = oText.TextString
     coll.Add itm
     itm(0) = itm(0)
     itm(1) = itm(1) - sond
     coll.Add itm
     Next
     oSset.Delete
     Set oSset = Nothing
     
     rowNum = iRow
     
     xlSheet.Cells(iRow, 3) = coll.Item(1)

          With xlSheet
          For i = 2 To coll.Count

               .Cells(iRow, 7) = coll.Item(i)(0)
               .Cells(iRow, 8) = coll.Item(i)(1)

               iRow = iRow + 1
               Next i
               
          .Columns(1).HorizontalAlignment = xlCenter
          .Range(Cells(rowNum, 3), Cells(iRow - 1, 3)).VerticalAlignment = xlCenter
          .Range(Cells(rowNum, 3), Cells(iRow - 1, 3)).Merge
     
          End With
         
     For i = coll.Count To 1 Step -1
     coll.Remove 1
     Next
     Loop

Exit_Sub:
     
     Set xlSheet = Nothing
     Set xlBook = Nothing
     Set xlApp = Nothing

Err_control:

     If Err.Number <> 0 Then
     If Err.Description Like "*failed" Then
     MsgBox "Interrupted by user"
     Else
     MsgBox Err.Description
     End If
     Resume Exit_Sub
     End If
     
End Sub

~'J'~