Dale Fye
asked on
Excel instance will not close properly
I'm using the following code to open an Excel file (hidden), and retrieve a string that contains the names of all the worksheets in the indicated workbook.
Unfortunately, the xl.Quit method used in the function is failing to close Excel. I've used similar code over and over again. Any idea what I'm missing.
Unfortunately, the xl.Quit method used in the function is failing to close Excel. I've used similar code over and over again. Any idea what I'm missing.
Public xl As Excel.Application 'Object 'used for late binding
Public wbk As Excel.Workbook 'Object 'used for late binding
Public sht As Excel.Worksheet 'Object 'used for late binding
Public Function Excel_Sheet_Names(Filename As String) As String
Dim intLoop As Integer
On Error GoTo ProcError
OpenWorkbookHidden (Filename)
For intLoop = 1 To wbk.Sheets.Count
Excel_Sheet_Names = Excel_Sheet_Names & ";" _
& chr$(34) & wbk.Sheets(intLoop).Name & chr$(34)
Next
Excel_Sheet_Names = Mid(Excel_Sheet_Names, 2)
ProcExit:
If Not wbk Is Nothing Then wbk.Close False
If Not xl Is Nothing Then xl.Quit
Exit Function
ProcError:
Select Case Err.Number
Case -2147221080
'Automation error - occurs when no workbook is already open
Case Else
Debug.Print Err.Number, Err.Description
Resume ProcExit
End Select
End Function
Public Sub OpenWorkbookHidden(Filename As String)
On Error GoTo ProcError
Set xl = GetObject(, "Excel.Application")
'Workbook Name is the file name (without the path)
Set wbk = xl.Workbooks(Mid(Filename, InStrRev(Filename, "\") + 1))
ProcExit:
Exit Sub
ProcError:
If Err.Number = 429 Then
Set xl = CreateObject("excel.application")
Resume Next
ElseIf Err.Number = 9 Then 'Workbook is not already open
Set wbk = xl.Workbooks.Open(Filename)
Resume Next
Else
Debug.Print Err.Number, Err.Description
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the assist. I didn't need to do the late binding (which I will eventually do), but using
Set xl = nothing
resolved the problem.
Set xl = nothing
resolved the problem.
https://www.experts-exchange.com/questions/28157400/Access-VBA-cannot-close-instance-of-Excel-object.html?anchorAnswerId=39247454#a39247454
Also, you are using Sheet which is not a WorkSheet.
As you are reading the worksheet names, this basic code could be a starting point:
Open in new window
You could easily turn it into late binding if you prefer that./gustav