Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel from Access (Access/Excel 2000 (9.0.4402 SR-1))

    I'm using VBA automation to "run" Excel from an Access VBA module. The basic framework is the following:
    <font face="Georgia">
    Dim appExcel As Excel.Application

    On Error Resume Next
    Set appExcel = GetObject(, "Excel.Application")

    blnNeedToQuit = False
    If Err Then
    Set appExcel = CreateObject("Excel.Application")
    blnNeedToQuit = True
    End If
    On Error GoTo 0

    <Excel automation code using the appExcel application object>

    If blnNeedToQuit Then appExcel.Quit
    </font face=georgia>
    Everything seems to work fine except that when I'm done, there's still an instance of Excel lurking on the system (it appears in the Close Program window when I press Ctrl-Alt-Del). If I don't "End Task" for Excel (and sometimes when when I do), Windows becomes unstable, particularly if I try to open Excel again ("manually" or through automation).

    This appears to be a problem only when Excel isn't already running (i.e., Excel is launched with the CreateObject method and the appExcel.Quit method is used to close Excel).

    I'm running Windows 98 Second Edition (4.10.2222 A).

    What do I need to add to (or change in) my automation code to make Excel quit more gracefully?

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Excel from Access (Access/Excel 2000 (9.0.4402 SR-1))

    I believe what you need to do is to destroy the OLE object to make it completely go away. In code

    <font color=blue> <font face="Georgia">set appExcel = Nothing</font face=georgia> </font color=blue>

    You may also need to change your code to read

    <font color=blue> <font face="Georgia">If blnNeedToQuit Then appExcel.Application.Quit</font face=georgia> </font color=blue>
    but try it first the way you have it.

    If you don't do this, you end up creating memory leaks which will cause Windows to become unstable. To see an example of this check out Technet Article 210148.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel from Access (Access/Excel 2000 (9.0.4402 SR-1))

    Thanks Wendell!!

    <font face="Georgia">Set appExcel = Nothing</font face=georgia> seems to be sufficient.

    "Nothing" to it, right? Seems obvious, now that you mention it. I'd seen this in the Help code examples, but overlooked including it and promptly forgot about it. Ah, old age is so wonderful!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •