Jump to content
Sambuddy

Lisp to open an excel file and search content

Recommended Posts

Sambuddy

Good afternoon,

My question has two parts:

1) Is there a LISP that can open an excel file by entering a command? Example: upon entering "POP" excel file in a directory on the server say, "F:\SALLE A DESSIN\2- ZONE RÉPERTOIRE_pop.xlsx" would open.

2) This excel contains all of our project numbers. All projects are hyperlinked so by clicking on each project the project directory/ folder would open. Is it possible to enter a command then enter the project number in autocad to then the excel file is searched and project directory is opened without having to open the excel and use find command in excel to open the project directory (All done in cad)?

I am not sure if it makes a difference that the excel file in question is password protected and you have to press on "Read Only" button before entering the file.

Thank you,

Sam

image.thumb.png.e2cdedd02d708c5416de6f12d7a7b2dd.png

Share this post


Link to post
Share on other sites
BIGAL

There is a huge amount of excel<->Autocad out there, start with something like Getexcel.lsp that has a lot of code functions. You can open a specific excel. The only problem would be the filename but if all projects are in some consistent directory structure its achievable.

Share this post


Link to post
Share on other sites
rlx
4 hours ago, BIGAL said:

There is a huge amount of excel<->Autocad out there, start with something like Getexcel.lsp that has a lot of code functions. You can open a specific excel. The only problem would be the filename but if all projects are in some consistent directory structure its achievable.

 

Also (as usual) no test (excel) file included. Maybe you have a crystal ball Bigal , but mine is missing (though I suspect my wife knows more about that :P )

Share this post


Link to post
Share on other sites
Sambuddy

All I could provide would be the snapshot not the actual excel file as a test file - it would not be useful as all hyperlinks are linked to the server and cannot function even you have the test file.

Share this post


Link to post
Share on other sites
rlx

correct but one would be able to see the structure of the file and the data format.

Share this post


Link to post
Share on other sites
Sambuddy

2- ZONE RÉPERTOIRE.xlsx

Please see attached excel file as an example of what the file looks like. You can see that the project numbers are scattered on different columns (C for one type and G for another) . Hopefully this would give you a better sense of what kind of excel I deal with. usually when I open the main excel file I have to go to find and type in the project number to then select and click on the project hyperlink to open the file directory.

 

Thank you,

 

Share this post


Link to post
Share on other sites
rlx
Posted (edited)

though its easy to read the (string) values from the excel file itself , I'm not entirely sure (yet) as how to correctly read the hyperlink value itself. Never used hyperlinks before in an excel sheet but when I look at your file (in excel) , select one and use edit hyperlink I see there are 2 values , the text you see (the label) and the hyperlink itself. The text value I retrieve in lisp is like "ZONE Z5 (GTA)\\Z5_University_Ave._165" while the text for the link itself (in excel) shows "ZONE%20Z5%20(GTA)\Z5_University_Ave._165". Not sure if this is important or not (link-wise). At first glance they are practically the same, in your case , but what if they are really different? Could post my scrappy code what I have so far but it wouldn't be very usefull so I think I first sleep a night on it because I make very long days and my sight is getting blurry 😵

Sambuddy.lsp

Edited by rlx

Share this post


Link to post
Share on other sites
pkenewell
Posted (edited)

EDIT: looks like rlx wrote you the code.

 

Here is a start on opening an excel file. While there is a way to open an excel in the background and search cells, It is time consuming and I don't have time to write it for you. Search for "Excel.application" in the forums and many examples will come up.

 

Give this a try. quickly written with no error checking. If you want a specific file instead of the file dialog, then just replace the (getfiled "...) with (findfile "<Your complete path to the file>")

 

(vl-load-com)

(defun c:OpenExcel (/ _GetShellOpen ext fn)

   (defun _GetShellOpen (ext / id)
     (if (setq id (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" ext)))
       (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" id "\\shell\\Open\\command"))
     )
   )

   (if (setq fn (getfiled "Select an Excel File to Open:" "" "xlsx;xls" 4))
      (progn
         (setq ext (vl-filename-extension fn))
         (startapp (_GetShellOpen ext) fn)
      )
   )
)

 

Edited by pkenewell

Share this post


Link to post
Share on other sites
rlx

opening the file is not the problem pkenewell , well , not mine anyway 😐

 

awell, I've had it for this day , have to get up in five hours so really need some sleep right now 🛌💤😴

Share this post


Link to post
Share on other sites
pkenewell
Just now, rlx said:

opening the file is not the problem pkenewell , well , not mine anyway 😐

 

awell, I've had it for this day , have to get up in five hours so really need some sleep right now 🛌💤😴

 

Yep - sorry I realized that after I read it all, and checked out your solution. Oh well - someone may find the simple code I wrote useful? 😃

Share this post


Link to post
Share on other sites
Tharwat
6 minutes ago, pkenewell said:

 

... Oh well - someone may find the simple code I wrote useful? 😃

You may need to replace the comma with semicolon. ;) 

Share this post


Link to post
Share on other sites
pkenewell
1 minute ago, Tharwat said:

You may need to replace the comma with semicolon. ;) 

Oops - Thanks. Corrected above.

Share this post


Link to post
Share on other sites
Tharwat
14 minutes ago, pkenewell said:

Oops - Thanks. Corrected above.

You replaced the asterisk and not the comma. 😉

Share this post


Link to post
Share on other sites
pkenewell
19 minutes ago, Tharwat said:

You replaced the asterisk and not the comma. 😉

Huh - weird - I replaced correctly in my working file. hopefully I have replaced it correctly above this time.

Share this post


Link to post
Share on other sites
rlx
Posted (edited)

ah, just found out by experimenting how to get the address from an excel range


  (setq ExcelHyperlinks (vl-catch-all-apply (function (lambda ()(vlax-get-property ExcelRange 'hyperlinks)))))

  (vlax-for url ExcelHyperlinks (setq ExcelAddress (vl-catch-all-apply (function (lambda ()(vlax-get-property url 'Address))))))

 

should be able to put this to good use (when I can find some spare time)

Edited by rlx

Share this post


Link to post
Share on other sites
rlx
Posted (edited)

see if this works for you

 

declared hyperlinks in column "B" and label in column "C"  in _Init subroutine and path hard coded in subroutine _SelectExcelWorkbookFilename as "F:\\SALLE A DESSIN\\2- ZONE RÉPERTOIRE_pop.xlsx"

 

ps haven't accounted yet for projectname in different columns but have to get something finished before I can go home so no time...

 

🐉

Sambuddy.lsp

Edited by rlx

Share this post


Link to post
Share on other sites
Sambuddy

Hey rlx,

Thank you for your hardwork in solving my problem.

Unfortunately I am faced with an error after executing your LISP: "No function Definition: VOID" is what I am getting and no action.

Could you please take a look again when you have some time?

and it is true that hyperlink resides on column B but Label, project numbers (that would be what I usually look for to find the project before clicking on the hyperlink for example in Excel) is scattered on all columns (C through P). would that constitute a big problem?

 

Thanks,

Sam

 

 

Share this post


Link to post
Share on other sites
rlx


(defun void (x) (or (not x) (= "" x) (and (eq 'STR (type x)) (not (vl-remove 32 (vl-string->list x))))))

 

column C - P probably isn't a problem (but a challenge)... biggest problem right now is time... pfff but I'll see what I can do

Share this post


Link to post
Share on other sites
Sambuddy

Hey pkenewell,

when I execute your LISP it come up with this error: "; error: too many arguments". I believe I cannot even open the excel from an Autocad command...

 

Share this post


Link to post
Share on other sites
Sambuddy

Thanks rlx,

the fact that you guys make wishes come true in LISP amazes me.

Thank you

Share this post


Link to post
Share on other sites

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...