Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

excel instance not closing

Posted on 2014-02-03
7
Medium Priority
?
543 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
[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
  • 4
  • 3
7 Comments
 
LVL 34

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 34

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 34

Accepted Solution

by:
Norie earned 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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