Topic: query in excel like in access with sql statement?

[Amsterdammed]

Hello there,

 I have functions to retrieve the value of cells by calling them by their address, but can i do a query like in access and search for a value in a cell and look then up the address of the cell or even better other values in the same row, like

Code: [Select]

(setq
        SQLStatement (STRCAT "SELECT  B,H,H1,B1,S,OPH,ARTNR,Fa,TYPE FROM "TBL     " WHERE LOW <=" (RTOS DIAM 2 0)  "AND   HIGH >= " (RTOS (- DIAM tol) 2 0))
      )
Thanks

Bernd

[irneb]

Do you want to do this from lisp? It might be a lot of unnecessary coding only making such a search slower.

If your XLS file is formatted correctly you could link to it through ADOLisp as if it's a database with tables inside. Then the link can use SQL statements same as with any other DB (like Access). But if the XLS isn't formatted to suit tables inside the XLS - this won't work.

Else a much simpler way might be to use Excel's formulas to perform such searches. E.g. use something like VLookup and then extract the result(s) into Lisp. This is a lot more customizable to suit any formatting you may have.

[Amsterdammed]

i see and agree. i don't know how i would use vlookup in vlisp?

[irneb]

You don't. You use it in Excel. Create a new sheet (if you want) then use VLookup in a formula (might need to add some input column for the lookup key). Remember to use the $ notation so a copy doesn't adjust the source range. Then you can copy the formula for as many lines as you want.

After that, you open the XLS file through lisp and activeX. Change the key value(s) you want to search for on that sheet, then extract the formula(s) value(s). There are many samples here (and on other forums) of setting / getting values from excel cells.

This is simply a way of using Excel's own formulas to extract data. VLookup is simply a sample I chose, there are many others you could use. I generally press the [ƒx] "Insert Function" button next to the formula bar - it makes entering / remembering these formulas easier (though I must say LibreOffice-Calc's formula wizard is a lot better than Excel's).

[Amsterdammed]

I see. I will try that. Thanks