Solved

excel instance not closing

Posted on 2014-02-03
7
518 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now