Get Excel Cell Data From Open Spreadsheet

[DGCSCAD]

I'm looking for a way (using LISP) to get data from a specific cell of an open, "Read Only" Excel Spreadsheet. I've looked into GetExcel.lsp and although it works as designed, I need to be able to keep the spreadsheet open. 

[Sea-Haven]

I wrote my own version based oon getexcel, as found some functions did not work the way I would like. 

Have a look at attached. Getcell.

[DGCSCAD]

---

@Sea-Haven wrote:

I wrote my own version based oon getexcel, as found some functions did not work the way I would like. 

Have a look at attached. Getcell.

---

After loading Alan Excel library.lsp:

; error: bad argument type: VLA-OBJECT nil

[MrJSmith]

@DGCSCAD You don't load the library, it isn't formatted properly. I'd copy paste what you want from it. He has two types of getcell functions. Either should work but note he doesn't define the variable in the function.

I personally never get a singular cell but rather a whole row or column of information. Here is an example of getting an entire row of info.

;Range -- Defined range in Excel. Typically use 'UsedRange of a sheet or active sheet.
;rowNum -- Can be either a number or letter representing the desired excel row. 1 for the first column.
(defun excelGetRowData2 (range rowNum / vD)
	(if 
		(setq vD 
			(vlax-variant-value 
				(vlax-get-property 
					(vlax-variant-value 
						(vlax-get-property 
							(vlax-get-property range "Rows")"Item" rowNum)
					) 
					'value
				)
			)
		)
		(progn
			(setq vD (vlax-safearray->list VD));Safe Array of the variant data of an entire column
			(mapcar '(lambda (x) (vlax-variant-value (vlax-variant-change-type x 8))) (car vD)) ;Returns a list of the variant data in string format.
			;Data could be transformed into other formats via different variant change types
		)
	)
)

You could then call it like such (assuming you have an excel file open).

(defun c:excelGetRow ( / rowNum range)
   (setq rowNum 1)
   (setq range (vlax-get-property (vlax-get-property (vlax-get-or-create-object "Excel.Application") 'ActiveSheet) 'UsedRange))
   (setq lst (excelGetRowData2 range rowNum))
   (print lst)
   (princ)
)
   

If you just wanted a singular cell though, copy one of his functions and setq the variable he is not declaring to the same stuff as my range (IIRC).

[Sea-Haven]

Thanks for comments, yes it full of various functions read each function. You use the correct function as needed, as implied getcell 1 cell, getrange -> select a range in Excel by fixo very nice, Getcurrent range, getrange by start end cells. In getexcel.lsp a get all cells is used as the default method.

In mine there is add an image as requested here at one stage. Thanks to others to help solving. 

There is also a great amount of functions by FIXO attached here, he is no longer with us.

You need to have good lisp skill as shown before jumping into say talking to Excel or Word. Another is Access.

Another good source is Alx by Denon Deterding. Check the help.

[DGCSCAD]

@Sea-Haven 

@MrJSmith 

I never followed up on this, so a thousand apologies. I'm finally digging back into datalinking and this is very useful info from both.

Disclaimer for post #2 from me: When I created this thread, I had just started back using LISP again after 2 decades of doing things other than programming with AutoDesk products. I originally started out LISP'ing in the early 90's with a lot of help from George O Heads' "AutoLisp in Plain English" and a few BBS threads, and had created quite a few tools and functions during that time, but as they say: If you don't use it, you lose it.