+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13
  1. #1
    Senior Member
    Discipline
    Electrical
    Using
    AutoCAD 2015
    Join Date
    Jul 2015
    Location
    Egypt
    Posts
    126

    Default make table from "opened" excel workbook to autocad drawing

    Registered forum members do not see this ad.

    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
    )

  2. #2
    Quantum Mechanic ReMark's Avatar
    Computer Details
    ReMark's Computer Details
    Operating System:
    Windows 7 Pro 64-bit
    Computer:
    Thinkmate
    Motherboard:
    Intel DX58SO2 LGA1366 X58
    CPU:
    Intel i7-960 Quad-core 3.20GHz 8MB cache
    RAM:
    12GB (3x4GB) PC3-106000 DDR3
    Graphics:
    nVidia Quadro K4200, 4GB GDDR5
    Primary Storage:
    150GB Velocipraptor 10,000 rpm
    Secondary Storage:
    none
    Monitor:
    Dell P24LLH - 24" wide screen LCD
    Discipline
    See details...
    ReMark's Discipline Details
    Occupation
    CAD Draftsman/Designer...chemical manufacturing.
    Discipline
    See details below.
    Details
    I work for a specialty chemical manufacturer. I do a little bit of everything from P&IDs to civil to architectural and structural.
    Using
    AutoCAD 2018
    Join Date
    Nov 2005
    Location
    Norwalk, CT USofA
    Posts
    46,852

    Default

    Why aren't you using the data link feature that already exists in AutoCAD?
    "I have only come here seeking knowledge. Things they wouldn't teach me of in college." The Police

    Eat brains...gain more knowledge!

    I'm now a full member of the Society for the Promotion of Mediocrity in CAD. Standards? We don't need no stinkin' standards! Take whatever advice I offer and do the opposite.

  3. #3
    Senior Member
    Discipline
    Electrical
    Using
    AutoCAD 2015
    Join Date
    Jul 2015
    Location
    Egypt
    Posts
    126

    Default

    Quote Originally Posted by ReMark View Post
    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

  4. #4
    Super Member rlx's Avatar
    Computer Details
    rlx's Computer Details
    Operating System:
    W10
    Computer:
    i74ghz/ssd500/2tbhdd
    Discipline
    Electrical
    rlx's Discipline Details
    Occupation
    electrical designer dragon
    Discipline
    Electrical
    Details
    I dont excel in anything but I rearly give up
    Using
    AutoCAD 2016
    Join Date
    Nov 2014
    Location
    Bergen op Zoom , Netherlands
    Posts
    966

    Default

    just get the activesheet property

    Code:
     (setq activesheet (vlax-get-property excel-app"activesheet"))
    gr. Rlx

  5. #5
    Quantum Mechanic BIGAL's Avatar
    Using
    Civil 3D 2016
    Join Date
    Dec 2005
    Location
    GEELONG AUSTRALIA
    Posts
    10,511

    Default

    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.
    A man who never made mistakes never made anything

  6. #6
    Super Member rlx's Avatar
    Computer Details
    rlx's Computer Details
    Operating System:
    W10
    Computer:
    i74ghz/ssd500/2tbhdd
    Discipline
    Electrical
    rlx's Discipline Details
    Occupation
    electrical designer dragon
    Discipline
    Electrical
    Details
    I dont excel in anything but I rearly give up
    Using
    AutoCAD 2016
    Join Date
    Nov 2014
    Location
    Bergen op Zoom , Netherlands
    Posts
    966

    Default

    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"))
    )
    gr.Rlx

  7. #7
    Senior Member
    Discipline
    Electrical
    Using
    AutoCAD 2015
    Join Date
    Jul 2015
    Location
    Egypt
    Posts
    126

    Default

    Quote Originally Posted by rlx View Post
    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"))
    )
    gr.Rlx
    thanks ,Rlx

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

  8. #8
    Super Member rlx's Avatar
    Computer Details
    rlx's Computer Details
    Operating System:
    W10
    Computer:
    i74ghz/ssd500/2tbhdd
    Discipline
    Electrical
    rlx's Discipline Details
    Occupation
    electrical designer dragon
    Discipline
    Electrical
    Details
    I dont excel in anything but I rearly give up
    Using
    AutoCAD 2016
    Join Date
    Nov 2014
    Location
    Bergen op Zoom , Netherlands
    Posts
    966

    Default

    Quote Originally Posted by handasa View Post
    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

  9. #9
    Senior Member
    Discipline
    Electrical
    Using
    AutoCAD 2015
    Join Date
    Jul 2015
    Location
    Egypt
    Posts
    126

    Default

    thanks again , Rlx
    you saved my day ... thanks bro

  10. #10
    Forum Newbie
    Discipline
    Electrical
    Using
    AutoCAD 2016
    Join Date
    Feb 2018
    Posts
    2

    Default We wont to use Excel Formatting so please Help !!!

    Registered forum members do not see this ad.

    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
    )
    [/QUOTE]

Similar Threads

  1. Insert a title Block from excel into a opened dwt with excel vba
    By alex140590 in forum .NET, ObjectARX & VBA
    Replies: 2
    Last Post: 15th Jul 2016, 11:43 am
  2. Default drawing opened when starting AutoCAD
    By maliakuhl in forum AutoCAD Beginners' Area
    Replies: 6
    Last Post: 22nd Jan 2015, 03:43 pm
  3. Excel table in drawing is not updating
    By tazzzz in forum AutoCAD Beginners' Area
    Replies: 4
    Last Post: 12th Sep 2013, 05:37 pm
  4. Copy Excel Table Into AutoCAD Drawing
    By Bill Tillman in forum AutoLISP, Visual LISP & DCL
    Replies: 0
    Last Post: 25th Feb 2012, 04:21 am
  5. Replies: 3
    Last Post: 13th Feb 2012, 06:09 pm

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts