Reading native Excel files via AutoLISP (Example to use)

[llorden4]

As a self-taught LISPer, I'd been looking for good "working" example of sub-routines to read native Excel files but just haven't found one that was written simple enough to either understand or remarked well enough to follow.  I've also seen plenty of posts with others looking for the same inspiration.  After searching and reverse engineering a wide variety of examples and tutoring sites; I've pieced together a very basic "reader only" that allows you to separately make calls to open, close, set tab, and read cell with appropriate error traps to be functional as-is.  Code is below for those who might also be interested in adapting for their needs.

;--- Excel Cell Reader Sub-Routine
;--- by Leonard Lorden
;--- July 26, 2017
;--- Routine to OPEN an existing Excel file (best to confirm filename & path prior to calling)
;--- Call routine with path & filename (Example: OpenExcel "C:/Test.xlsx")
;--- File will be opened hidden
(defun OpenExcel (Exfile)
(setq MyFile (findfile Exfile)) ;double check file exists at location
(if (/= MyFile nil) ;nil = file not found
(progn ;if file found open it
(setq MyXL (vlax-get-or-create-object "Excel.Application")) ;find Excel application
(vla-put-visible MyXL :vlax-false) ;hide application from view
(vlax-put-property MyXL 'DisplayAlerts :vlax-false) ;hide Excel alerts
(setq MyBook (vl-catch-all-apply 'vla-open (list (vlax-get-property MyXL "WorkBooks") MyFile)))
))
) ;return - MyFile = nil if file not found

;--- Routine to CLOSE Excel file & Session
;--- Assumes previously opened with OpenExcel function
(defun CloseExcel
(vl-catch-all-apply 'vlax-invoke-method (list MyBook "Close"))
(vl-catch-all-apply 'vlax-invoke-method (list MyXL "Quit"))
(vl-catch-all-apply 'vlax-release-object MyCell)
(vl-catch-all-apply 'vlax-release-object MyRange)
(vl-catch-all-apply 'vlax-release-object MySheet)
(vl-catch-all-apply 'vlax-release-object MyBook)
(vl-catch-all-apply 'vlax-release-object MyXL)
(setq MyFile nil MyXL nil MyBook nil MySheet nil MyRange nil
MyTab nil MyCell nil ExCell nil) ;clear variables from memory
(gc) ;garbage cleanup
) ;return

;--- Routine to set Worksheet Tab
;--- Call using GetTab "Tabname" (Example: GetTab Sheet1)
;--- If MySheet = nil on return then requested TAB not found in Excel file or Excel file was not open
(defun GetTab (MyTab)
(if (/= MyXL nil) ;ensure file is open
(progn ;if it is then...
(setq MySheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property myBook "Sheets") "Item" MyTab)))
(if (not (vl-catch-all-error-p MySheet)) ;if requested tab found then...
(vlax-invoke-method MySheet "Activate") ;set the desired active tab
(setq MySheet nil))) ;if tab not found then nil MySheet
(setq MySheet nil)) ;if file wasn't open then nil MySheet
MySheet) ;return with Mysheet status

;--- Routine to READ an Excel Cell on the current active tab
;--- Call using GetCell "Cell Name" (Example: GetCell A1)
;--- MyCell returns cell value (nil = empty)
(defun GetCell (ExCell)
(if (/= MyXL nil) ;ensure file is open
(progn ;if it is then...
(setq MyRange (vlax-get-property (vlax-get-property MySheet 'Cells) "Range" ExCell))
(setq MyCell (vlax-variant-value (vlax-get-property MyRange 'Value2))))
(setq MyCell nil)) ;nill cell value if file not open
MyCell) ;return with cell value

[pbejse]

---

@llorden4 wrote:

As a self-taught LISPer, I'd been looking for good "working" example of sub-routines to read native Excel files but just haven't found one that was written simple enough to either understand or remarked well enough to follow.  I've also seen plenty of posts with others looking for the same inspiration.  After searching and reverse engineering a wide variety of examples and tutoring sites; I've pieced together a very basic "reader only" that allows you to separately make calls to open, close, set tab, and read cell with appropriate error traps to be functional as-is.  Code is below for those who might also be interested in adapting for their needs.

---

Thank you for sharing  llorden4, 

From you successful search, by any chance, you found a program that can either read or convert an excel file to a readable text format without MS office installed on the machine? 

pBe

[llorden4]

No I have not seen such an application, but then again I've never looked for or had the need to look for one as I've never worked on a station that did not have MS Office products available.  I'm definitely not the person who would know where to find such an application or if it even exists.  I would expect it very unlikely to find such an application as the general practice is to use Excel to make the conversion to a comma delineated file.  Good luck!

[dgorsman]

@pbejse - the xlsx Excel format is a ZIP'ed container, much like the CUIx format.  Not certain you want to dive down that particular rabbit hole though.

[pbejse]

---

@dgorsman wrote:

@pbejse - the xlsx Excel format is a ZIP'ed container, much like the CUIx format.  Not certain you want to dive down that particular rabbit hole though.

---

Thank you for the info. Think I'll pass on this one.

Although I read somewhere that it can be achieved using .NET here , certainly worth looking in to.

Cheers

[roland.r71]

---

@pbejse wrote:

---

 

From you successful search, by any chance, you found a program that can either read or convert an excel file to a readable text format without MS office installed on the machine? 

pBe

---

A program that can read (& save as .csv) an excel file?

Try OpenOffice's Calc

[roland.r71]

@pbejse

Roadkill's XLS to CSV converter is a verry small converter, that does the job for XLS (not XLSX)

Another (easy) way is to use Google Docs.

Create a new sheet, import the excel file & download it in .csv format.

[pbejse]

---

@roland.r71 wrote:

Roadkill's XLS to CSV converter is a verry small converter, that does the job for XLS (not XLSX)

Another (easy) way is to use Google Docs.

---

Thank you for all the valuable information roland.r71, What I'm hoping for is to find a way to read the excel data in its native format that Lisp/Vlisp can understand, thus removing the need to convert the file to csv.  The .NET code link I posted looks very promising, although you still need the windows component Microsoft Access Database Engine 2010 Redistributable to facilitate transfer of data between 2010 Microsoft Office System files and non-Microsoft Office applications. But I'm yet to try it.

Cheers

[roland.r71]

I was afraid you where going to say that 😉

Way to much work & effort for just a little gain, imho.

[Anonymous]

looks promising. I tried to load it but got this error

"bad element in arguments list: (QUOTE vlax-invoke-method)"

[Anonymous]

i figured it out. the function "CloseExcel" excel was missing the "()" for variables.  works like a charm, thanks!

[Anonymous]

i tried using this lisp, i have to fill title block for which i already have a lisp program which asks for values such as 

project name 

name

date 

and i want to use excel to enter the values, i tried customizing above program but it says theres syntax error and i am not able to figure out what i have done wrong

;--- Excel Cell Reader Sub-Routine
;--- by Leonard Lorden
;--- July 26, 2017
;--- Routine to OPEN an existing Excel file (best to confirm filename & path prior to calling)
;--- Call routine with path & filename (Example: OpenExcel "C:/Test.xlsx")
;--- File will be opened hidden
(defun OpenExcel "Info.xlsx"
(setq MyFile (findfile "Info.xlsx")) ;double check file exists at location
(if (/= Myfile nil) ;nil = file not found
(progn ;if file found open it
(setq MyXL (vlax-get-or-create-object "Excel.Application")) ;find Excel application
(vla-put-visible MyXL :vlax-false) ;hide application from view
(vlax-put-property MyXL 'DisplayAlerts :vlax-false) ;hide Excel alerts
(setq MyBook (vl-catch-all-apply 'vla-open (list (vlax-get-property MyXL "WorkBooks") MyFile)))
))
) ;return - MyFile = nil if file not found
;--- Routine to set Worksheet Tab
;--- Call using GetTab "Sheet1" (Example: GetTab Sheet1)
;--- If MySheet = nil on return then requested TAB not found in Excel file or Excel file was not open
(defun GetTab "Sheet1"
(if (/= MyXL nil) ;ensure file is open
(progn ;if it is then...
(setq MySheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property myBook "Sheets") "Item" MyTab)))
(if (not (vl-catch-all-error-p MySheet)) ;if requested tab found then...
(vlax-invoke-method MySheet "Activate") ;set the desired active tab
(setq MySheet nil))) ;if tab not found then nil MySheet
(setq MySheet nil)) ;if file wasn't open then nil MySheet
MySheet) ;return with Mysheet status

;--- Routine to READ an Excel Cell on the current active tab
;--- Call using GetCell "Cell Name" (Example: GetCell A1)
;--- MyCell returns cell value (nil = empty)
(defun GetCell "C1"
(if (/= MyXL nil) ;ensure file is open
(progn ;if it is then...
(setq MyRange (vlax-get-property (vlax-get-property MySheet 'Cells) "Range" ExCell))
(setq MyCell (vlax-variant-value (vlax-get-property MyRange 'C7))))
(setq MyCell nil)) ;nill cell value if file not open
MyCell) ;return with cell value
;--- Routine to CLOSE Excel file & Session
;--- Assumes previously opened with OpenExcel function
(defun CloseExcel
(vl-catch-all-apply 'vlax-invoke-method (list MyBook "Close"))
(vl-catch-all-apply 'vlax-invoke-method (list MyXL "Quit"))
(vl-catch-all-apply 'vlax-release-object MyCell)
(vl-catch-all-apply 'vlax-release-object MyRange)
(vl-catch-all-apply 'vlax-release-object MySheet)
(vl-catch-all-apply 'vlax-release-object MyBook)
(vl-catch-all-apply 'vlax-release-object MyXL)
(setq MyFile nil MyXL nil MyBook nil MySheet nil MyRange nil
MyTab nil MyCell nil ExCell nil) ;clear variables from memory
(gc) ;garbage cleanup
) ;return

[llorden4]

Well, I haven't been in LISP for a while but let's see what I can remember.  Looking through your code and comparing it to my original I only noted one deviation that I think might be it, and a couple of other possibilities.

Try changing the line under the READ option or "GetCell" from this

(setq MyCell (vlax-variant-value (vlax-get-property MyRange 'C7))))

to this

(setq MyCell (vlax-variant-value (vlax-get-property MyRange 'Value2))))

It looks like you're attempting to read cell C7 where 'Value2 is a system variable name, if memory serves.

If that doesn't do it, there were two minor locations that might have an issue...

Under the GetTab function, add a "/" after your tab name...

(defun GetTab "Sheet1"/

Same with the GetCell function, add a "/" after your cell address...

(defun GetCell "C1"/

Hope this helps, good luck!