huygens Posted December 16, 2015 Share Posted December 16, 2015 Hi all, i already found a great lisp bij Juan Villareal . With this lisp i can select a range in a specific excelsheet. It is added in Autocad but without excel formatting could the excelformatting be added? ;; © 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 arq "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 December 16, 2015 Share Posted December 16, 2015 Users can specify a range of cells from Excel (with formatting) to be brought into AutoCAD using the DATALINK option of the TABLE command. Quote Link to comment Share on other sites More sharing options...
huygens Posted December 16, 2015 Author Share Posted December 16, 2015 thanks. but if you wish to do a lot via commandline than the lisp i placed works great. But. It doesnt automatically keep excel formatting. And i know there is an option to keep this in the Datalink menu. So this should be placed in the lisp code somehow cheers Quote Link to comment Share on other sites More sharing options...
EComstock Posted May 16, 2016 Share Posted May 16, 2016 Belatedly, under (setq DataLinkList : (cons 1 "AcExcel") (cons 300 "") (cons 301 (strcat "Data Link\n" DataLinkName "\n" FilePath "\nLink details: Entire sheet: " SheetName)) (cons 302 (strcat RelPath "!" SheetName)) (cons 90 2) (cons 91 1835009) ; keep Excel formatting Additionally, if you want a relative path instead of a full path, you can insert this into the code as well: (setq miss (vl-string-mismatch (getvar 'dwgprefix) (vl-filename-directory FilePath))) (setq count 0) (setq num nil) (setq pfx (substr (vl-filename-directory FilePath) (1+ miss))) (while (< 0 (strlen pfx)) (setq count (1+ count)) (setq num (vl-string-search "\\" pfx)) (if (not num) (setq num (- (strlen pfx) 1))) (setq pfx (substr pfx (+ 2 num))) ) ; end while (setq path "") (while (< -1 count) (setq path (strcat "..\\" path)) (setq count (1- count)) ) ; end while (setq RelPath (strcat path (vl-filename-base FilePath) (vl-filename-extension FilePath))) If you need the full code, I'll be happy to post Villareal's code with my edits. Cheers, E Comstock Quote Link to comment Share on other sites More sharing options...
huygens Posted May 17, 2016 Author Share Posted May 17, 2016 just one post yet Comstock? keep up the good work! the only thing that would be great if you added it would be a commandline only version And a complete code woul be cool thank you anyway! Quote Link to comment Share on other sites More sharing options...
Arni Posted September 4, 2019 Share Posted September 4, 2019 Can you give me a hint, please? how to set a hard range in relation to the excel and edit if necessary Quote Link to comment Share on other sites More sharing options...
BIGAL Posted September 4, 2019 Share Posted September 4, 2019 Arni google getexcel.lsp or any other example will have a method of selecting a range of cells. Quote Link to comment Share on other sites More sharing options...
Arni Posted September 4, 2019 Share Posted September 4, 2019 1 hour ago, BIGAL said: Arni google getexcel.lsp or any other example will have a method of selecting a range of cells. Thank you, getexcel.lsp creates the table and copies the value but does not create a link to Excel and Tbl.lsp linking the data to the entire sheet I'd appreciate it if you could tell me how to link the data to the range, or tell me where to find Quote Link to comment Share on other sites More sharing options...
BIGAL Posted September 6, 2019 Share Posted September 6, 2019 This is the key word in code Entire sheet: but no idea what to replace it with. It may be as simple as A1,G56: A data link object with range should reveal the 301 property. 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.