Jump to content

Lisp to open an excel file and search content


Sambuddy

Recommended Posts

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

Link to comment
Share on other sites

  • Replies 51
  • Created
  • Last Reply

Top Posters In This Topic

  • rlx

    23

  • Sambuddy

    19

  • pkenewell

    7

  • Tharwat

    2

Top Posters In This Topic

Posted Images

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.

Link to comment
Share on other sites

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 )

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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,

 

Edited by Sambuddy
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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 🛌💤😴

Link to comment
Share on other sites

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? 😃

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites


(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

Link to comment
Share on other sites

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...

 

Link to comment
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
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  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...