Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

excel instance not closing

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
developingprogrammer
Asked:
developingprogrammer
  • 4
  • 3
1 Solution
 
NorieData ProcessorCommented:
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
 
developingprogrammerAuthor Commented:
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
 
developingprogrammerAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NorieData ProcessorCommented:
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
 
developingprogrammerAuthor Commented:
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
 
NorieData ProcessorCommented:
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
 
developingprogrammerAuthor Commented:
ah yes makes perfect sense now. thanks so much imnorie!! = ))
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now