Close Excel

I have spent about a week learning to read Excel spreadsheets and I have still having a problem with completely closing the Excel file. The program quietly finishes after reading the excel, but when I check the "Task Manager" a session of Excel.exe is still running (even if I shut down AutoCAD). Also, when I do shut down the Excel.exe process and reopen the file (with Excel), I have to perform a "Document Recovery" on the file.
I have tried to make sure I have released all objects in the correct order, but I'm now brainlocked and can't see the trees for the forest.
Thanks in advance!!

(defun C:GETXL ()
(setq FileName "Excel.xls")
(XL_Get_App FileName)
; set to nil the variable with list for each sheet with lists of values by row-column
(setq LIST_EXCEL nil)
; get list of all sheets in Workbook
(XL_Get_Sheets)
(setq List_Sheet_Names (list "Sheet1" "Sheet2"))
(foreach SheetName List_Sheet_Names
(if (member SheetName List_SheetsFound)
(progn
; activate sheet, get range of cells, start-end rows & columns
(XL_Get_Sheet_Data SheetName)
; Read Excel worksheet with range and store in LIST_EXCEL
(XL_Read_Sheet XLrowS XLcolS XLrowE XLcolE)
(setq LIST_EXCEL (append LIST_EXCEL (list List_Cells)))
)
)
)
; release objects, set values to nil and (GC)
(XL_Exit_App)
(princ)
)

; (XL_Get_App FileName) open Excel Application and get basic objects
(defun XL_Get_App (XLFileName /)
; Check if excel object library is all ready loaded
(if (null XL_Library)
(progn
; load appropriate Excel library
(setq XL_Library
(cond
((findfile (strcat (getenv "systemdrive") "\\Program Files\\Microsoft Office\\Office10\\Excel.exe")))
((findfile (strcat (getenv "systemdrive") "\\Program Files\\Microsoft Office\\Office\\Excel.exe")))
((findfile (strcat (getenv "systemdrive") "\\Program Files\\Microsoft Office\\Office\\Excel10.olb")))
((findfile (strcat (getenv "systemdrive") "\\Program Files\\Microsoft Office\\Office\\Excel9.olb")))
((findfile (strcat (getenv "systemdrive") "\\Program Files\\Microsoft Office\\Office\\Excel8.olb")))
(T (alert "\nERROR - Unknown or No Excel Program on this computer")(exit))
)
)
; setup Excel library interfaces for specific access
(if XL_Library
(vlax-import-type-library
:tlb-filename XL_Library
:methods-prefix "MSxl-"
roperties-prefix "MSxl-"
:constants-prefix "MSxl-"
)
)
)
)
; get Excel data file
(setq XLfile (findfile XLFileName))
; open Excel application
(if (and XLfile (setq XLapp (vlax-get-or-create-object "Excel.Application")))
(progn
; get the workbooks object
(setq XLwbs (vlax-get-property XLapp "Workbooks"))
; open workbooks
(setq XLwbo (vla-open XLwbs XLfile))
; get sheets object
(setq XLsho (vlax-get-property XLwbo "Sheets"))
)
; application could not be started
(progn
(if (not XLfile)
(alert (strcat "\nERROR - Can not find or open " XLFileName " file."))
(alert "\nERROR - Can not start Excel Application.")
)
(princ "\nAborting Program...")
(exit)
)
)
(princ)
)

; (XL_Get_Sheets) get list of all sheets in Workbook
(defun XL_Get_Sheets () ;(/ SHnum Count SheetCur SheetNam)
; get number of sheets in workbook
(setq SHnum (vla-get-count XLsho))
(setq List_SheetsFound nil)
(setq Count 1)
; get list of the sheet names
(while (<= Count SHnum)
(setq SheetCur (MSxl-get-item XLsho Count))
(setq SheetNam (vla-get-name SheetCur))
(setq List_SheetsFound (append List_SheetsFound (list SheetNam)))
(setq Count (+ Count 1))
)
(vlax-release-object SheetCur)
(princ)
)

; (XL_Get_Sheet_Data SheetName) activate sheet, get range of cells, start-end rows & columns
(defun XL_Get_Sheet_Data (SheetName / XLrange XLstart XLend)
(setq XLash (vlax-get-property XLsho 'Item SheetName))
; activate selected worksheet
(vlax-invoke-method XLash "Activate")
; get worksheet used range
(setq XLrng (vlax-get-property XLash 'UsedRange))
; get the cells from the range
(setq XLcel (vlax-get-property XLrng 'Cells))
; get the addresses of the top left and bottom right cells
(setq XLadr (MSxl-get-address XLrng XLcel 2 3 1))
; get start,end of rows and columns
(setq XLrange (Get_Text_list XLadr "!"))
(setq XLstart (car (Get_Text_list (last XLrange) ":")))
(setq XLend (cadr (Get_Text_list (last XLrange) ":")))
(setq XLrowS (atoi (substr XLstart 2)))
(setq XLcolS (atoi (cadr (Get_Text_list XLstart "C"))))
(setq XLrowE (atoi (substr XLend 2)))
(setq XLcolE (atoi (cadr (Get_Text_list XLend "C"))))
(princ)
)

; (XL_Read_Sheet XLrowS XLcolS XLrowE XLcolE) Read Excel worksheet with range and store in LIST_EXCEL
(defun XL_Read_Sheet (RowS ColS RowE ColE) ;(RowS ColS RowE ColE / RowC ColC List_Row Cell_Value)
; set current row and column
(setq RowC RowS)
; start with emply list
(setq List_cells nil)
; loop from start to end of rows
(while (<= RowC RowE)
(setq ColC ColS)
(setq List_Row nil)
; loop from start to end of columns
(while (<= ColC ColE)
(setq Cell_Value (XL_Get_Cell_Value RowC ColC))
(setq List_Row (append List_Row (list Cell_Value)))
(setq ColC (1+ ColC))
)
(setq List_Cells (append List_Cells (list List_Row)))
(setq RowC (1+ RowC))
)
List_cells
)

; (XL_Get_Cell_Value) read cell value from Row & Column Number
(defun XL_Get_Cell_Value (RowT ColT /)
(vlax-variant-value
(MSxl-get-value
(vlax-variant-value
(MSxl-get-item
(MSxl-get-cells (MSxl-get-ActiveSheet XLapp))
(vlax-make-variant RowT)
(vlax-make-variant ColT)
)
)
)
)
)

; (XL_Exit_App) release objects, set values to nil and (GC)
(defun XL_Exit_App ()
(vlax-release-object XLcel)
(vlax-release-object XLrng)
(vlax-release-object XLash)
(vlax-release-object XLsho)
(vlax-release-object XLwbo)
(vlax-release-object XLwbs)
(vlax-release-object XLapp)
(setq SHnum nil SheetCur nil SheetNam nil)
(setq XLadr nil XLcel nil XLrng nil XLash nil XLsho nil XLwbo nil XLwbs nil XLapp nil)
(gc)
(princ)
)

; (Get_text_list Text Chars) subroutine to create a list from text separated by character(s)
(defun Get_Text_list (TTT TF / LTF KKS KKF KKK KKL)
(setq LIST_TEXT nil)
(setq LTF (strlen TF))
(setq KKS 1 KKF 0 KKK 1 KKL (strlen TTT))
(while (<= KKK KKL)
(if (= (substr TTT KKK LTF) TF)
(progn
(setq LIST_TEXT (append LIST_TEXT (list (substr TTT KKS (- KKK KKS)))))
(setq KKS (+ KKK LTF))
)
)
(setq KKK (1+ KKK))
)
(setq LIST_TEXT (append LIST_TEXT (list (substr TTT KKS))))
)

 

I tried the following line -
(vlax-invoke-method XLapp 'QUIT)
before the
(vlax-release-object XLapp)
but that didn't close the Excel process either.

 

Terry,
Would that cause problems if a user already had excel opened
with another spreadsheet?

Regards,
Doug

PS. Alan,
Did you close or quit from the open workbooks before trying
to quit excel? I'm not sure whether you need to but guessing.

 

....

PS. Alan,
Did you close or quit from the open workbooks before trying
to quit excel? I'm not sure whether you need to but guessing.


Perceptive question. Yes, *all* of Excel's objects need to be released; but
the workbook needs to be closed, and the application quit before releasing
those objects.

 

I added the following lines with ;<<< and that seems to solve the problem
with the "Document Recovery", but I still have an "Excel Process" running
even after shutting down AutoCAD. But I ran the program 50 times - opening a
drawing, access Excel files, drawing and then saving. Everything seems to be
okay, but I would still like to stop the "Excel Process". Okay, I
know...VBA... But I needed a quick fix before the next major learning curve.
Plus I needed more experience with the ActiveX in AutoCAD.

(defun XL_Exit_App ()
(vlax-release-object XLcel)
(vlax-release-object XLrng)
(vlax-release-object XLash)
(vlax-release-object XLsho)
(vlax-invoke-method XLwbo 'SAVE) ;<<<
(vlax-invoke-method XLwbo 'CLOSE) ;<<<
(vlax-release-object XLwbo)
(vlax-invoke-method XLwbs 'CLOSE) ;<<<
(vlax-release-object XLwbs)
(vlax-invoke-method XLapp 'QUIT) ;<<<
(vlax-release-object XLapp)
(setq SHnum nil SheetCur nil SheetNam nil)
(setq XLadr nil XLcel nil XLrng nil XLash nil XLsho nil XLwbo nil XLwbs
nil XLapp nil)
(gc)
(princ)
)

 

Alan,
Do you really need to load the type library? Might loading
that activate an excel process that continues running? Is
there a corresponding function to unload a type library?

Regards,
Doug

 

I spent a week trying to figure out how to read every cell in each sheet of
an excel file. I tried Jon Fleming's ADOlisp and had problems with SQL
search returning nil on some cells in a column that were real numbers with
other cells in the same column that were text (I tried column format set to
General & Text). I then used several programs I found written by Jeffery
Sanders, David Stein, AfraLisp, etc. that used the library interface and got
that to work. I couldn't figure out a different method to -
; get total number of sheets in a workbook
(setq SheetCur (MSxl-get-item XLsho Count))
; get the addresses of the top left and bottom right cells
(setq XLadr (MSxl-get-address XLrng XLcel 2 3 1))
other than through the library interface

 

Have you tried
(vlax-invoke-method xlsho 'item count)

and

(vlax-get xlrng 'address xcel 2 3 1)


or things like it?

Just a guess. I really don't think having
an excel process running that is doing nothing
is that big a deal however. Does the excel
process close when Autocad closes?

Regards,
Doug

 

Thanks Terry. That's nice to know.