Jump to content
handasa

make table from "opened" excel workbook to autocad drawing

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 :)

[CODE];; © 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 8)
(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
)
[/CODE]

Share this post


Link to post
Share on other sites
Why aren't you using the data link feature that already exists in AutoCAD?

Share this post


Link to post
Share on other sites
[quote name='ReMark']Why aren't you using the data link feature that already exists in AutoCAD?[/QUOTE]

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

Share this post


Link to post
Share on other sites
just get the activesheet property

[code] (setq activesheet (vlax-get-property excel-app"activesheet")) [/code]

gr. Rlx

Share this post


Link to post
Share on other sites
Get a copy of Getexel.lsp it may be more usefull for what you want it is more about getting/putting cells to and from excel.

Share this post


Link to post
Share on other sites
something like this? :


[code]

(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"))
)

[/code]
gr.Rlx

Share this post


Link to post
Share on other sites
[quote name='rlx']something like this? :


[code]

(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"))
)

[/code]
gr.Rlx[/QUOTE]

thanks ,Rlx

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

Share this post


Link to post
Share on other sites
[quote name='handasa']thanks ,Rlx

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


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

Share this post


Link to post
Share on other sites
Dear

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

[CODE];; © 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 8)
(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
)
[/CODE][/QUOTE]

Share this post


Link to post
Share on other sites
Want for yes?, or WONT for want not ?


Please upload both the XLS and the dwg



[QUOTE](excelData)
[/QUOTE]

It is not at lisp

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×