Lisp to open an excel file and search content

[Sambuddy]

Thanks rlx,

I have a bad argument - I will try to see if it is because of the cell number issue specified on the lisp. It does open the excel in the background and attempt to find columns then it fails. I will see what i can do but Gorgeous LISP so far - I am very impressed what you can do - You the MAN! 

[rlx]

else send me your excel file by private message so I can see if it has something to do with the file itself. It doesn't matter I wont be able to open the links because I'm only interested if the data itself is read correctly.

[Sambuddy]

How do I send a private message rlx?

 

it says "You are only allowed to send 0 messages per day. Please try again later." when I want to do that. Can you send me a private message and I reply to that please?

[rlx]

Had a look at your files, well , one of them anyways, and it doesn't make me very happy. Lots of different project name formats, lines with more than one project name, lines with nothing on it etc. etc. And that was just one file haha. Nevertheless , think I will have a go at it but its a lot of effort for basically ctrl-find in excel. Not sure I can guarantee the end result will be idiot proof or when I have the next routine ready for testing but then again patience is a virtue ne c'est pas

[rlx]

attempt no.3

Search can take some time but that's because the data is so scattered captain chaos

 

; RLX - 10-10-2019 - last update 16-oct-2019
(vl-load-com)

(defun c:Sam ( / *AcApp* *ActDoc* OldErr ExcelWorkbookFilename cell-values hyperlink-lst label-lst
                 aso-list aso *excel-app* *excel-workbooks* *excel-openworkbook* *excel-activesheet*
                 *Excel-MaxRow* *Excel-MaxCol* Hyperlink-Column Label-Column) (_Init) (_Start) (_Exit) (princ))

(defun _Init ()(setq *error* _Err *AcApp* (vlax-get-acad-object) *ActDoc* (vla-get-activedocument *AcApp*)))
(defun prl (lst)(terpri)(mapcar '(lambda(x)(princ "\n")(princ x)) lst)(terpri)(princ))
(defun _Err ($s) (term_dialog) (princ $s) (_Exit) (setq *error* OldErr) (princ))
(defun _Exit ()  (_CloseExcel) (setq *error* OldErr) (princ))
(defun itol (i)(if (< i 27)(chr (+ 64 i))(strcat (itol (/ (1- i) 26))(itol (1+ (rem (1- i) 26))))))
(defun void (x) (or (null x) (and (eq 'STR (type x)) (eq "" (vl-string-trim " \t\r\n" x)))))

(defun _Start ( / the-x-files search-text inp result end-result rtn)
  (if (and (setq search-text (getstring "\nEnter number to search : "))
           (not (void search-text)))(setq search-text (strcase search-text))(setq search-text nil))
  (if search-text
    (progn
      (princ "\n(1) - search 1-Zone, (2) - search 2-Zone, (3) - both <3> : ")
      (setq inp (vl-catch-all-apply 'grread (list nil 8 0)))
      (cond
        ((vl-catch-all-error-p inp)(princ "\nSelection cancelled")(setq result nil))
        ((equal inp '(2 49))(setq result (Read_1_Zone)))
        ((equal inp '(2 50))(setq result (Read_2_Zone)))
        ; 3, enter, space or R-mouse
        ((or (equal inp '(2 51)) (equal inp '(2 13)) (equal inp '(2 32)) (equal (car inp) 25))
         (setq result (append (Read_1_Zone) (Read_2_Zone))))
        (t (princ "\nSearch aborted")(setq result nil))
      )
      (princ "\n\nScanning file(s) for search text......")
      (setq end-result (find_search_text search-text result))
      (cond
        ((void end-result)(alert "\nSearch text not found"))
        ((> (length end-result) 1) (princ "\nMultiple results found")
         (if (setq rtn (cfl end-result))(startapp "explorer" rtn) (princ "\nHyperlink selection cancelled")))
        (t (startapp "explorer" (car end-result)))
      )
    )
    (princ "\nSearch aborted")
  )
  (princ)
)

(defun Read_1_Zone ( / f r)
  (if (setq f (findfile "T:\\SALLE A DESSIN\\1- ZONE RÉPERTOIRE_BOUCHERVILLE.xlsx"))
    (setq r (Read_ExcelFile f "D" '("F" "H" "J" "L" "N")))(progn (princ "\n1- ZONE not found")(setq r nil))) r)

(defun Read_2_Zone ( / f r)
  (if (setq f (findfile "T:\\SALLE A DESSIN\\2- ZONE RÉPERTOIRE_TORONTO.xlsx"))
    (setq r (Read_ExcelFile f "B" '("C" "E" "G" "I" "K")))(progn (princ "\n2- ZONE not found")(setq r nil))) r)

(defun find_search_text (s l / r) (foreach x l (if (vl-string-search s (car x)) (setq r (cons (cadr x) r)))) r)

; $fn = xlsl filename, $hlc = hyperlink column, %col-lst = list of column names
; in this case I use the first and last column letter from %col-lst i.e. '("F" "H" "J" "L" "N")
(defun Read_ExcelFile ($fn $hlc %col-lst / *Excel-MaxRow* *Excel-MaxCol* *range* i cell-values hyperlink result-list)
  (if (and $fn (setq ExcelWorkbookFilename (findfile $fn))) (_OpenWorkbook ExcelWorkbookFilename))
  (if (and *excel-app* *excel-openworkbook* *excel-activesheet*)
    (progn
      (prompt (strcat "\nReading from : " $fn))
      (setq *range* (vlax-get-property  *excel-activesheet* 'UsedRange))
      (prompt (strcat "\nNumber of rows in sheet : "
        (vl-princ-to-string (setq *Excel-MaxRow* (vlax-get-property (vlax-get-property *range* "Rows") "Count")))))
      (prompt (strcat "\nNumber of columns in sheet : "
        (vl-princ-to-string (setq *Excel-MaxCol* (vlax-get-property (vlax-get-property *range* "Columns") "Count")))))
    )
  )
  ; get all the cell values
  ; if first row are labels for example , first data-row (i) = 2 , else i = 1
  (setq i 1)
  (if *Excel-MaxRow*
    ;(1- *Excel-MaxRow*) if data starts on 2nd row
    (repeat *Excel-MaxRow*
      (and
        ; check if hyperlink is found, if not, no need to look any further
        (setq hyperlink (Excel_GetCellHyperlinks (strcat $hlc (itoa i) ":" $hlc (itoa i))))
        ; read entire range from Label-Column (car Label-Column+row) : (last Label-Column+row)
        ; other possibility would be read each column and convert with strcat to one string
        (vl-consp (setq cell-values (Excel_GetCellValue
           (strcat (car %col-lst) (itoa i) ":" (last %col-lst) (itoa i)))))
        (setq result-list (cons (cons (apply 'strcat (mapcar 'strcase cell-values)) hyperlink) result-list))
      )
      (setq i (1+ i))
    )
  )
  result-list
)

(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 1 1 1 (vlax-make-variant "rlx")))))
     (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-false)(princ "\nExcel opened"))
  )
)

(defun _CloseExcel ()
  (if *excel-app* (progn (vl-catch-all-apply 'vla-quit (list *excel-app*))(_ReleaseExcel)(princ "\nExcel closed"))))

(defun _ReleaseExcel ()
  (mapcar '(lambda(x) (if x (vl-catch-all-apply 'vlax-release-object (list x)))(setq x nil))
           (list *excel-activesheet* *excel-openworkbook* *excel-workbooks* *excel-app*)) (gc))

; assumes excel is up & running
(defun Excel_GetCellValue ( %range / ExcelRange range ExcelVariant ExcelValue etype)
  (if *excel-app*
    (progn
      ; change single cell range to multiculti cell i.e. "A1" -> "A1:A1"
      (if (not (wcmatch %range "*:*"))(setq range (strcat %range ":" %range))(setq range %range))
      (setq ExcelRange (vlax-get-property *excel-app* "Range" range))
      (setq ExcelVariant (vl-catch-all-apply (function (lambda ()(vlax-get-property ExcelRange 'Value)))))
      (setq ExcelValue (vl-catch-all-apply (function (lambda ()(vlax-variant-value ExcelVariant)))))
      (if ExcelValue
 (setq etype (type ExcelValue)
       ExcelValue
               (cond ((= etype 'INT) (itoa ExcelValue))
                     ((= etype 'REAL) (rtos ExcelValue 2 2))
                     ((= etype 'STR) (vl-string-trim " " ExcelValue))
                     ((= etype 'safearray) (vl-remove "nil" (mapcar 'vl-princ-to-string (mapcar
                      '(lambda (s) (vlax-variant-value s))(car (vlax-safearray->list excelvalue))))))
                     ((/= etype 'STR) "")))
        (setq ExcelValue "")
      )
    )
  )
  ; change all nil's to "Empty"
  (if (listp ExcelValue) (setq ExcelValue (subst "Empty" nil ExcelValue)))
  ExcelValue
)


(defun Excel_GetCellHyperlinks ( %range / ExcelRange range ExcelHyperlinks hyperlist)
  (if *excel-app*
    (progn
      ; change single cell range to multiculti cell i.e. "A1" -> "A1:A1"
      (if (not (wcmatch %range "*:*"))(setq range (strcat %range ":" %range))(setq range %range))
      (setq ExcelRange (vlax-get-property *excel-app* "Range" range))
      (setq ExcelHyperlinks (vl-catch-all-apply (function (lambda ()(vlax-get-property ExcelRange 'hyperlinks)))))
      (vlax-for url ExcelHyperlinks
        (if (not (vl-catch-all-error-p (setq ExcelAddress
           (vl-catch-all-apply (function (lambda () (vlax-get-property url 'Address)))))))
          (setq hyperlist (cons ExcelAddress hyperlist))(setq hyperlist (cons "hyper-error" hyperlist))
        )
      )
    )
  )
  hyperlist
)


; choose from list (cfl '("1""2""3"))
(defun cfl (l / f p d r)
  (and (setq p (open (setq f (vl-filename-mktemp ".dcl")) "w"))
       (princ "cfl:dialog{label=\"Choose\";:list_box{width=80;key=\"lb\";}ok_cancel;}" p)
       (not (setq p (close p)))(< 0 (setq d (load_dialog f)))(new_dialog "cfl" d)
       (progn
         (start_list "lb")(mapcar 'add_list l)(end_list)
         (action_tile "lb" "(setq r (nth (atoi $value) l))(done_dialog 1)")
         (action_tile "accept" "(setq r (get_tile \"lb\"))(done_dialog 1)")
         (action_tile "cancel" "(setq r nil)(done_dialog 0)")
         (start_dialog)(unload_dialog d)(vl-file-delete f)
       )
  )
  (cond ((= r "") nil)(r r)(t nil))
)

(princ (strcat "\n\nSearch excel file for string and open hyperlink"
   "\nWritten for SamBuddy by RLX 16-Oct-2019 - type sam or (c:sam) after loading"))
(princ)

 

 

if it works and others may find it useful I can make a more generic version with a test xlsx example  cause now the files are hard coded so little use for others (unless they know how to lisp of course)

 

 

 

[Sambuddy]

Thanks a lot rlx,

The search appears on the command line, it even refers you to which zone to search or both (fantastic), but still the search results in returning "My documents" each time I search - that is the only folder to open each time I execute the command.

 

You have out-done the LISP programming. Please do not take any more time on my inquiry. I am sure this lisp would be great for others to share and use.

 

Thanks again 

[rlx]

if you still get those dialogs as above maybe you are not loading the right version. Make sure you move or delete all previous versions and make sure the last version I've posted is the only one loaded because the dialogs show in your post before this one shouldn't be happening. Lisp always runs the last loaded routine so maybe close autocad and start fresh and then paste my last code in the editor and load. Here at my side things seem to be working as it should. But maybe here at home its a different story, haven't tried that yet but first have to find a way to get rid of a little terrorist and have something to eat. Wish I could write an app for that (defun c:lose_terro_eat_wife ()(princ "\nbyebye"))

 

[Sambuddy]

hah!

I am sorry for those windows! I thought I had deleted them before sending my last email. No! the windows do not show up but the search always return "My Document Folder".

 

Sorry for the messed up message.

[rlx]

strange... I wonder if your drive mappings are ok? The last line in the subfunction _Start is (princ). What are you getting if you change that to (princ end-result)?

[Sambuddy]

Same -  Like I said you have done more than enough! maybe in the future I will ask you again but it is a great stage as it stands.

 

Thank you very much for your time and effort rlx.

[rlx]

terro didn't leave but now she did - although she said I'll be back (after dinner grrr) - anyways quickly made a diagnostic version as a 'final' effort. It will start automatically after loading. Type  as search text xxxx and then enter 2 to select the zone 2 excel file. It should find 2 links. It also generates a text file which should pop up and looks like example below. The fact every time your document folder is selected means it cannot locate the folder so there is something wrong with you drive mapings or network folders.

 

Search text = xxxx
Hyperlink found = ......

Data found in excel :

(xxxxx \\......)
etc

 

hope this helps...

 

ah ... food..... see ya...

 

 

 

Sambuddy4.lsp

Edited October 16, 2019 by rlx

[rlx]

attempt 999 :

 

; RLX - 10-10-2019 - last update 25-oct-2019
(defun c:Sam ( / *AcApp* *ActDoc* OldErr cell-values *excel-app* *excel-workbooks* *excel-openworkbook*
                 *excel-activesheet* *Excel-MaxRow* *Excel-MaxCol*)(_Init)(_Start)(_Exit)(princ))
(defun _Init ()(setq *error* _Err *AcApp* (vlax-get-acad-object) *ActDoc* (vla-get-activedocument *AcApp*)))
(defun _Start ( / search-text inp result end-result rtn my-choise)
  (if (and (setq search-text (getstring "\nEnter number to search : "))(not (void search-text)))
    (setq search-text (strcase search-text))(setq search-text nil))
  (if search-text
    (progn
      (princ "\n(1) - search 1-Zone, (2) - search 2-Zone, (3) - both <3> : ")
      (setq inp (vl-catch-all-apply 'grread (list nil 8 0)))
      (textscr)
      (cond
        ((vl-catch-all-error-p inp)(princ "\nSelection cancelled\n")(setq result nil))
        ((equal inp '(2 49))(princ " 1 \n")(setq result (Read_1_Zone)))
        ((equal inp '(2 50))(princ " 2 \n")(setq result (Read_2_Zone)))
        ((or (equal inp '(2 51)) (equal inp '(2 13)) (equal inp '(2 32)) (equal (car inp) 25))
         (princ " both \n")(setq result (append (Read_1_Zone) (Read_2_Zone))))
        (t (princ "\nSearch aborted\n")(setq result nil))
      )
      (setq end-result (find_search_text search-text result))
      (cond
        ((void end-result)(alert "\nSearch text not found\n"))
        ((> (length end-result) 1) (princ "\nMultiple results found\n")
         (if (and (setq rtn (cfl end-result)) (vl-file-directory-p rtn))
           (progn (princ (strcat "\nOpening folder : " rtn))(startapp "explorer" rtn))
           (princ (strcat "\nFolder not found : " (vl-princ-to-string rtn) "\n"))))
        ((not (vl-file-directory-p (car end-result)))
         (princ (strcat "\nUnable to find folder : " (car end-result))))
        ((vl-file-directory-p (car end-result))
         (princ (strcat "\nOpening folder : " (car end-result)))(startapp "explorer" (car end-result)))
        (t (princ "\nHyperlink selection cancelled or unable to find folder\n"))
      )
    )
    (princ "\nSearch aborted\n")
  )
  (princ)
)

(defun Read_1_Zone ( / f r)
  (if (setq f (findfile "T:\\SALLE A DESSIN\\1- ZONE RÉPERTOIRE_BOUCHERVILLE.xlsx"))
    (setq r (Read_ExcelFile f "D" '("F" "H" "J" "L" "N")))(progn (princ "\n1- ZONE not found")(setq r nil))) r)

(defun Read_2_Zone ( / f r)
  (if (setq f (findfile "T:\\SALLE A DESSIN\\2- ZONE RÉPERTOIRE_TORONTO.xlsx"))
    (setq r (Read_ExcelFile f "B" '("C" "E" "G" "I" "K")))(progn (princ "\n2- ZONE not found")(setq r nil))) r)

(defun find_search_text (s l / r) (foreach x l (if (vl-string-search s (car x)) (setq r (cons (cadr x) r)))) r)

; $fn = xlsl filename, $hlc = hyperlink column, %col-lst = list of column names
; in this case I use the first and last column letter from %col-lst i.e. '("F" "H" "J" "L" "N")
(defun Read_ExcelFile ($fn $hlc %col-lst / *Excel-MaxRow* *Excel-MaxCol* *range* i cell-values hyperlink result-list)
  (if (and $fn (setq $fn (findfile $fn))) (_OpenWorkbook $fn))
  (if (and *excel-app* *excel-openworkbook* *excel-activesheet*)
    (progn
      (prompt (strcat "\nReading from : " $fn "\n"))
      (setq *range* (vlax-get-property  *excel-activesheet* 'UsedRange))
      (prompt (strcat "\nNumber of rows in sheet : " (vl-princ-to-string (setq *Excel-MaxRow* (vlax-get-property
        (vlax-get-property *range* "Rows") "Count"))) " , Number of columns in sheet : " (vl-princ-to-string
          (setq *Excel-MaxCol* (vlax-get-property (vlax-get-property *range* "Columns") "Count"))) "\nScanning...\n"))
    )
  )
  ; get all the cell values
  ; if first row are labels for example , first data-row (i) = 2 , else i = 1
  (setq i 1)
  (if *Excel-MaxRow*
    ;(1- *Excel-MaxRow*) if data starts on 2nd row
    (repeat *Excel-MaxRow*
      (and
        ; check if hyperlink is found, if not, no need to look any further
        (setq hyperlink (Excel_GetCellHyperlinks (strcat $hlc (itoa i) ":" $hlc (itoa i))))
        ; read entire range from Label-Column (car Label-Column+row) : (last Label-Column+row)
        ; other possibility would be read each column and convert with strcat to one string
        (vl-consp (setq cell-values (Excel_GetCellValue
           (strcat (car %col-lst) (itoa i) ":" (last %col-lst) (itoa i)))))
        (setq result-list (cons (cons (apply 'strcat (mapcar 'strcase cell-values)) hyperlink) result-list))
      )
      (setq i (1+ i))
    )
  )
  result-list
)

(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 fn))(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 1 1 1 (vlax-make-variant "rlx")))))
     (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-false)(princ "\nExcel opened\n"))
  )
)

(defun _CloseExcel ()
  (if *excel-app* (progn (vl-catch-all-apply 'vla-quit (list *excel-app*))(_ReleaseExcel)(princ "\nExcel closed\n"))))

(defun _ReleaseExcel ()
  (mapcar '(lambda(x) (if x (vl-catch-all-apply 'vlax-release-object (list x)))(setq x nil))
           (list *excel-activesheet* *excel-openworkbook* *excel-workbooks* *excel-app*)) (gc))

; assumes excel is up & running
(defun Excel_GetCellValue ( %range / ExcelRange range ExcelVariant ExcelValue etype)
  (if *excel-app*
    (progn
      ; change single cell range to multiculti cell i.e. "A1" -> "A1:A1"
      (if (not (wcmatch %range "*:*"))(setq range (strcat %range ":" %range))(setq range %range))
      (setq ExcelRange (vlax-get-property *excel-app* "Range" range))
      (setq ExcelVariant (vl-catch-all-apply (function (lambda ()(vlax-get-property ExcelRange 'Value)))))
      (setq ExcelValue (vl-catch-all-apply (function (lambda ()(vlax-variant-value ExcelVariant)))))
      (if ExcelValue
 (setq etype (type ExcelValue)
       ExcelValue
               (cond ((= etype 'INT) (itoa ExcelValue))
                     ((= etype 'REAL) (rtos ExcelValue 2 2))
                     ((= etype 'STR) (vl-string-trim " " ExcelValue))
                     ((= etype 'safearray) (vl-remove "nil" (mapcar 'vl-princ-to-string (mapcar
                      '(lambda (s) (vlax-variant-value s))(car (vlax-safearray->list excelvalue))))))
                     ((/= etype 'STR) "")))
        (setq ExcelValue "")
      )
    )
  )
  ; change all nil's to "Empty"
  (if (listp ExcelValue) (setq ExcelValue (subst "Empty" nil ExcelValue)))
  ExcelValue
)

(defun Excel_GetCellHyperlinks ( %range / ExcelRange range ExcelHyperlinks hyperlist)
  (if *excel-app*
    (progn
      ; change single cell range to multiculti cell i.e. "A1" -> "A1:A1"
      (if (not (wcmatch %range "*:*"))(setq range (strcat %range ":" %range))(setq range %range))
      (setq ExcelRange (vlax-get-property *excel-app* "Range" range))
      (setq ExcelHyperlinks (vl-catch-all-apply (function (lambda ()(vlax-get-property ExcelRange 'hyperlinks)))))
      (vlax-for url ExcelHyperlinks
        (if (not (vl-catch-all-error-p (setq ExcelAddress
           (vl-catch-all-apply (function (lambda () (vlax-get-property url 'Address)))))))
          (setq hyperlist (cons ExcelAddress hyperlist))(setq hyperlist (cons "hyper-error" hyperlist))
        )
      )
    )
  )
  hyperlist
)

(defun cfl (l / f p d r)
  (and (setq p (open (setq f (vl-filename-mktemp ".dcl")) "w"))
       (princ "cfl:dialog{label=\"Choose\";:list_box{width=80;key=\"lb\";}ok_cancel;}" p)
       (not (setq p (close p)))(< 0 (setq d (load_dialog f)))(new_dialog "cfl" d)
       (progn
         (start_list "lb")(mapcar 'add_list l)(end_list)
          (action_tile "lb" "(setq r (nth (atoi $value) l))(done_dialog 1)")
           (action_tile "accept" "(setq r (get_tile \"lb\"))(done_dialog 1)")
            (action_tile "cancel" "(setq r nil)(done_dialog 0)")
             (start_dialog)(unload_dialog d)(vl-file-delete f)))
  (cond ((= r "") nil)(r r)(t nil))
)

(defun _Err ($s) (term_dialog) (princ $s) (_Exit) (setq *error* OldErr) (princ))
(defun _Exit ()  (_CloseExcel) (setq *error* OldErr) (princ))
(defun void (x) (or (null x) (and (eq 'STR (type x)) (eq "" (vl-string-trim " \t\r\n" x)))))

(princ (strcat "\n\nSearch excel file for string and open hyperlink"
               "\nWritten for SamBuddy by RLX 25-Oct-2019 - type sam or (c:sam) after loading"))
(vl-load-com)
(princ)