Results 1 to 9 of 9

Thread: Link text in Autocad with an Excel spreadsheet

  1. #1
    Member
    Join Date
    2012-04
    Posts
    18
    Login to Give a bone
    0

    Default Link text in Autocad with an Excel spreadsheet

    I am trying to use either 'Datalink' or 'Fields' to do the following:

    In Autocad, place a piece of text on the drawing and have it go after an excel spreadsheet, and only pull the information from certain cells in excel.

    There might be several rows that have the certain text to go after the other cells. If I place 'B-01' in Autocad I would like to have it pull all information
    from all (3) rows that start with 'B-01'

    Example:
    A Column B Column C Column D Column
    B-01 5" PVC UG
    B-01 3" PVC UG
    B-01 4" PVC UG
    B-02 8" EMT Surface
    B-03 4" Cond Flush


    Any help would greatly be appreciated.

  2. #2
    Administrator BlackBox's Avatar
    Join Date
    2009-11
    Posts
    5,714
    Login to Give a bone
    0

    Default Re: Link text in Autocad with an Excel spreadsheet

    Welcome to AUGI!

    Why not simply copy the desired cells from your Excel spreadsheet, and use the PASTESPEC (read "Paste Special") Command, select the "Paste Link" radio button, and select "AutoCAD Entities" under the "As:" option area?
    "How we think determines what we do, and what we do determines what we get."

    Sincpac C3D ~ Autodesk Exchange Apps

    Computer Specs:
    Dell Precision 3660, Core i9-12900K 5.2GHz, 64GB DDR5 RAM, PCIe 4.0 M.2 SSD (RAID 0), 16GB NVIDIA RTX A4000

  3. #3
    AUGI Addict fixo's Avatar
    Join Date
    2005-05
    Location
    Pietari, Venäjä
    Posts
    1,269
    Login to Give a bone
    0

    Default Re: Link text in Autocad with an Excel spreadsheet

    Load XL library from here:

    http://www.theswamp.org/index.php?to...4520#msg464520

    then add this code in Startup Suite, then run this code after:

    Code:
    ;| sample example to show how to read data from Excel with user interaction|;
    
    ;;_______________________________________________________________;;
    (defun C:XLTXT(/ lst path rangeinfo result xlapp xlbook xlbooks xlcell xlrange xlsheet xlsheets)
        (vl-load-com)
    
         (setq xlapp (xlgetapp))
      
         (_set xlapp 'visible :vlax-false)
      
       ;|check if no Excel documents were open|;
      (if (xlhasbooks xlapp)
        ;| if one or more open then get active document|;
         (setq xlbook (xlgetactivebook xlapp))
        (progn
         ;|otherwise ask user to select document from file dialog|;
         (setq path (getfiled "Select Excel file to read data from :"   (getvar "dwgprefix")   "xls;xlsx"  16 ))
         ;| get Workbooks collection|;
         (setq xlbooks (xlgetbooks xlapp))
         ;| open selected document|;
         (setq xlbook (xlopenbook xlbooks path))
         ;| activate this document (optional)|;
         (setq xlbook (xlgetactivebook xlapp))
         )
         )
    
       ;| get Worksheets collection|;
         (setq xlsheets (xlgetsheets xlbook))
       ;| get Worksheet object by index or by sheet name|;
         (setq xlsheet (xlgetsheet xlsheets 1))
      ;|or the same sheet|;
      ;|(setq xlsheet (xlgetsheet xlsheets "Sheet1"))|;
    
      (_set xlapp 'visible :vlax-true)
      ;| activate this sheet|;
    (setq xlsheet  (xlactivate xlsheet))
    
         ;|some different technic to read data|;
    
      ;| by selecting desired cell range on Excel screen|;
    (setq rangeinfo (xlgetreference xlapp "Select a Range of Cells" "Read Cell Range Example"))
        ;|or using simplified function "xlreadrangebyref": |;
       (setq result (xlreadrangebyref xlapp (cadr rangeinfo) nil))
       ;;|or using extended function "xlreadrange": |;
       ;;(setq result (xlreadrange xlapp (cadr rangeinfo) nil nil nil ))
         ;|let Excel to display alert box to inform user|;
          (xlshout xlapp :vlax-true)
       ;|we don't need to save document, so we can close it without saving|;
    
         (xlbookclose xlbook nil)
           ;|quit Excel application|;
         (xlquit xlapp)
          ;| clean up memory and relese objects we're using|;
          ;| better yet they follow in anti-hierarchical order|;
          ;| from child to parent|;
         (xlcleanup  (list xlcell xlrange xlsheet xlsheets xlbook xlapp))
          
         (setq xlapp nil)
    
      ;|go to Acad|;
      (setq lst (vl-remove-if '(lambda (x)(not (eq "B-01" (car x))))result))
      (alert (vl-princ-to-string lst))
      (alert "Do whatwever you need with retrieved list here")
      (princ)
      )
    ~'J'~

  4. #4
    Certifiable AUGI Addict
    Join Date
    2015-11
    Location
    Jo'burg SA
    Posts
    4,512
    Login to Give a bone
    0

    Default Re: Link text in Autocad with an Excel spreadsheet

    Yep, if you go the route RenderMan explained, I'd advise you to set a named range in Excel. Then after you've performed the PasteSpec, open the DataExtraction dialog (the command is DataExtraction ). In there you should see something like "Excel Link 1", right click and Edit. If you can't see stuff like Selected Range, then browse to the XLS(x) file again using the Browse button. Then you can pick the Range by Name. The nice thing here is you can have that range grow/shrink and you won't need to update the link manually - just insert / delete rows / columns, or reselect the Range in Excel.

    For the lisp thing, you'd have to delete the text and rerun the lisp every time there's an update.

  5. #5
    Member
    Join Date
    2012-05
    Posts
    4
    Login to Give a bone
    0

    Default Re: Link text in Autocad with an Excel spreadsheet

    help
    i am use this code ,bat command "no function definition: XLGETAPP".
    tanks!

  6. #6
    AUGI Addict fixo's Avatar
    Join Date
    2005-05
    Location
    Pietari, Venäjä
    Posts
    1,269
    Login to Give a bone
    0

    Default Re: Link text in Autocad with an Excel spreadsheet

    Quote Originally Posted by yxh_1202230274 View Post
    help
    i am use this code ,bat command "no function definition: XLGETAPP".
    tanks!
    Use link below and load this library first:
    http://www.theswamp.org/index.php?to...4520#msg464520
    then you can run a program

    ~'J'~

  7. #7
    Member
    Join Date
    2017-06
    Posts
    2
    Login to Give a bone
    0

    Default Re: Link text in Autocad with an Excel spreadsheet

    I too am having a similar issue. I want to set up an Excel spreadsheet, where the AutoCAD used puts in a few inputs, and Excel does the calculations, then have the results linked to fields in the AutoCAD drawing. Now this works well if the Excel data is ONLY numbers, but if there is text, say for instance in a bill of material, it will not give the data, only #####. There HAS to be a way to extract this data, since there are 3rd party programs that will do it. But, I feel that it is an unnecessary expense for the company that I work for to spend $100's of dollars for something that should already be incorporated into AutoCAD. I am hoping one of you LISP gurus can point me in the direction of a routine or any other option to get my desired results. Thank you in advance for your time.

  8. #8
    I could stop if I wanted to
    Join Date
    2005-06
    Location
    CORDOBA-ARGENTINA
    Posts
    275
    Login to Give a bone
    0

    Default Re: Link text in Autocad with an Excel spreadsheet

    Field can have formula on it .
    Please upload the DWG , the XLS , or the formula you want to apply.

    By the way . I afraid to say that our friend FIXO , is not more at any forum. I is my friend , if you see the avatar , it show my country flag, Argentina, It was on a World Football Championship , he was Argentina football fan. Maybe I´m wrong.

  9. #9
    All AUGI, all the time
    Join Date
    2003-07
    Posts
    555
    Login to Give a bone
    0

    Default Re: Link text in Autocad with an Excel spreadsheet

    This may be helpful, http://www.cadtutor.net/forum/showth...xcel-on-on-fly

    If you have a look at Lee-mac.com parse.lsp it will pull the numbers out of a text string.

Similar Threads

  1. How to import text from a Excel spreadsheet to Acad (using ObjectDbx)
    By Michel.Levesque.ST522691 in forum VBA/COM Interop
    Replies: 0
    Last Post: 2014-03-20, 09:19 PM
  2. How to link an excel spreadsheet
    By Iceberg in forum AutoCAD Tables
    Replies: 5
    Last Post: 2014-01-30, 04:00 PM
  3. Link Block attribute with Excel spreadsheet
    By Wish List System in forum AutoCAD Wish List
    Replies: 3
    Last Post: 2013-10-27, 07:02 PM
  4. 2012: Link text in Autocad with an Excel spreadsheet
    By wiegmanroger433258 in forum AutoCAD Customization
    Replies: 8
    Last Post: 2012-04-26, 09:33 PM
  5. Excel Spreadsheet and AutoCAD 2005
    By daveng in forum AutoCAD Customization
    Replies: 1
    Last Post: 2005-09-09, 04:05 PM

Posting Permissions

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