Get data from excel to autolisp

Hi,

I have an excel sheet with a lot of number. See below. I have made an dcl and an lisp file. That I want to do is to enter a value, ex 11230 then I will get "HRPM30" in return. Someone knows how to do that?

 

1122011220SP71, Singel,2D, Flushing backventilHRPM0101123011230SP71, Singel,2D, Flushing extern pump, singel filterHRPM0301124011240SP71, Singel,2D, Flushing+broms1, singel filterHRPM0321125011250SP71, Singel,2D, Flushing+broms1o2, singel filterHRPM0341126011260SP71, Singel,2D, Broms1, singel filter1127011270SP71, Singel,2D, Broms1o2, singel filter1122111221SP71, Singel,2D, Flushing backventilHRPM0101123111231SP71, Singel,2D, Flushing extern pump, dubbel filterHRPM0311124111241SP71, Singel,2D, Flushing+broms1, duplex filterHRPM0331125111251SP71, Singel,2D, Flushing+broms1o2, duplex filterHRPM0351126111261SP71, Singel,2D, Broms1, duplex filter1127111271SP71, Singel,2D, Broms1o2, duplex filter1132011320SP71, Singel,3D, Flushing backventilHRPM0101133011330SP71, Singel,3D, Flushing extern pump, singel filterHRPM0371134011340SP71, Singel,3D, Flushing+broms1, singel filterHRPM0391135011350SP71, Singel,3D, Flushing+broms1o2, singel filterHRPM0411136011360SP71, Singel,3D, Broms1, singel filter1137011370SP71, Singel,3D, Broms1o2, singel filter1132111321SP71, Singel,3D, Flushing backventilHRPM0101133111331SP71, Singel,3D, Flushing extern pump, dubbel filterHRPM0381134111341SP71, Singel,3D, Flushing+broms1, duplex filterHRPM0401135111351SP71, Singel,3D, Flushing+broms1o2, duplex filterHRPM0421136111361SP71, Singel,3D, Broms1, duplex filter1137111371SP71, Singel,3D, Broms1o2, duplex filter

 
(defun saveVars()
(setq code(atoi(get_tile "code")))
)

(defun RCL (FilePath ShtNum CellAddress / xlApp cellData xlSheet CellRange xlBook)
(vl-load-com)
(setq xlApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible xlApp :vlax-false) 
(vlax-put-property xlApp 'DisplayAlerts :vlax-false)
(setq xlBook (vl-catch-all-apply
'vla-open
(list (vlax-get-property xlApp "WorkBooks") FilePath)
)
)
(setq xlSheet (vl-catch-all-apply
'vlax-get-property
(list (vlax-get-property xlBook "Sheets")
"Item"
ShtNum
)
)
)
(vlax-invoke-method xlSheet "Activate")
(setq CellRange (vlax-get-property
(vlax-get-property xlSheet 'Cells) "Range" CellAddress)
cellData (vlax-variant-value
(vlax-get-property CellRange 'Value2)
)
)
(vl-catch-all-apply
'vlax-invoke-method
(list xlBook "Close")
)
(vl-catch-all-apply
'vlax-invoke-method
(list xlApp "Quit")
)
(mapcar
(function
(lambda (x)
(vl-catch-all-apply
(function (lambda ()
(progn
(if (not (vlax-object-released-p x))
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
)
)
(list CellRange xlSheet xlBook xlApp)
)
(gc)
(gc)
cellData
)
(defun c:replen()
 ;;;--- Load the dcl file
 (setq dcl_id (load_dialog "kod"))
 ;;;--- Load the dialog definition if it is not already loaded
 (if (not (new_dialog "kod" dcl_id))
   (progn
    (alert "The SAMPLE4.DCL file could not be loaded!")
     (exit)
   )
 )
 ;;;--- If an action event occurs, do this function
 (action_tile "accept" "(setq ddiag 2)(saveVars)(done_dialog)")
 (action_tile "cancel" "(setq ddiag 1)(done_dialog)")


  ;;;--- Display the dialog box
 (start_dialog)
 ;;;--- Unload the dialog box
 (unload_dialog dcl_id)
 ;;;--- If the user pressed the Cancel button
 (if(= ddiag 1)
   (princ "\n Sample4 cancelled!")
 )
(if(= ddiag 2)
   (progn
     
;;(while (/= kod somevalue)
 ;;(setq p(+ p 1))
 ;;(setq q(strcat "A" (itoa p)))       
 (setq fname "T:\\DUE\\databas\\pumps.xlsx" ; <-- Excel file full path
    shnum 1; <-- sheet number
    addr "h2"); <-- cell address
    
;; (setq somevalue (RCL fname shnum addr))
 (setq rad2 (fix (RCL fname shnum (strcat "d" (itoa p)))))

)
))

 
kod : dialog { 
         label = "Get cell"; 
         : column { 
           : boxed_column { 
             : edit_box {
               key = "code";
               label = "Enter the code:";
               edit_width = 15;
               value = "";
               initial_focus = true;
                        }
}                         }
 : boxed_row {
             : button {
               key = "accept";
               label = " Okay ";
               is_default = true;
                      }
             : button {
               key = "cancel";
               label = " Cancel ";
               is_default = false;
               is_cancel = true;
             }

             
                    }} 

/Danpumps.xlsx

You want to do look up tables lots of different ways just a suggestion

1 line 1122011220SP71, Singel,2D, Flushing backventilHRPM0101123011230SP71

read all the lines into a list then pull out the 11230 into a 2nd list display the second list in your dcl it will return the item number in the list the "nth" value then just retrieve the "nth" line from the 1st list and you have what you want

Hi Bigal,

I think this was to complicated for me. But thank you Bigal for your answer

If you want to go down the excel direction you should be able to load a column of values into your DCL pick one, you can then find its cell address or row number this is the same as using NTH but then pull value of this row but with new column. I dont do a lot of excel-cad your code refers to cell address.