handasa Posted July 14, 2017 Share Posted July 14, 2017 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 ) Quote Link to comment Share on other sites More sharing options...
ReMark Posted July 14, 2017 Share Posted July 14, 2017 Why aren't you using the data link feature that already exists in AutoCAD? Quote Link to comment Share on other sites More sharing options...
handasa Posted July 14, 2017 Author Share Posted July 14, 2017 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 Quote Link to comment Share on other sites More sharing options...
rlx Posted July 18, 2017 Share Posted July 18, 2017 just get the activesheet property (setq activesheet (vlax-get-property excel-app"activesheet")) gr. Rlx Quote Link to comment Share on other sites More sharing options...
BIGAL Posted July 19, 2017 Share Posted July 19, 2017 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. Quote Link to comment Share on other sites More sharing options...
rlx Posted July 19, 2017 Share Posted July 19, 2017 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 Quote Link to comment Share on other sites More sharing options...
handasa Posted July 19, 2017 Author Share Posted July 19, 2017 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 ? Quote Link to comment Share on other sites More sharing options...
rlx Posted July 19, 2017 Share Posted July 19, 2017 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 Quote Link to comment Share on other sites More sharing options...
handasa Posted July 20, 2017 Author Share Posted July 20, 2017 thanks again , Rlx you saved my day ... thanks bro Quote Link to comment Share on other sites More sharing options...
Jagdish Posted February 13, 2018 Share Posted February 13, 2018 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 ) Quote Link to comment Share on other sites More sharing options...
devitg Posted February 13, 2018 Share Posted February 13, 2018 Want for yes?, or WONT for want not ? Please upload both the XLS and the dwg (excelData) It is not at lisp Quote Link to comment Share on other sites More sharing options...
BIGAL Posted February 14, 2018 Share Posted February 14, 2018 jagdish this question has been asked before do a search here. Quote Link to comment Share on other sites More sharing options...
Jagdish Posted February 19, 2018 Share Posted February 19, 2018 Dear Devitg Please find attached both file. M-ANM-3A1DPW-CJ00-SDR-000004-GROUND FLOOR.dwg Cable Tray Sections WEST DEPOT Building C.xlsx Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.