Thanks for that. I really appreciate the help, but it's not finding my test string and the clean up portion of it isn't working correctly. It's leaving a lock file that I can't delete and remains until I reboot.
I'm getting close with this:
; 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 "G:\\JEI CAD Support\\AutoCad Support\\Development\\DataLink\\Excel_Test_001.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 "G:\\JEI CAD Support\\AutoCad Support\\Development\\DataLink\\Excel_Test_001.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))
(princ cell-values)
)
(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)
As it is, I'm having trouble deciphering the "Zones" and the search starts at column C, which I'm trying to sort out. This was created to search for hyperlinks, but the search functionality is there, just gotta steer it in my direction. Here's the portion I'm concentrating on:
; 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))
(princ cell-values)
)
(setq i (1+ i))
)
)
AutoCad 2018 (full)
Win 11 Pro
