Author Topic: Pick EXCEL cell and get info  (Read 302 times)

0 Members and 1 Guest are viewing this topic.

Jeremy2

  • Mosquito
  • Posts: 11
Pick EXCEL cell and get info
« on: May 24, 2022, 06:36:24 PM »
I have an Excel file open and I want to prompt the user to pick a cell which will define the row of information I want.
I want the cell pick to return a list that contains the following:

1. row number
2. column letter
3. name of sheet that the cell is on
4. the contents of the cell

How does one pick the cell in AutoLISP?

BIGAL

  • Swamp Rat
  • Posts: 1063
  • 40 + years of using Autocad
Re: Pick EXCEL cell and get info
« Reply #1 on: May 24, 2022, 11:41:39 PM »
You can get current range which can be 1 cell, there may be a better way.

Code: [Select]
; get a cell bu pick by AlanH
; get active range selected

(defun getrangexl ( / lst UR CR RADD )

(setq lst '())
(setq UR (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "UsedRange"))
(setq CR (vlax-get-property UR "CurrentRegion"))
(setq RADD (vlax-get-property CR "Address"))
(setq cnt (vlax-get-property CR  "Count"))

(setq lst (csv->lst radd))

(setq st (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 0 lst) )))
(setq end (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 1 lst) )))

(setq st  (columnrow st))
(setq end  (columnrow end))

(princ st)
(princ "\n")
(princ end)

)

I use a alert to stop Autocad that says pick cell then pick OK trying to find the full code.
« Last Edit: May 24, 2022, 11:46:55 PM by BIGAL »
A man who never made a mistake never made anything

ribarm

  • Gator
  • Posts: 2780
  • Marko Ribar, architect
Re: Pick EXCEL cell and get info
« Reply #2 on: May 25, 2022, 04:10:21 AM »
If I may ask (not to create new topic) regrding similar thing...

I have *.csv :
55.8,23.5,=(A1-B1)

* third cell is formula - can be anything, but it's formulated in reference to other cell(s) - A1, B1 (here)...

I want to know [ not using CAD(s) if possible ] :

How can I get value of C1 : 32.3, the quickest way and store it in a perhaps OS system variables (SET /A ret=32.3) so I can use it in batch scripting... I know that *.csv is related to EXCELL, but on www there's nothing ab this issue - macros should be I guess the best approach, but I am very rusty with using them... It could be also nice, if somehow I could copy value result directly to clipboard (thinking there may be answers ab pasting clipboard to 'tmp.txt' and piping 'tmp.txt' to batch script back, following removal of 'tmp.txt')...
Marko Ribar, d.i.a. (graduated engineer of architecture)

:)

M.R. on Youtube

Jeremy2

  • Mosquito
  • Posts: 11
Re: Pick EXCEL cell and get info
« Reply #3 on: May 25, 2022, 11:14:23 AM »
Thanks BIGAL, I could find the other information I wanted but couldn't figure out how to pause things so the user could pick something.

kirby

  • Newt
  • Posts: 111
Re: Pick EXCEL cell and get info
« Reply #4 on: May 25, 2022, 05:53:45 PM »
@ribarm

Parsing anything but the simplest Excel formula without accessing Excel from AutoCAD (and presumably outside of Excel) may be a rabbit hole you don't want to go down.  If it is just simple operators can be achieved but there are too many built in functions to deal with.

Some links to good reading on parsing Excel formulas shown on this website:
Code - HTML5: [Select]
  1. https://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

If you can use CAD to access the .csv via Excel (or an Excel VBA macro) then should be easy to solve.
e.g.
- Start Excel Application
- Open existing .csv (or .xlsx/.xls) file
- Pick range (cell) of interest to get activesheet and 'selection'
- test using 'HasFormula property to determine the cell has a formula
- if HasFormula = true, then get the solved formula from 'Value2 property
- send answer to text file or do something else with it, etc.

I can mock up a LISP example (or Excel VBA macro) based on the above if you like (and if I'm not off track for what you are hoping to achieve).

Jeremy2

  • Mosquito
  • Posts: 11
Re: Pick EXCEL cell and get info
« Reply #5 on: May 25, 2022, 06:08:34 PM »
I'm looking at extracting just basic data like strings, integers and reals.

BIGAL

  • Swamp Rat
  • Posts: 1063
  • 40 + years of using Autocad
Re: Pick EXCEL cell and get info
« Reply #6 on: May 25, 2022, 08:05:59 PM »
I did not post all the other functions that are used like "get application" which opens the link to excel.

Do you know about autocad <-> excel using lisp ? Start with getexel.lsp



A man who never made a mistake never made anything