Lisp to open an excel file and search content

[pkenewell]

57 minutes ago, Sambuddy said:

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

 

Hmm - It is working fine for me. Here try downloading the file instead.

 

**EDIT: I found a bug with (startapp) that will not open the file correctly if the path has spaces in it. just had to add enclosing quotes to the path and file string.

 

OpenExcellFile.lsp

Edited October 9, 2019 by pkenewell

[rlx]

4 hours ago, Sambuddy said:

Thanks rlx,

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

Thank you

 

you're welcome.

 

Now for the last (?) challenge. Hope to have some time tomorrow between work because in the evening and in the weekend also have some things to do. Anyways , the way I would try to tackle the C - P issue , is to first read them all to a list , kick out the empty ones and then find the project name in the left-overs. If I see correctly , there can also be other texts in the cells in the same row like a description? So to find the project number I would apply a wcmatch filter. Am I correct to assume all project names consists of 1 letter and 4 numbers?

[Sambuddy]

Thanks rlx,

You are correct for the majority but not entirety. unfortunately, some project number have weird project numbers, example: PQ1231 or CN3420 or SIM10.

Would you LISP search the excel file, then find the project number and associated hyperlink, then open the folder containing the project? because that is what I would have wished. Also if this challange would not satisfy your thirst of computers and programming, how about searching through two excel files? hah! both are on the same path but named differently: example region_a and region_b.

 

I thank you again for partaking this LISP. 

[rlx]

As long as I can find a way to distinguish a project name from the rest its fine by me. You can have for example multiple filter masks at once so "$####,$$####,$$$##" would catch all projectnames I've seen so far (meaning 1char+4num,2char+4num,3char+2num. What could work too is looking at the string length like if you could say that descriptions are allways longer than 10 characters and projectnames allways shorter. Something like that you know. Well :

[rlx]

This one (hopefully) takes care of the 'C to P' problem. If you want multiple files for now you could enable the getfiled line in _SelectExcelWorkbookFilename (enable line 99 by removing the ; in front of it and disable line 100 by placing a ; at the beginning of the line, and then select the xlsx file you're after. I guess it would be possible to provide the app with a folder rather than a filename. Just think its better to attack this problem at the source rather than cleaning up afterwards. But I understand this may not be an option due to company policy or historical garbage.

Sambuddy.lsp

[rlx]

This version gets all xlsx files from a list of folders (see around line 78 in the code)

Sambuddy2.lsp

[Sambuddy]

Good morning rlx,

 

unfortunately I am getting this error: "No data found in excel file" when I execute the LISP. Maybe I am to blame for not giving accurate information. Please take a look at this sanpshot one last time when you have time.

the two excel files address are: "T:\SALLE A DESSIN\2- ZONE RÉPERTOIRE_TORONTO.xlsx" and "T:\SALLE A DESSIN\1- ZONE RÉPERTOIRE_BOUCHERVILLE.xlsx"

Thank you again

 

 

 

 

 

 

 

 

Edited November 18, 2019 by Sambuddy

[Sambuddy]

Hi rlx,

again I receive this error this time around:

"Excel opened
bad argument type: stringp 3600.0
Excel closed"

The two excel files are admin password protected so I have to click read only - would this constitute an issue?

 

Thanks

[rlx]

My first guess would be the password gets in the way. I have made 2 changes but I can't tell if they will work

 

first in line 21-23 skipped the folder search and hard coded the 2 files in case the problem was there

(defun _Start ( / the-x-files)
  ;(setq the-x-files (_SelectExcelWorkbookFolder))
  (setq the-x-files
    (list "T:\\SALLE A DESSIN\\2- ZONE RÉPERTOIRE_TORONTO.xlsx" "T:\\SALLE A DESSIN\\1- ZONE RÉPERTOIRE_BOUCHERVILLE.xlsx"))
  (foreach f the-x-files (Read_ExcelFile f))

...

 

and the second thing is totally untested on a protected workbook but just give it a go. Else open the protected one and paste its contents in a new one as a dirty solution. Anyways changes I made are in lines 96 -101

(defun _OpenWorkbook ( fn / )
  (cond
    ((or (void fn) (not (eq (type fn) 'STR)))
     (alert (strcat "Invalid filename for workbook" (vl-princ-to-string fn))))
    ((not (findfile ExcelWorkbookFilename))
     (alert (strcat "Unable to locate excel workbook :\n" fn)))
    ((vl-catch-all-error-p (setq *excel-app* (vl-catch-all-apply 'vlax-get-or-create-object '("excel.application"))))
     (alert (strcat "\nError intializing Excel application :\n" (vl-catch-all-error-message *excel-app*))))
    ((vl-catch-all-error-p (setq *excel-workbooks* (vl-catch-all-apply 'vlax-get-property (list *excel-app* 'workbooks))))
     (alert (strcat "Error intializing Excel workbooks :\n" (vl-catch-all-error-message *excel-workbooks*))))
    ;((vl-catch-all-error-p (setq *excel-openworkbook* (vl-catch-all-apply 'vlax-invoke-method (list *excel-workbooks* 'open fn))))
    ; (alert (strcat "Error opening Excel workbook collection :\n" (vl-catch-all-error-message *excel-openworkbook*))))
   
    ((vl-catch-all-error-p (setq *excel-openworkbook* (vl-catch-all-apply 'vlax-invoke-method
       (list wb-collection "open" fn 1 1 1 (vlax-make-variant "password here")))))  ;;;;;;<------------------------------------------------------ password
     (alert (strcat "Error opening Excel workbook collection :\n" (vl-catch-all-error-message *excel-openworkbook*))))
                                
    ((vl-catch-all-error-p (setq *excel-activesheet* (vl-catch-all-apply 'vlax-get-property (list *excel-app* 'activesheet))))
     (alert (strcat "Error opening Excel active workbook :\n" (vl-catch-all-error-message *excel-activesheet*))))
    ((vl-catch-all-error-p (setq err (vl-catch-all-apply 'vlax-invoke-method (list *excel-activesheet* 'activate))))
     (alert (strcat "Unable to activate active sheet :\n" (vl-catch-all-error-message err))))
    (t (vla-put-visible *excel-app* :vlax-true)(princ "\nExcel opened"))
  )
)

 

with the addition that you have to fill in the password on line 100

 

Little else I can do when not on location... only thing I could (should) do is to test on my own protected excel file which I haven't done.

 

 

Sambuddy2.lsp

Edited October 10, 2019 by rlx

[Sambuddy]

Good morning rlx,

 

Still not working! but I thank you for your help and time you spent on this.

 

I am receiving : "bad argument type: numberp:" nil: and this dialog box on my screen:

I do understand this has been a painful process.

Thanks

[rlx]

Yeah that's probably the problem with not being able to test it on the real thing... will try a protected excel file later when if I can find the time. First have to babysit a little terrorist because here mother's had an operation , then she has also something to do at her school and after that we have to drive her to her mother in hospital so no time right now. And later , in the weekend,  we have 2 other little terro's to entertain ... oh what terrible things I must have done in my previous life to deserve this haha

 

Maybe later...

[Sambuddy]

Thanks anyways!

[Sambuddy]

Good afternoon pkenewell,

 

I am replying to your latest post where you said you fixed the problem if the path has spaces. Still no result. 

 

(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"))
     )
   )

      (progn
         (setq ext (vl-filename-extension fn))
         (startapp (_GetShellOpen ext) (strcat "\"" fn "\""))
      )
   )
)

 

the line in red is the one I replaced and it gives me this message:

 

as you can see it opens up this dialog box on "My Documents". Your other option of "getfile" returns "nil" on the command line. 

Edited November 18, 2019 by Sambuddy

[rlx]

think made a little typo in line 100

 

((vl-catch-all-error-p (setq *excel-openworkbook* (vl-catch-all-apply 'vlax-invoke-method
       (list *excel-workbooks* "open" fn 1 1 1 (vlax-make-variant "rlx"))))) ;<- and replace rlx with your own password

 

it said

(list wb-collection "open"

and should be

(list *excel-workbooks* "open

Edited October 10, 2019 by rlx

[rlx]

In lisp you either use \\ or / but not \ because a single \ is an escape character. The \ is usually followed by a letter to give it a special meaning like \n for new line or \t for tab etc.

 

from the help function of AutoCad : (getfiled title default ext flags)

 

so what's wrong with (setq fn (getfiled "T:\SALLE A DESSIN\2- ZONE RÉPERTOIRE_TORONTO.xlsx" "" "xlsx;xls" 4))?

 

well :

 

1) : the \  sould be \\ or /

2) : the 1st parameter of getfiled is a title , not a filename

3) : if you want to select a existing file maybe better use 0 as flag?

 

so to wrap it up :

(setq fn (getfiled "Select workbook" "T:\\SALLE A DESSIN\\2- ZONE RÉPERTOIRE_TORONTO.xlsx" "xlsx;xls" 0))

or

(setq fn (getfiled "Select workbook" "T:/SALLE A DESSIN/2- ZONE RÉPERTOIRE_TORONTO.xlsx" "xlsx;xls" 0))

or just

(setq fn (getfiled "Select workbook" "T:/SALLE A DESSIN/" "xlsx;xls" 0))

 

Edited October 10, 2019 by rlx

[pkenewell]

2 minutes ago, rlx said:

In lisp you either use \\ or / but not \ because a single \ is an escape character. The \ is usually followed by a letter to give it a special meaning like \n for new line or \t for tab etc.

 

from the help function of AutoCad : (getfiled title default ext flags)

 

so what's wrong with (setq fn (getfiled "T:\SALLE A DESSIN\2- ZONE RÉPERTOIRE_TORONTO.xlsx" "" "xlsx;xls" 4))?

 

well :

 

1) : the \  sould be \\ or /

2) : the 1st parameter of getfiled is a title , not a filename

3) : if you want to select a existing file maybe better use 0 as flag?

 

so to wrap it up :

(setq fn (getfiled "Select workbook" "T:\\SALLE A DESSIN\\2- ZONE RÉPERTOIRE_TORONTO.xlsx" "xlsx;xls" 0))

or

(setq fn (getfiled "Select workbook" "T:/SALLE A DESSIN/2- ZONE RÉPERTOIRE_TORONTO.xlsx" "xlsx;xls" 0))

or just

(setq fn (getfiled "Select workbook" "T:/SALLE A DESSIN/" "xlsx;xls" 0))

 

 

Yes - What rlx said...

 

As for the bitcode 4 in (getfiled), I wanted to be able to override the Excel extension using "*" In the filename box - so I could open CSV or other files. I tested this and it works great if you need to open other extensions in Excel. Of course you could also just add the other extensions possible into the (getfiled) argument, such as "xls;xlm;xlsx;xlmx;csv" and others as applicable.

[Sambuddy]

Thank you again for looking at this:

with the correct path, now I am getting inside the correct directory but I have to click open, even though the excel file is selected by default (through your LISP).

 

the message says: Sorry! We did not find %1.xlsx. Perhapsit it was displaced, renamed or removed? (But if you hit enter it does open the excel file)

 

 

By hitting enter key 3 times, I can get it and bypass all these 3 windows - can this be keyed in into the lisp?

Thanks

 

[pkenewell]

7 hours ago, Sambuddy said:

Thank you again for looking at this:

with the correct path, now I am getting inside the correct directory but I have to click open, even though the excel file is selected by default (through your LISP).

 

the message says: Sorry! We did not find %1.xlsx. Perhapsit it was displaced, renamed or removed? (But if you hit enter it does open the excel file)

 

 

By hitting enter key 3 times, I can get it and bypass all these 3 windows - can this be keyed in into the lisp?

Thanks

 

 

Sambuddy,

 

The program is just a generic for opening any excel file in the folder you specify. I always meant it to be an example to get you started - not a out of the box program for you. If you want it to just open a specific file, just replace:

(getfiled "Select an Excel File to Open:" "" "xlsx;xls" 4)

with:

(findfile "T:\\SALLE A DESSIN\\2- ZONE RÉPERTOIRE_TORONTO.xlsx")

 

Sorry - I have no idea why the "%1.xlsx" is happening to you. I works on all the files I tested. As rlx points out, perhaps it is a password issue.

 

NOTE: this website is supposed to be for - programmers helping other programmers learning AutoLISP. not free LISP programs for anyone who demands them. If you just want a made to order program instead of learning AutoLISP, perhaps you should be contacting someone to write it for you and compensating them for their time.

[Sambuddy]

Good morning rlx,

 

Any hope with that VLA-OBJECT Nil box on the lisp?

 

Thanks

[rlx]

did you change (list wb-collection "open" .... to (list *exel-workbooks* "open" ...as I suggested last thursday? And corrected all the filenames in the lispfile and replaced all the \ with either / or \\?

 

Have inserted the file with the corrections in case you haven't

 

Sambuddy2.lsp