Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Exiting Excel (Access 2003)

    For some reason not apparent to me, the following function leaves an instance of Excel open closed. I have stepped through the code in debug with Task Manager open and can see when it opens. Even though it goes through the:
    ' Quit Excel
    xlApp.Quit
    Set xlApp = Nothing
    and exits the function Excel is still open.

    Function ReorderSheets(strFullPath As String) ', strFileName As String, strSheetName As String)
    Dim xlWrkbk As Excel.Workbook
    Dim xlChartObj As Excel.Chart
    Dim xlApp As Excel.Application

    ' Create a Microsoft Excel object.
    Set xlApp = CreateObject("Excel.Application")

    ' Open the spreadsheet with the exported data.
    Set xlWrkbk = xlApp.Workbooks.Open(strFullPath)

    With xlWrkbk
    ' Reorder Sheets
    xlApp.Sheets("sqCore").Select
    xlApp.Sheets("sqCore").Move After:=Sheets(10)
    xlApp.Sheets("Reconcile").Move Before:=Sheets(6)
    xlApp.Sheets("PolTerm").Move Before:=Sheets(7)
    xlApp.Sheets("Reconcile").Move Before:=Sheets(8)
    xlApp.Sheets("Legacy").Move Before:=Sheets(7)
    xlApp.Sheets("Prior").Move Before:=Sheets(7)
    xlApp.Sheets("Changes").Move Before:=Sheets(5)
    xlApp.Sheets("PolYearA").Move Before:=Sheets(4)
    xlApp.Sheets("THAS").Move Before:=Sheets(8)
    xlApp.Sheets("PolYearB").Move Before:=Sheets(1)
    xlApp.Sheets("sqCore").Select
    xlApp.Rows("1:1").Select
    End With

    Exit_ReorderSheets:

    Set xlChartObj = Nothing
    xlWrkbk.Close SaveChanges:=True
    Set xlWrkbk = Nothing
    ' Quit Excel
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function


    Err_ReorderSheets:
    MsgBox CStr(Err) & " " & Err.Description
    Resume Exit_ReorderSheets
    End Function

    Any ideas are greatly appreciated.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exiting Excel (Access 2003)

    All your references to Sheets(n) aren't referenced to xlApp or xlWrkbk. Use
    <code>
    xlApp.Sheets("sqCore").Move After:=xlApp.Sheets(10)
    </code>
    etc., or
    <code>
    .Sheets("sqCore").Move After:=.Sheets(10)
    </code>
    since you already have With xlWrkbk ... End With.

Posting Permissions

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