Change Datalink Range

[DGCSCAD]

Hello all,

I'm looking to change the range in an AutoCad datalink based on which (Excel xlsx) row (always column A) matches a given string.

1. Search an Excel file and return the value of the row where the string matches.

2. Update the datalink to the new row value in AutoCad.

Any ideas to set me on the right path to achieve this?

[Sea-Haven]

There are lisp examples about reading Excel from CAD.

You can get the range of the current sheet, as row and column.

So can do a loop looking for the text.

To get you started.

(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)

(setq CurRegion (vlax-get-property (vlax-get-property
(vlax-get-property  myxl "ActiveSheet") "Range" "A1") "CurrentRegion")
)
(setq MaxRow# (vlax-get-property (vlax-get-property CurRegion "Rows") "Count"))
(setq MaxColumn# (vlax-get-property (vlax-get-property CurRegion "Columns") "Count"))

(defun getcell2 (row column / )
(setq cells (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Cells"))
(setq cell (vlax-get (vlax-variant-value  (vlax-get-property cells "Item" row column)) 'value))
)

[DGCSCAD]

I appreciate that Sea-Haven.

How do I update the datalink?

I found this bit of code, but not sure how to apply the new strings to the list, and if I'll need to adjust anything else:

(cons 301 (strcat "Data Link\n" DataLinkName "\n" FilePath "\nLink details: Entire sheet: " SheetName))

New Path: "C:\\Datalink Test\\Data Link Test 002.xlsx"

New Range: "D4:D4"

[Sea-Haven]

Ok that is a different question, I misunderstood your request. 

I am not sure but this seems to be the path to follow

(setq adict (dictsearch (namedobjdict) "ACAD_DATALINK"))
((-1 . <Entity name: 7c16dab0>) (0 . "DICTIONARY") (5 . "B954") (102 . "{ACAD_REACTORS") (330 . <Entity name: 9648beb0>) (102 . "}") (330 . <Entity name: 9648beb0>) (100 . "AcDbDictionary") (281 . 1) (3 . "alan") (350 . <Entity name: 66958da0>))
(setq link (entget (cdr (assoc 350 adict))))
(setq address (cdr (assoc 302 link)))
; not sure what to do now
; D:\\MYexcels\\XXX.xlsx!CSVData!A5:C23"

[DGCSCAD]

You were going in the right direction, it's just me changing gears. My apologies.

I decided to keep the opening of any spreadsheets for data inquiries out of this, so I'm just concerned about getting the datalink updated with a new spreadsheet and new range within AutoCad.

Using this to get the datalink codes:

(setq datalink (dictsearch (cdr (assoc -1 datalinkDict)) "Datalink Name"))

...I believe I'll just need to update the 1, 301, and 302 codes. I'll post up my findings.

[DGCSCAD]

I'm able to edit the 301 and 302 codes, but there are three 1's. The first is "AcExcel", the second is "DATAMAP_BEGIN" and the third is the one I'm trying to reach to input the new path and range (same as 302 code), but I don't know how to access it.

Here's the code I'm using:

(setq datalinkDict001 (dictsearch (namedobjdict) "ACAD_DATALINK"))
(setq datalink001 (dictsearch (cdr (assoc -1 datalinkDict001)) "JEI - Cable"))
(setq datapath_orig001 (cdr (assoc 1 datalink001)))
(setq datapath001 (vl-string-subst "..\\Apps\\LISP\\Datalink Test\\Data Link Test 002.xlsx!Sheet1!D4:D4" datapath_orig001 (cdr (assoc 1 datalink001))))
(entmod (subst (cons 1 datapath001) (assoc 1 datalink001) datalink001))

Any pointers?

[DGCSCAD]

I'm starting to wonder if editing the spreadsheet location and range in an existing AutoCad datalink is even possible. 

Parameters:

Existing datalink info:

Location: C;\\PRD\\LISP\\Datalink Test 001.xlsx

Range: D1:D1

New Datalink info:

Location: C:\\PRD\\LISP\\Datalink Test\\Datalink Test 002.xlsx

Range: D4:D4

The 301 code/edit:

(setq datalinkDict301 (dictsearch (namedobjdict) "ACAD_DATALINK"))
(setq datalink301 (dictsearch (cdr (assoc -1 datalinkDict301)) "JEI - Cable"))
(setq datapath_orig301 (cdr (assoc 301 datalink301)))
(setq datapath301 (vl-string-subst "Data Link\nJEI - Cable\nC:\\PRD\\Apps\\LISP\\Datalink Test\\Data Link Test 002.xlsx\nLink details: Range: D4:D4" datapath_orig301 (cdr (assoc 301 datalink301))))
(entmod (subst (cons 301 datapath301) (assoc 301 datalink301) datalink301))

Result:

<snip> (301 . "Data Link\nJEI - Cable\nC:\\PRD\\Apps\\LISP\\Datalink Test\\Data Link Test 002.xlsx\nLink details: Range: D4:D4") <snip>

^that works, afaik^

302 code/edit:

(setq datalinkDict302 (dictsearch (namedobjdict) "ACAD_DATALINK"))
(setq datalink302 (dictsearch (cdr (assoc -1 datalinkDict302)) "JEI - Cable"))
(setq datapath_orig302 (cdr (assoc 302 datalink302)))
(setq datapath302 (vl-string-subst "..\\Apps\\LISP\\Datalink Test\\Data Link Test 002.xlsx!Sheet1!D4:D4" datapath_orig302 (cdr (assoc 302 datalink302))))
(entmod (subst (cons 302 datapath302) (assoc 302 datalink302) datalink302))

Result:

<snip> (302 . "..\\Apps\\LISP\\Data Link Test 001.xlsx!Sheet1!D1:D1..\\Apps\\LISP\\Data Link Test 001.xlsx!Sheet1!D1:D1..\\Apps\\LISP\\Datalink Test\\Data Link Test 002.xlsx!Sheet1!D4:D4") <snip>

The 302 edit is resulting in an appended string and causes an error when opening the datalink for editing.

I can't seem to get to the embedded code 1 data to replace that string.

Has anyone else attempted this? Any ideas?

[DGCSCAD]

This gets me into the correct nested dxf code 1:

(setq datalinkDict360 (dictsearch (namedobjdict) "ACAD_DATALINK"))
(setq datalink001 (dictsearch (cdr (assoc -1 datalinkDict360)) "JEI - Cable"))
(setq datalink360 (entget (cdr (assoc 360 datalinkDict360))))
(setq tmp1 (member (assoc 305 datalink360) datalink360))
(setq tmp2 (member (assoc 90 tmp1) tmp1))
(setq tmp3 (cdr (assoc 1 tmp2)))
(setq datapath001 (vl-string-subst "..\\Apps\\LISP\\Datalink Test\\Data Link Test 002.xlsx!Sheet1!D4:D4" tmp3 (cdr (assoc 1 tmp2))))
(entmod (subst (cons 1 datapath001) (assoc 1 tmp2) datalink001))

...and after running the above for 301 and 302 as well, I'm modifying the list in the correct places:

(-1 . <Entity name: 26820ed5c70>)
    (0 . "DATALINK")
    (5 . "2048237")
    (102 . "{ACAD_REACTORS")
    (330 . <Entity name: 26819012e00>)
    (102 . "}")
    (330 . <Entity name: 26819012e00>)
    (100 . "AcDbDataLink")
    (1 . "AcExcel")
    (300 . "")
    (301 . "Data Link\nJEI - Cable\nC:\\PRD\\Apps\\LISP\\Datalink Test\\Data Link Test 002.xlsx\nLink details: Range: D4:D4")
    (302 . "..\\Apps\\LISP\\Datalink Test\\Data Link Test 002.xlsx!Sheet1!D4:D4")
    (90 . 2)
    (91 . 1179649)
    (92 . 1)
    (170 . 2023)
    (171 . 11)
    (172 . 15)
    (173 . 20)
    (174 . 27)
    (175 . 30)
    (176 . 0)
    (177 . 3)
    (93 . 0)
    (304 . "")
    (94 . 13)
    (330 . <Entity name: 26816558ee0>)
    (330 . <Entity name: 2681640b490>)
    (330 . <Entity name: 2682fad5f70>)
    (330 . <Entity name: 2682fadf960>)
    (330 . <Entity name: 26821039ad0>)
    (330 . <Entity name: 26785b5f440>)
    (330 . <Entity name: 267af4d8db0>)
    (330 . <Entity name: 267f4440f50>)
    (330 . <Entity name: 267f44379a0>)
    (330 . <Entity name: 267af4fab90>)
    (330 . <Entity name: 26785b60600>)
    (330 . <Entity name: 267f44a58f0>)
    (330 . <Entity name: 26820d90f70>)
    (360 . <Entity name: 26820ed5c80>)
    (305 . "CUSTOMDATA")
    (1 . "DATAMAP_BEGIN")
    (90 . 3)
    (300 . "ACEXCEL_CONNECTION_STRING")
    (301 . "DATAMAP_VALUE")
    (93 . 2)
    (90 . 4)
    (1 . "..\\Apps\\LISP\\Datalink Test\\Data Link Test 002.xlsx!Sheet1!D4:D4")
    (94 . 0)
    (300 . "")
    (302 . "")
    (304 . "ACVALUE_END")
    (300 . "ACEXCEL_SOURCEDATE")
    (301 . "DATAMAP_VALUE")
    (93 . 2)
    (90 . 8)
    (92 . 16)
    (310 . "E7070B0003000F000C003B001D000000")
    (94 . 0)
    (300 . "")
    (302 . "")
    (304 . "ACVALUE_END")
    (300 . "ACEXCEL_UPDATEOPTIONS")
    (301 . "DATAMAP_VALUE")
    (93 . 2)
    (90 . 1)
    (91 . 1)
    (94 . 0)
    (300 . "")
    (302 . "")
    (304 . "ACVALUE_END")
    (309 . "DATAMAP_END")

...but the results aren't showing up correctly in the datalink:

Not sure what I'm missing...??

entupd?

[DGCSCAD]

It appears this needs to be done in .NET, after much digging and finding sources like:

[url=https://forums.autodesk.com/t5/vba/repath-the-excel-reference-through-vba/m-p/5436104/highlight....

I guess it's time to take the plunge into the .NET world.