Solved

excel instance not closing

Posted on 2014-02-03
7
523 Views
Last Modified: 2014-02-06
hey guys,

i'm trying to clean up after using an Excel.Application from my Access programme. i can do this no problem if i'm using early binding however if i'm using late binding i can't seem to close it.

the problem come in after the sort in Excel 2007.

here's my code below and here's the sample database.

Question: how come early binding successfully closes the Excel instance in task manager but late binding doesn't?

thanks guys!!

Option Compare Database
Option Explicit
Sub test()

    'dear Experts Exchange experts, kindly try to change the compiler constant between "Late" and "Early2007"
    #Const ExcelBindingAndVersion = "Late" '"Early2003" or "Early2007" or "Late"
    
    #If ExcelBindingAndVersion = "Early2003" Or ExcelBindingAndVersion = "Early2007" Then
        'early binding for 2003 and 2007
        Dim xlsApp                      As Excel.Application
        Dim xlsWbk                      As Excel.Workbook
        Dim xlsWst                      As Excel.Worksheet
        Set xlsApp = New Excel.Application
    #ElseIf ExcelBindingAndVersion = "Late" Then
        'late binding
        Dim xlsApp                      As Object 'excel object
        Dim xlsWbk                      As Object 'excel object
        Dim xlsWst                      As Object 'excel object
        Set xlsApp = VBA.CreateObject("Excel.Application")
    #End If
    
    Set xlsWbk = xlsApp.Workbooks.Add
    Set xlsWst = xlsWbk.Worksheets(1)
    
    
    
    With xlsWst.Sort
        .SetRange xlsWst.Range("A1:A5")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
    Set xlsWst = Nothing
    Set xlsWbk = Nothing
    xlsApp.Quit
    Set xlsApp = Nothing
    
    Stop
    'at this point in time, if i used early binding and i open task manager, there will be NO excel processes running
    'however if i use late binding i'll still have an excel process running.
    'this Excel process "unwilling to die" only happens after running the sort code
End Sub

Open in new window

Excel-Process-Not-Closing.mdb
0
Comment
Question by:developingprogrammer
  • 4
  • 3
7 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 39830072
Why are you mixing early and late-binding in this code?

If you are having version problems then you should use late-binding.

Also, when you use late-binding you need to define constants like xlYes, xlTopToBottom.

PS Why aren't you closing the Excel workbook before quitting Excel?

That could cause Excel to pop a save dialog, which might explain the 'ghost' instance.
0
 

Author Comment

by:developingprogrammer
ID: 39831318
hi imnorie! thanks for your help! i'm just using early binding to test my code.

i've taken your advice and defined the constants and also closed the excel workbook before quitting excel but still i can't get the instance to be released and closed.

here's my modified code and the modified test database. could you help me out? thanks!

Option Compare Database
Option Explicit
Sub test()

    'dear Experts Exchange experts, kindly try to change the compiler constant between "Late" and "Early2007"
    #Const ExcelBindingAndVersion = "Late" '"Early2003" or "Early2007" or "Late"
    
    #If ExcelBindingAndVersion = "Early2003" Or ExcelBindingAndVersion = "Early2007" Then
        'early binding for 2003 and 2007
        Dim xlsApp                      As Excel.Application
        Dim xlsWbk                      As Excel.Workbook
        Dim xlsWst                      As Excel.Worksheet
        Set xlsApp = New Excel.Application
    #ElseIf ExcelBindingAndVersion = "Late" Then
        'late binding
        Dim xlsApp                      As Object 'excel object
        Dim xlsWbk                      As Object 'excel object
        Dim xlsWst                      As Object 'excel object
        Set xlsApp = VBA.CreateObject("Excel.Application")
    Const xlYes As Integer = 1
    Const xlTopToBottom As Integer = 1
    Const xlPinYin As Integer = 1
    
    #End If
    
    Set xlsWbk = xlsApp.Workbooks.Add
    Set xlsWst = xlsWbk.Worksheets(1)
    
    
    With xlsWst.Sort
        .SetRange xlsWst.Range("A1:A5")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Set xlsWst = Nothing
    xlsWbk.Close
    Set xlsWbk = Nothing
    xlsApp.Quit
    Set xlsApp = Nothing
    
    Stop
    'at this point in time, if i used early binding and i open task manager, there will be NO excel processes running
    'however if i use late binding i'll still have an excel process running.
    'this Excel process "unwilling to die" only happens after running the sort code
End Sub

Open in new window

Excel-Process-Not-Closing.mdb
0
 

Author Comment

by:developingprogrammer
ID: 39831335
hi imnorie, also an additional question, how is it that in the first demo database when i didn't define my constants and i used late binding there was no error but instead it still could read the constants?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 33

Expert Comment

by:Norie
ID: 39833361
You have a reference to the Excel 14.0 Object Library which is the library that contains the constants.

The idea of late-binding is that it eliminates the need for such a reference.

When I remove the reference and remove the compiler #If directive and run the following code no instance of Excel persists after code execution.
Option Compare Database
Option Explicit
Const xlYes As Integer = 1
Const xlTopToBottom As Integer = 1
Const xlPinYin As Integer = 1

Sub test()
Dim xlsApp As Object           ' Excel.Application
Dim xlsWbk As Object           ' Excel.Workbook
Dim xlsWst As Object           ' Excel.Worksheet

    Set xlsApp = VBA.CreateObject("Excel.Application")

    Set xlsWbk = xlsApp.Workbooks.Add
    Set xlsWst = xlsWbk.Worksheets(1)

    With xlsWst.Sort
        .SetRange xlsWst.Range("A1:A5")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Set xlsWst = Nothing
    xlsWbk.Close
    Set xlsWbk = Nothing
    xlsApp.Quit
    Set xlsApp = Nothing

End Sub

Open in new window


PS If you are going to be automating Excel from Access VBA it's a good idea to make your instance of Excel visible so you can see what's happening with it.
0
 

Author Comment

by:developingprogrammer
ID: 39834336
i see thanks imnorie! sorry imnorie, you're saying that i have a reference to Excel 14.0 Object Library which contains all the constants - where do i find that? when i open up my references window i only see this references
and yup i'll make my excel instance visible so it's easier next time = ) thanks!
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 39834376
Well it's not 14.0 you have but you do have 12.0.

It's the 5 in the list - Microsoft Excel 12.0 Object Library.

The version changes according to the version of Excel the user has installed, which is kind of one reason for using late-binding.
0
 

Author Comment

by:developingprogrammer
ID: 39834434
ah yes makes perfect sense now. thanks so much imnorie!! = ))
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 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