Solved

excel instance not closing

Posted on 2014-02-03
7
520 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

867 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

18 Experts available now in Live!

Get 1:1 Help Now