Copy values from Excel to Lookup table single move

[nuno.ramos5SJR2]

Hello everybody
It is with pleasure that I post this special request in this forum, and I hope that you can help in this matter that surely has already come up to someone.
Copying values from Excel to a Lookup table in AUTOCAD, in a single movement, is something I've been looking for for a long time. I recently discovered two almost perfect LISP routines that allow you to copy Excel to Lookup and vice versa in a single move. The problem is that it only works with Excel 2003 (no longer supported by Microsoft) and Autocad 2010.
I share with you the routines (comments in Russian) and ask if anyone can rewrite them so that they work in the latest versions of Excel and Autocad 2021 and above. I've tested it in Autocad 2021 and I guarantee it doesn't work.
If anyone manages to successfully rewrite the LISP routines, please share, they will surely be a lifeline for many CAD users.
Thank you very much and I pray that someone succeeds.

[hosneyalaa]

upload your sample.dwg

[nuno.ramos5SJR2]

Hi  hosneyalaa.

Thank you for your interest. 

I sent you the DWG block and the EXCEL final with the data that i need to import/copy to the LOOKUP inside the block called RCVFinalSelection. Columns A to D are to show in the Input Properties side of the LOOKUP, and column E is to show in the Lookup Properties side. Ove course depending in the block the number of columns can be higher or lower.

Also sometimes we have the block LOOKUP already feel with data and we need to extract to EXCEL. That is why i shared two different LSP routines.

Hope you can help. Thank you very much.

[Sea-Haven]

I have done some stuff with a client look up tables in excel, take 2 numbers put in excel and lookup checks returning yes or no in a cell. Its a height width ratio thing.

I opened the excel and appears to be just a lot of values and not a true Excel Lookup function.

Please explain what you mean by look up.

Any way try

(setq g_oex (vlax-get-or-create-object "Excel.Application.11"))

my current version
(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)

it looks like the .11 is not needed.

[nuno.ramos5SJR2]

Thanks for helping

LOOKUP it´s a parameter of the BLOCK AUTHORING PALETTES in AutoCAD dynamic blocks. Basically assigns custom property values to the dynamic block reference. We do a list of the custom properties needed to control the dynamic block in EXCEL (its easier with the EXCEL automation options) and then we need to past that table to the LOOKUP parameter table. Please see scheme attached.

[Sea-Haven]

I read from excel a value or values then update a dynamic block, using the VL functions to adjust a dynamic block. So could pick in Excel say A1, B1 for length and width of a rectang the dynamic block would be a pline 1x1 so updates to the size values of A1 x B1. I have pick a range in excel called from Autocad.

Skips the block authoring palette which I know nothing about. 

So its the end for me. Some one else may be able to help.

[nuno.ramos5SJR2]

Hi Sea-Haven

We also link Excel tables to dynamic properties of the block, using link tools of Autocad. But in this case we are talking about LOOKUP tables to drive the block that in some cases have hundreds of inputs. AUTODESK still doesn´t allow massive direct copy of values from EXCEL to LOOKUP tables. The routines LSP that i shared do exactly this, but only works in previous versions of EXCEL and AUTOCAD. Perhaps an LPS experiment  programmer can update this routines and help many cad users with this problem. Never the less thank you for your interest.

[Sea-Haven]

Did you look at the code re Application its is different than yours.

[nuno.ramos5SJR2]

Hi Sea-Haven

Yes, just made that change, and I started to have hopes because the message saying to use EXCEL2003 no longer shows. The process seams to go the right way, with the steps that I use in AUTOCAD20210 but sadly the end result it´s an empty LOOKUP table. But it is a start. Hopefully someone can accomplish the end result.
Here is the changed code: (i had to remove the .11 also in the first line)

(setq g_oex (vlax-get-or-create-object "Excel.Application"))
;my current version
(or (setq myxl (vlax-get-object "Excel.Application"))
(setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)
(if (null g_oex) (progn (alert "Невозможно запустить Microsoft Excel 2003") (exit)))
(if vis (vlax-put-property g_oex "Visible" :vlax-true))
)

Thank you very much for your interest. You already gave me hope 

[Sea-Haven]

Like this

(setq g_oex (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible g_oex :vlax-true)
(vlax-put-property g_oex 'ScreenUpdating :vlax-true)
(vlax-put-property g_oex 'DisplayAlerts :vlax-true)
(if (null g_oex) (progn (alert "Невозможно запустить Microsoft Excel 2003") (exit)))

[nuno.ramos5SJR2]

Good morning Sea-Haven
After updating the code the result is the same, an empty LOOKUP table. Here is the line showed in the AUTOCAD 2021 command line:

Command: APPLOAD
Excel-Lookup.lsp successfully loaded.
Command: Óêàæèòå íóæíûé âàì áëîê
53
nil
Command: 

Attached also sent you the updated routine. Thanks for trying to help.

[nuno.ramos5SJR2]

Hi everybody

I have some fresh news about this. I´ve just tried the other routine Lookup-Excel.lsp, that does exactly the opposite that I'm looking for, and just removing the .11 in the end of the line showed under, does what´s supposed to do. So why is that doing exactly the same in routine Excel-Lookup.lsp things don´t work?? SEA-HAVEN it seams so close and yet... Perhaps if you compare the two routine you´ll be able to discover the solution! Much appreciated to all.

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

[Sea-Haven]

A couple of possible changes (defun ex12_set_connect ( / ) vis removed, by having vis there its looking for a variable when called, the get-create looks for a excel is open or if not will open a new excel.

(defun ex12_set_connect ( / )
(setq g_oex (vlax-get-or-create-object "Excel.Application"))
(if (null g_oex) (progn (alert "Невозможно запустить Microsoft Excel 2003") (exit)))
(vla-put-visible g_oex :vlax-true)
(vlax-put-property g_oex 'ScreenUpdating :vlax-true)
(vlax-put-property g_oex 'DisplayAlerts :vlax-true)
)

Try this code by just pasting to command line, make sure the Excel has a value in cell A1.

(setq myRange (vlax-get-property  (vlax-get-property g_oex "ActiveSheet") "Range" "A1"))
(princ  (vlax-variant-value (vlax-get-property myRange 'Value2)))

(setq myRange (vlax-get-property (vlax-get-property g_oex "ActiveSheet") "Range" "A1"))
#<VLA-OBJECT Range 0000000023BB7738>
: (princ (vlax-variant-value (vlax-get-property myRange 'Value2)))
abc"abc"

[nuno.ramos5SJR2]

Good morning Sea-Haven
After pasting first line to command line, here is what happens:
Command: (setq myRange (vlax-get-property (vlax-get-property g_oex "ActiveSheet") "Range" "A1"))
; error: bad argument type: VLA-OBJECT nil

Perhaps I'm doing something wrong. Is it possible for you trying to connect to my pc and look into it?

[nuno.ramos5SJR2]

Sea-Haven
I can add that removing the vis and pasting your code to the routine, here is the message in the command line:
Command: APPLOAD
Excel-Lookup.lsp successfully loaded.
Command: ; error: too many arguments

[nuno.ramos5SJR2]

Hi guys,

I´ve translated (Google translator) the Russian comments inside the routine. Perhaps this can help a little bit more to find the solution. Thanks to all for helping.  

[nuno.ramos5SJR2]

I´ve done a research and find out that Nikolay Poleshchuk (Poleshchuk N.N) helped building this routine. This is his page http://poleshchuk.spb.ru/cad/PNNe.htm 

[nuno.ramos5SJR2]

Good morning guys

I’ve some bad news regarding this subject. I sent an e-mail to Poleshchuk and he was kind to reply. I share with you his answer:

Dear friend Nuno,

Yes, I am the right person you are searching for. The article of my friend Andrey Lazebny who passed away is still on my site: http://poleshchuk.spb.ru/cad/2009/tainypode.htm

All that was published there was possible only in AutoCAD 2007 because even in AutoCAD 2010 the internal structure of DWG file changed dramatically, and the facilities found by Andrey disappeared (or closed by Autodesk).
So the total answer is "mpossible".

Kind regards, Nikolay Poleshchuk
http://poleshchuk.spb.ru/cad/eng.html

So this is something that I think only by users joining forces together, maybe we can push Autodesk to incorporate in new versions of Autocad. After all data handle is something that all of companies do. It makes no sense not to be able to copy from a simple Excel spreadsheet to Lookup table.

From my part I am available to share my not so good experience with Autocad Lookup tables.

Thanks to all who tried to help, specially Sea-Haven

[hosneyalaa]

hi

My language is not good
Is the direction of the arrow , correct
can you explain through Video what you want

[nuno.ramos5SJR2]

Hi hosneyalaa
I think no video is required. If you are a CAD user, you know what´s a LOOKUP table and the time spent to fill required parameters to drive a dynamic block. Your understanding of the arrows is correct. In EXCEL we fill all the table with needed parameters values for the block. But after that, we can´t copy the entire table to the LOOKUP parameter of AUTOCAD. According to the author of the LSP routine EXCEL-LOOKUP.LSP, since 2010, the internal structure of the dwg file dramatically changed, so now it´s impossible to accomplish what we are looking for. Nontheless thank you for your interest.