Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Object not being killed (Access 2K SR1 and Excel 2K SR1)

    Hi all

    Got a weird scenario here and I have no idea of how to solve it.

    Here is what I am doing.

    In Access, I am creating various reports for a number of people which is done once a month.

    My logic flow is as follows:
    Create Excel.Application Object
    Retrieve name of first person and report data
    Loop begins
    Open Excel template for the report
    Fill in the template with the data
    Save (as a new file), close and email the report
    Retrieve the name of next person and the
    relevant data
    Loop ends
    Excel.Application.Quit
    Set Excel Object = nothing

    This all works perfectly fine and dandy under Windows 2000, but when I run it on a Windows 95 or Windows 98 machine, the Excel object is not destroyed is I create more than a certain number of reports (about 30 to 45) and it utilised about 95%+ of the CPU usage.

    I have even moved the creation and destruction of the Excel object into the loop and that creates even more havoc on all the operating systems.

    It seams as if Windows' parrallel processing and/or caching gets in the way and it tries open an Excel spreadsheet using the Excel Object when the object hasn't been created yet, or even destroy the object when the object hasn't finished doing some internal processing (and therefore unable to destroy it).

    Anyone got any ideas on how to solve this?

    Thanks in advance!

    James

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Object not being killed (Access 2K SR1 and Excel 2K SR1)

    In classic fashion, the Knowledge Base instructs to use the .Quit method to resolve this issue. (<A target="_blank" HREF=http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q210129>ACC2000: Applications Run from Automation Do Not Always Close (Q210129)</A>)

    I didn't test, but does it make any difference if you create the application object using:

    Set xlApp = New Excel.Application

    rather than CreateObject ?

    Maybe some of the suggestions in this article, <A target="_blank" HREF=http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q266078>BUG: Word 2000 Does Not Quit After Saving Hidden Document Using Automation (Q266078)</A> will carry over to Excel.

    (This link is just something interesting I stumbled across in my searches: <A target="_blank" HREF=http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q288902>INFO: GetObject and CreateObject Behavior of Office Automation Servers (Q288902)</A>)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object not being killed (Access 2K SR1 and Excel 2K SR1)

    Thanks for the information!

    Its definitely a help and confirmation that my basic logic is correct that that there definitely is behavioural changes of Office Applications between Operating Systems and also major VBA code changes and behavioural changes of identical VBA code between Office versions.

    I'll see what I can gleen from the links you gave me and see what the tests produce over the next two days.

    Thanks once again!

    James

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object not being killed (Access 2K SR1 and Excel 2K SR1)

    Hi there

    Thanks for the help, the information in the links you gave definitely helped and the problem is definitely a Microsoft bug (to which they gave a workaround).
    My only wish now is that Microsoft's solutions and workarounds worked stabily and consistently!

    James

Posting Permissions

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