Problem closing Excel File

Through this newsgroup I have pieced together a program to open an Excel file, read specified worksheets and create a list of lists of the data. The problem I am having is that the Excel program is not shutting down after I run the program. I can continue using the program to open Excel file(s) and get the data, but if I actually start the Excel program and try to access any Excel file previously open, the program wants to open the file as read-only. Even after I close AutoCAD an instance of Excel is still running in Task Manager, I can close it and then everthing is back to normal. I have tried to make sure I release all objects, but somewhere along the way I am sure I am missing something. If someone could help me with this program I would greatly appreciate their assistance.
TIA

Program based portions of code from Doug Broad, Marc'Antonio Alessi, David Stein, Tony Tanzillo, Ed Jobe & Kenny Ramage

Program is launched by -
(XL_Get_App "ExcelFileName.xls" (list "Worksheet1") T) ;retrieve 1 worksheet
(XL_Get_App "ExcelFileName.xls" (list "Worksheet1" "Worksheet2") T) ;retrieve 2 worksheets

; T - tells program to overwrite existing List_Excel variable

(vl-load-com)
;==============================================================================
; (XL_Get_App FileName)
; open Excel Application and get basic objects
; main subroutine to read Excel Spreadsheet book and store in LIST_EXCEL
(defun XL_Get_App (FileName List_Sheet_Names Overwrite_List_Excel / XLbooks ConnectionObject ConnectionString SQLStatement LXL PhaseSection)
(if OverWrite_List_Excel
(progn
; set Excel file name and open
(if (findfile FileName)
(progn
; open Excel Application and get basic objects
(XL_Open_App FileName)
(setq LIST_EXCEL nil)
; get list of all sheets in Workbook
(XL_Get_Sheets)
(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)))
)
)
)
)
; Excel spreadsheet not found in current folder
(progn
(alert (strcat "\nERROR - Excel file [" FileName "] NOT Found!"))
(setq LIST_EXCEL nil)
(exit)
)
)
)
)
(princ)
)


;==============================================================================
; (XL_Open_App FileName)
; open Excel Application and get basic objects
(defun XL_Open_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\\Office13\\Excel.exe")))
((findfile (strcat (getenv "systemdrive") "\\Program Files\\Microsoft Office\\Office12\\Excel.exe")))
((findfile (strcat (getenv "systemdrive") "\\Program Files\\Microsoft Office\\Office11\\Excel.exe")))
((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 "ASxl-"
roperties-prefix "ASxl-"
:constants-prefix "ASxl-"
)
)
)
)
; 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 [" FileName "] 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 (ASxl-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 (ASxl-get-address XLrng XLcel 2 3 1))
; get start,end of rows and columns
(setq XLrange (GETTEXTLIST XLadr "!"))
(setq XLstart (car (GETTEXTLIST (last XLrange) ":")))
(setq XLend (cadr (GETTEXTLIST (last XLrange) ":")))
(setq XLrowS (atoi (substr XLstart 2)))
(setq XLcolS (atoi (cadr (GETTEXTLIST XLstart "C"))))
(setq XLrowE (atoi (substr XLend 2)))
(setq XLcolE (atoi (cadr (GETTEXTLIST 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 / KK KL KF 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 KF 0 KK 0 KL (length List_Row))
(while (< KK KL)
(if (not (nth KK List_Row)) (setq KF (1+ KF)))
(setq KK (1+ KK))
)
(if (/= KF KL)
(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
(ASxl-get-value
(vlax-variant-value
(ASxl-get-item
(ASxl-get-cells (ASxl-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-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)
)

(princ)

 

Alan,
I had the same problem with an application I wrote, Excel session just 'hangs' in there. I had to do a 'save' .. e.g.
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; (Save_Sheet) Save the active Excel Sheet ;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

(defun save_sheet (WorkBook FilName OvWrYN / AltStr )
(if (and (/= WorkBook nil)(/= FilName nil))
(progn
(or OvWrYN (vlax-put-property myApp 'DisplayAlerts :vlax-False))
(if
(vl-catch-all-error-p
(setq AltStr
(vl-catch-all-apply
'vlax-invoke-method (list WorkBook "SaveAs" FilName JXCL-xlNormal "" "" :vlax-False :vlax-False nil)
)
)
)
(alert (strcat "File " FilName " NOT Saved!\n\n" (vl-catch-all-error-message AltStr)))
)
(or OvWrYN (vlax-put-property myApp 'DisplayAlerts :vlax-True))
); progn
(alert (strcat "WorkBook or FileName not present - internal Error:" "\n"))
); if
); end function

Then, the 'instance' of Excel was happy.
Saveas......then......Quit.

Bob

 

The problem I am having is that the Excel program is not

I think the answer is in:

; For more dectails see: autodesk.autocad.customization
; Excel Quit: new behaviour - Excel: multi range - Excel quit

--

Do you have the last revision of my: MsXUtl01.LSP?


; Marc'Antonio Alessi - 04/01/03
; A lot of functions derive from David M. Stein and Reini Urban (see below)
; I have modified some functions and added new.
; All functions, arguments, locals, globals are renamed.
;
; Thanks to Doug Broad and Ed Jobe for their contribution.
;
; For more dectails see: autodesk.autocad.customization
; Excel Quit: new behaviour - Excel: multi range - Excel quit
;
; Any feedback will be appreciated.
;
; Revisions:
; 05/01/03 added MSX_Integer2Column (@column$ by John Uhden)
; 05/01/03 modified MSX_GetRangeR1C1 to use MSX_Integer2Column
; 05/03/03 modified MSX_GetCell : no need to vlax-make-variant on RowNum
and ColNum
; 05/03/03 modified MSX_GetRangeA1: no need to vlax-make-variant on RngStr
; 19/05/03 renamed PutCellsColorByRow in MSX_PutCellsColorByRow
; 14/08/03 added ALEax_Make2DimSafeArray see: MSX_PutCellsPropertyFromTo
; 29/01/04 added MSX_WorkbookClose
; 08/10/04 added translation of italian prompts see: "; ENG"

--

Marc'Antonio Alessi
http://xoomer.virgilio.it/alessi
(strcat "NOT a " (substr (ver) 8 4) " guru.")

--

 

No. The latest version I have is 29/1/04.

 

No. The latest version I have is 29/1/04.

Ok, there are only few modifications:

and did you searched for?

--

Marc'Antonio Alessi
http://xoomer.virgilio.it/alessi
(strcat "NOT a " (substr (ver) 8 4) " guru.")

--

 

I did read all the threads on Excel in this newsgroup. My problem is I wrote
this code based on what I learned from this newsgroup and a few other
sources, but I don't totally understand the whole process. I was hoping
someone could spot the object that I am not releasing or the method to
release Excel based on my program. I am just learning ActiveX (and currently
taking VB.NET classes) and I don't quit know how to accomplish this task.

 

Sorry but now I dont have time to test your functions
and I have not all of them like:

ASxl-get-ActiveSheet GETTEXTLIST ASxl-get-item

Why do you not use my original funcs in MsXUtl01.LSP

I think there are more that you actually need.


Cheers.

 

Alan,
As I said before, the Excel App won't close until you
'saveas' - problem is the Excel App wants a Y or N to
save changes (even when you haven't changed anything).

Saveas filename.xls, Then, Quit.

Bob

 

I was trying to rewrite, so that I would learn what the programs were doing,
since I'm just learning ActiveX .

 

;==============================================================================
; (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-invoke-method XLwbo 'SAVE) << doesn't this line accomplish the 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)
)

If not, how do I accomplish this? I looked at your (Save_Sheet) and can't figure out what it is doing.

 

23/11/2004 renamed msxm-get-Name to msxp-get-Name
23/11/2004 renamed msxm-get-Count to msxp-get-Count
23/11/2004 renamed msxm-put-Name to msxp-put-Name

23/11/2004 modified for Office >=2003

; MSX_TypeLib
; Description: Returns typelib (olb) file for either
; Excel 97, 2000, XP or >=2003
; Args: none
; Example: (MSX_TypeLib)
;
;
(defun MSX_TypeLib ( / XlsPat MSOVer)
(setq XlsPat (MSX_GetPath))
(cond
( (<
9
(atoi
(setq MSOVer
(substr
(vl-filename-base (vl-filename-directory XlsPat)) 7
)
)
)
)
(list XlsPat "Excel.exe" MSOVer)
)
( (vl-file-systime (strcat XlsPat "Excel9.olb" ))
(list XlsPat "Excel9.olb" MSOVer)
)
( (vl-file-systime (strcat XlsPat "Excel8.olb" ))
(list XlsPat "Excel8.olb" MSOVer)
)
)
)

--

Marc'Antonio Alessi
http://xoomer.virgilio.it/alessi
(strcat "NOT a " (substr (ver) 8 4) " guru.")

--

 

(vlax-invoke-method XLwbo 'SAVE)<< doesn't this line accomplish the save??
..No, you need to supply the "Y" or "N" also.
The OvWrYN is set to 'T' on call:
Like:
(save_sheet myWBook XLS_FileName T)
Then OvWrYN = True (Yes)
And the following portion saves the file.xls, which clears
the Excel Prompt - and sets internal Excel 'saved' as True.
----------
(or OvWrYN (vlax-put-property myApp 'DisplayAlerts :vlax-False))
(if
(vl-catch-all-error-p
(setq AltStr
(vl-catch-all-apply
'vlax-invoke-method (list WorkBook "SaveAs" FilName JXCL-xlNormal "" "" :vlax-False :vlax-False nil)
)
)
)
(alert (strcat "File " FilName " NOT Saved!\n\n" (vl-catch-all-error-message AltStr)))
)
(or OvWrYN (vlax-put-property myApp 'DisplayAlerts :vlax-True))