Extract Excel to AutoCAD Text - Optional Methods.

[RockyBrown4134]

I was asked to find out if there was a way to extract text from and Excel spreadsheet and put it into a MTEXT inside the drawing.

I can do it with a table and Data Link. But is that the easiest way? Any other Thoughts?

Parameters that are kept in the Excel File:

1) File will be called drawing name + notes.xls

2) It will be located in the in the support path directory. It can be moved to the same directory as the drawing if needed.

3) Text needed to be extracted will always be in one column, starting with Cell "A5". (First four rows are header information rows not used by AutoCAD at this time)

4)There is no row limit. For now 1-99 would be sufficient.

Any and all suggestions are welcome.

Moderator: Please feel free to move this post to another group if needed.

Thanks.

[hmsilva · Accepted solution]

I don't deal with Excel for some time, but as a starting point...

Change the " + notes.xls" to the correct name.

(vl-load-com)
(defun c:demo (/ Activesheet Cells Column ExcelApp FilePath N_Rows Pt Range Row V Str Val Workbook
               Workbooks)
  (if (and (setq FilePath (findfile (strcat (vl-filename-base (getvar 'DWGNAME)) " + notes.xls")))
           (setq Pt (getpoint "\nEnter Mtext insertion point: "))
      )
    (progn
      (setq ExcelApp    (vlax-get-or-create-object "Excel.Application")
            Workbooks   (vlax-get-property ExcelApp 'Workbooks)
            Workbook    (vlax-invoke-method Workbooks 'Open FilePath)
            Activesheet (vlax-get-property ExcelApp 'ActiveSheet)
            Cells       (vlax-get-property ActiveSheet 'Cells)
            Range       (vlax-get-property Activesheet 'UsedRange)
            Rows_R      (vlax-get-property range 'rows)
            N_Rows      (vlax-get-property Rows_R 'count)
            Str         ""
            Column      1
            Row         5
      )
      (while (<= Row N_Rows)
        (setq Val (vlax-variant-value
                    (vlax-get-property
                      (vlax-variant-value (vlax-get-property cells 'Item Row Column))
                      'Value
                    )
                  )
        )
        (if Val
          (progn
            (cond ((= (type Val) 'REAL)
                   (setq val (rtos val 2 2))
                  )
                  ((= (type Val) 'INT)
                   (setq val (itoa val))
                  )
            )
            (if (and (= (type Val) 'STR)
                     (/= Val "")
                )
              (if (< Row N_Rows)
                (setq Str (strcat Str Val "\\P"))
                (setq Str (strcat Str Val))
              )
            )
          )
        )
        (setq Row (1+ Row))
      )
      (vlax-invoke-method Workbooks 'Close)
      (vlax-invoke-method ExcelApp 'Quit)
      (mapcar 'vlax-release-object
              (list Range Rows_R cells activesheet workbook workbooks ExcelApp)
      )
      (repeat 3 (gc))
      (if (/= Str "")
        (entmake (list
                   (cons 0 "MTEXT")
                   (cons 100 "AcDbEntity")
                   (cons 100 "AcDbMText")
                   (cons 10 Pt)
                   (cons 71 1)
                   (cons 1 Str)
                 )
        )
        (prompt "\nNo data found at column 1...")
      )
    )
    (cond (FilePath
           (prompt "\nCanceling...")
          )
          (T
           (prompt "\nXLS file not found...")
          )
    )
  )
  (princ)
)

Hope that helps

Henrique

[RockyBrown4134]

Henrique;
I'm out of the office. I'll take a better look at it when I get back in.
Thanks.

[RockyBrown4134]

Thanks for the help. I copied the lisp to a file and saved it in the search path.

Next;

1) Created an Excel file called testnotes.xls.

2) Created a drawing called test.dwg.

3) Saved both in the same directory.

When I load the lisp and run it, I get "XLS file not found..."

Any Suggestions?

[hmsilva]

Change

(setq FilePath (findfile (strcat (vl-filename-base (getvar 'DWGNAME)) " + notes.xls")))

to

(setq FilePath (findfile (strcat (vl-filename-base (getvar 'DWGNAME)) "notes.xls")))

Henrique

[RockyBrown4134]

Oh do I feel embarassed!

The simple things we overlook.

Ran the File after I renamed the .xls correctly, works great.

Thank you for the help.

[hmsilva]

You're welcome, Rocky Brown
Glad I could help

Henrique