Jump to content

make table from "opened" excel workbook to autocad drawing


handasa

Recommended Posts

Greetings everyone ..

 

i found a code written by Juan Villarreal

which ask the user to browse the target excel and then open it and copy its used range as a table to the autocad ...

 

now i have my excel already opened ... can some one please what part of this code to modify to read the data from this open Excel workbook instead of opening a new one ...

 

thanks for reading and your time :)

 

;; © Juan Villarreal 12.06.2011

(defun ExcelData ( /  row col lst vertxldata CellProps CellItem)

(or
(and
  FilePath
  (= (type FilePath) 'STR)
  (findfile FilePath)
 )
(setq FilePath (getfiled "Select File:" (getvar 'dwgprefix) "xls;csv" 2))
)

(setq excel-app (vlax-get-or-create-object "excel.application")
     wb-collection (vlax-get excel-app "workbooks")
     arq    (vlax-invoke-method wb-collection  "Open" FilePath)
     sheets (vlax-get  excel-app "sheets")
     sheet1 (vlax-get-property sheets "item" 1)
)

(setq sheetname (vlax-get-property sheet1 "Name"))
(vlax-invoke-method wb-collection 'Close)
(vl-catch-all-apply 'vlax-invoke-method (list excel-app 'Quit))
(mapcar '(lambda (x)(vlax-release-object x))
(list sheet1 sheets arq wb-collection excel-app))
(mapcar '(lambda (x)(setq x nil)(gc))
(list sheet1 sheets arq wb-collection excel-app))
(gc)(gc)
)


(defun dc (str len /)
(atoi (substr (rtos DlDate 2 20) str len))
)


(Defun AddDataLink ( DataLinkName FilePath / ActDoc DatDict DlDate DatDictEname DataLinkList DLEM
                    TempTC SheetName TableContent)
(excelData)
(setq ActDoc (vla-get-activedocument (vlax-get-acad-object)))
(setq DatDict
(vla-add
 (vla-get-dictionaries
  (vla-get-database ActDoc)
 )
 "ACAD_DATALINK"
)
)
(setq datalink (list '(0 . "DATALINK") '(100 . "AcDbDataLink")))
(setq DLEM (entmakex datalink))
(setq EDL (entget DLEM))
(setq TempTC
(entmakex
 (list 
  (cons 0 "TABLECONTENT")
  (cons 100 "AcDbLinkedData")
  (cons 100 "AcDbLinkedTableData")
  (cons 92 0)
  (cons 100 "AcDbFormattedTableData")
  (cons 300 "TABLEFORMAT")
  (CONS 1 "TABLEFORMAT_BEGIN")
  (CONS 90 4)
  (CONS 170 0)
  (CONS 309 "TABLEFORMAT_END")
  (CONS 90 0)
  (CONS 100 "AcDbTableContent")
 )))
(setq DlDate (getvar 'cdate))
(setq DatDictEname (vlax-vla-object->ename DatDict))
(entmod (subst (cons 330 DatDictEname)(assoc 330 (entget temptc)) (entget temptc)))

(setq DataLinkList
(list
 (assoc -1 edl)
 (cons 0 "DATALINK")
 (cons 102 "{ACAD_REACTORS")
 (cons 330 DatDictEname)
 (cons 102 "}")
 (cons 330 DatDictEname)
 (cons 100 "AcDbDataLink")
 (cons 1 "AcExcel")
 (cons 300 "")
 (cons 301 (strcat "Data Link\n" DataLinkName "\n" FilePath "\nLink details: Entire sheet: " SheetName))
 (cons 302 (strcat FilePath "!" SheetName))
 (cons 90 2)
 (cons 91 1179649)
 (cons 92 1)
 (cons 170 (dc 1 4));Year
 (cons 171 (dc 5 2));Month
 (cons 172 (dc 7 2));Day
 (cons 173 (+ (dc 10 2)6));Hour+6
 (cons 174 (dc 12 2));Minutes
 (cons 175 (dc 14 2));Seconds
 (cons 176 (dc 16 2));Milliseconds
 (cons 177 3)
 (cons 93 0)
 (cons 304 "")
 (cons 94 0)
 (cons 360 TempTC)
 (cons 305 "CUSTOMDATA")
 (cons 1 "DATAMAP_BEGIN")
 (cons 90 3)
 (cons 300 "ACEXCEL_UPDATEOPTIONS")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 1)
 (cons 91 1179649)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_CONNECTION_STRING")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 4)
 (cons 1 (strcat FilePath "!" SheetName))
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_SOURCEDATE")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 
 (cons 92 16)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 309 "DATAMAP_END")
)
)
(entmod DataLinkList)
(entmod (append (entget datdictename)(list (cons 3 DataLinkName) (cons 360 DLEM))))
(princ)
)

(defun c:Tbl (/ DLName)
(setq DLName (getstring T "Data Link Name: "))
(AddDataLink DLName nil);or use filename in place of nil
(vl-cmdf "-table" "L" DLName);IF YOU DIDN'T ADD IT TO THE FUNCTION
)

Link to comment
Share on other sites

Why aren't you using the data link feature that already exists in AutoCAD?

 

because getting the table from excel is step of a large lisp code ...which have steps before and steps after

Link to comment
Share on other sites

something like this? :

 

 


(defun ExcelData (/ row col lst vertxldata CellProps CellItem activesheet activeworkbook)
 (setq excel-app   (vlax-get-or-create-object "excel.application")
activesheet (vlax-get-property excel-app "activesheet")
activeworkbook (vlax-get-property excel-app "activeworkbook")
sheetname   (vlax-get-property activesheet "Name")
FilePath    (vlax-get-property activeworkbook "FullName"))
)

gr.Rlx

Link to comment
Share on other sites

something like this? :

 

 


(defun ExcelData (/ row col lst vertxldata CellProps CellItem activesheet activeworkbook)
 (setq excel-app   (vlax-get-or-create-object "excel.application")
activesheet (vlax-get-property excel-app "activesheet")
activeworkbook (vlax-get-property excel-app "activeworkbook")
sheetname   (vlax-get-property activesheet "Name")
FilePath    (vlax-get-property activeworkbook "FullName"))
)

gr.Rlx

 

thanks ,Rlx

 

what if the opened sheet isn't already saved ... does the "FilePath" variable hold ?

Link to comment
Share on other sites

thanks ,Rlx

 

what if the opened sheet isn't already saved ... does the "FilePath" variable hold ?

 

 

You're welcome...

 

 

Nope , file has to be saved first... at least , I tried it and it failed. Program still sees 'book1' as name but it cannot get the data it seems at first glance.

 

 

Gr.Rlx

Link to comment
Share on other sites

  • 6 months later...

Dear

 

We wont to use Excel Formatting so please Help !!! :(

 

;; © Juan Villarreal 12.06.2011

(defun ExcelData ( /  row col lst vertxldata CellProps CellItem)

(or
(and
  FilePath
  (= (type FilePath) 'STR)
  (findfile FilePath)
 )
(setq FilePath (getfiled "Select File:" (getvar 'dwgprefix) "xls;csv" 2))
)

(setq excel-app (vlax-get-or-create-object "excel.application")
     wb-collection (vlax-get excel-app "workbooks")
     arq    (vlax-invoke-method wb-collection  "Open" FilePath)
     sheets (vlax-get  excel-app "sheets")
     sheet1 (vlax-get-property sheets "item" 1)
)

(setq sheetname (vlax-get-property sheet1 "Name"))
(vlax-invoke-method wb-collection 'Close)
(vl-catch-all-apply 'vlax-invoke-method (list excel-app 'Quit))
(mapcar '(lambda (x)(vlax-release-object x))
(list sheet1 sheets arq wb-collection excel-app))
(mapcar '(lambda (x)(setq x nil)(gc))
(list sheet1 sheets arq wb-collection excel-app))
(gc)(gc)
)


(defun dc (str len /)
(atoi (substr (rtos DlDate 2 20) str len))
)


(Defun AddDataLink ( DataLinkName FilePath / ActDoc DatDict DlDate DatDictEname DataLinkList DLEM
                    TempTC SheetName TableContent)
(excelData)
(setq ActDoc (vla-get-activedocument (vlax-get-acad-object)))
(setq DatDict
(vla-add
 (vla-get-dictionaries
  (vla-get-database ActDoc)
 )
 "ACAD_DATALINK"
)
)
(setq datalink (list '(0 . "DATALINK") '(100 . "AcDbDataLink")))
(setq DLEM (entmakex datalink))
(setq EDL (entget DLEM))
(setq TempTC
(entmakex
 (list 
  (cons 0 "TABLECONTENT")
  (cons 100 "AcDbLinkedData")
  (cons 100 "AcDbLinkedTableData")
  (cons 92 0)
  (cons 100 "AcDbFormattedTableData")
  (cons 300 "TABLEFORMAT")
  (CONS 1 "TABLEFORMAT_BEGIN")
  (CONS 90 4)
  (CONS 170 0)
  (CONS 309 "TABLEFORMAT_END")
  (CONS 90 0)
  (CONS 100 "AcDbTableContent")
 )))
(setq DlDate (getvar 'cdate))
(setq DatDictEname (vlax-vla-object->ename DatDict))
(entmod (subst (cons 330 DatDictEname)(assoc 330 (entget temptc)) (entget temptc)))

(setq DataLinkList
(list
 (assoc -1 edl)
 (cons 0 "DATALINK")
 (cons 102 "{ACAD_REACTORS")
 (cons 330 DatDictEname)
 (cons 102 "}")
 (cons 330 DatDictEname)
 (cons 100 "AcDbDataLink")
 (cons 1 "AcExcel")
 (cons 300 "")
 (cons 301 (strcat "Data Link\n" DataLinkName "\n" FilePath "\nLink details: Entire sheet: " SheetName))
 (cons 302 (strcat FilePath "!" SheetName))
 (cons 90 2)
 (cons 91 1179649)
 (cons 92 1)
 (cons 170 (dc 1 4));Year
 (cons 171 (dc 5 2));Month
 (cons 172 (dc 7 2));Day
 (cons 173 (+ (dc 10 2)6));Hour+6
 (cons 174 (dc 12 2));Minutes
 (cons 175 (dc 14 2));Seconds
 (cons 176 (dc 16 2));Milliseconds
 (cons 177 3)
 (cons 93 0)
 (cons 304 "")
 (cons 94 0)
 (cons 360 TempTC)
 (cons 305 "CUSTOMDATA")
 (cons 1 "DATAMAP_BEGIN")
 (cons 90 3)
 (cons 300 "ACEXCEL_UPDATEOPTIONS")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 1)
 (cons 91 1179649)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_CONNECTION_STRING")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 4)
 (cons 1 (strcat FilePath "!" SheetName))
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_SOURCEDATE")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 
 (cons 92 16)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 309 "DATAMAP_END")
)
)
(entmod DataLinkList)
(entmod (append (entget datdictename)(list (cons 3 DataLinkName) (cons 360 DLEM))))
(princ)
)

(defun c:Tbl (/ DLName)
(setq DLName (getstring T "Data Link Name: "))
(AddDataLink DLName nil);or use filename in place of nil
(vl-cmdf "-table" "L" DLName);IF YOU DIDN'T ADD IT TO THE FUNCTION
)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...