Solved

Excel instance will not close properly

Posted on 2013-06-26
3
336 Views
Last Modified: 2013-06-27
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.
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

Open in new window

0
Comment
Question by:Dale Fye (Access MVP)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 39280256
After a little research I come to the conclusion that late binding is, indeed, recommended for this type of job. Your code binds early. Try ..
Public xl As Object
The other suggestion I have is not to use Quit (seeing that it doesn't quit). Try ...
Set xl = Nothing
I owe my wisdom to this site.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39280440
You have to be extremely specific and strict in the opening and using of the Excel objects - for example as shown here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28157400.html#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:
Public Sub RenameWorkSheet()

    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
    
    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
    Set wks = wkb.Worksheets(1)
    
    wks.Name = "My New Name"
    wkb.Close True
    
    Set wks = Nothing
    Set wkb = Nothing
    
    xls.Quit
    
    Set xls = Nothing

End Sub

Open in new window

You could easily turn it into late binding if you prefer that.

/gustav
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 39280844
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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question