Get Excel cell data into variables

I was going to suggest for future reference I often use the (nth ( nth this is where like hanhphuc a list is made up of sub lists so you can mix and match the items within the sub lists.

(nth 2 (nth 0 lst)) = "C"
(nth 2 (nth 1 lst)) = 67

I agree with Tharwat sometimes you need help and have to pay for it, you need to look at what cost savings would be achieved, I know of one task taking around ten minutes manually now done in seconds. Its not hard to see the cost savings. Don't forget you might get this one done and realise so much benefit that you will get more programming done.

i do support Tharwat's statement - LISP = LISt Process setting variable is not effective & messy as Tharwat mentioned.

We seem to be going around in circles...

(1- (+ (car l) (apply '+ (mapcar '(lambda (x) (* x 26)) (cdr l)))))

Not quite - this will not yield a one-to-one mapping between decimal and the 'base-26' system of Excel column headings - observe:

_$ (xls:alpha->nth "BCA")
130
_$ (xls:alpha->nth "CBA")
130

Instead, consider the method implemented by my Column to Number function -

_$ (LM:col->num "BCA")
1431
_$ (LM:col->num "CBA")
2081

(Note that this result is one-based, not zero-based - subtract one from the result to obtain a list index).

If only I had your excel functions like five years earlier Lee , could have saved me some time. Did manage on my own but (as usual) not as elegant as yours

We seem to be going around in circles...

 

 

Not quite - this will not yield a one-to-one mapping between decimal and the 'base-26' system of Excel column headings - observe:

_$ (xls:alpha->nth "BCA")
130
_$ (xls:alpha->nth "CBA")
130

Instead, consider the method implemented by my Column to Number function -

_$ (LM:col->num "BCA")
1431
_$ (LM:col->num "CBA")
2081

(Note that this result is one-based, not zero-based - subtract one from the result to obtain a list index).

Lee your are correct. thanks for pointing out the mistake. It was a quick idea popped up but didn't test intensively up to 3 letters or more.

It's been a while, as always your criticisms are always appreciated !

our projects prefer txt csv file than xls,

(LM:col->num "CBA") 

nice sub already exist

earlier i was unsure, now i'm convinced of one-based is preferable. thanks

I agree with Tharwat sometimes you need help and have to pay for it, you need to look at what cost savings would be achieved, I know of one task taking around ten minutes manually now done in seconds. Its not hard to see the cost savings. Don't forget you might get this one done and realise so much benefit that you will get more programming done.

agree useful routine really boosts up productivity.

suggestion: add a donation button for each thread.

If only I had your excel functions like five years earlier Lee , could have saved me some time. Did manage on my own but (as usual) not as elegant as yours

Thank you - I hope they prove useful to you

Lee your are correct. thanks for pointing out the mistake. It was a quick idea popped up but didn't test intensively up to 3 letters or more.

It's been a while, as always your criticisms are always appreciated !

You're most welcome hanhphuc

finally at once.

Thank you guys for your input.

Now find out why excel is closed when the routine is ready.

Which part should I delete from first post (fixo)?

;(nth 0 (nth 0 lst))
;(nth 1 (nth 0 lst))
;(nth 2 (nth 0 lst))
;(nth 3 (nth 0 lst))

;example
(alert (strcat (strcat " column 1 . . . " (nth 0 (nth 0 lst))) "\n" (strcat " column 2 . . . " (nth 1 (nth 0 lst))) "\n" (strcat " column 3 . . . " (nth 2 (nth 0 lst))) "\n" (strcat " column 4 . . . " (nth 3 (nth 0 lst)))))

Pmxcad

finally at once.

Thank you guys for your input.

Now find out why excel is closed when the routine is ready.

Which part should I delete from first post (fixo)?

 

;(nth 0 (nth 0 lst))
;(nth 1 (nth 0 lst))
;(nth 2 (nth 0 lst))
;(nth 3 (nth 0 lst))

;example
(alert (strcat (strcat " column 1 . . . " (nth 0 (nth 0 lst))) "\n" (strcat " column 2 . . . " (nth 1 (nth 0 lst))) "\n" (strcat " column 3 . . . " (nth 2 (nth 0 lst))) "\n" (strcat " column 4 . . . " (nth 3 (nth 0 lst)))))

 

Pmxcad

try comment out

 ; (*error* nil) 

That's it, works perfectly.

Thanks rlx