Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    4

    Question Unanswered: Access Automation does not let go of Excel

    I can't find the problem in the code below. When I run this from Access, Excel does not totally close until I close the whole database. Can someone find the leak, please?

    TIA,

    Tom Ewald
    tewald@yahoo.com

    Sub IPT_Export()
    Dim XLApp As Excel.Application
    Dim objXLBook As Excel.Workbook
    Dim astrLinks As Variant
    Dim strFileName As String

    strFileName = InputBox("What should the new file be named? ", "Request for Information") & ".xls"


    Set XLApp = New Excel.Application
    Set objXLBook = XLApp.Workbooks.Open("c:\my documents\WRI_IPT.xlt", 0)

    DoCmd.SetWarnings False

    objXLBook.SaveAs (strFileName)

    'Break the link
    astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLi nks)
    ActiveWorkbook.BreakLink _
    Name:=astrLinks(1), _
    Type:=xlLinkTypeExcelLinks
    objXLBook.Save
    ActiveWorkbook.Close

    Set objXLBook = Nothing

    XLApp.Quit
    Set XLApp = Nothing

    DoCmd.SetWarnings True

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    You're using unqualified references to excel objects.

    Try replace the "activethingie" with your workbook object (objXLBook), here's a bit more info
    http://support.microsoft.com/default.aspx?kbid=178510

    I think the Excel version of "setwarnings" is something like

    XLApp.DisplayAlerts = false
    Roy-Vidar

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Is it necessary to close Excel, or can the user do it?

    When running Excel through automation from VB 6, I've had problems when the outside application STARTS Excel and then releases it instead of closing it.

    Specifically, VB opens Excel, does some stuff and then closes itself while leaving Excel open. User closes Excel at a later time and an error sometimes results with an obscure message (sorry - don't remember the message).

    After some research, it was related to one of those "memory leaks" and the solution was to require the user to open Excel first, and then connect to it using GetObject instead of Opening it using CreateObject or the method you used.

    Perhaps this will help you.

    good luck,
    tc

Posting Permissions

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