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

    Closing Instance of Excel (2003 SP3)

    I am having trouble determining why the following code opens an instance of Excel, but doesn't close it. Is there something I am leaving out?

    Thanks,
    Function ReorderSheets(strFullPath As String)
    Dim xlWrkbk As Excel.Workbook
    Dim xlApp As Excel.Application
    Dim i As Integer

    On Error GoTo Err_ReorderSheets

    ' Create a Microsoft Excel object.
    Set xlApp = CreateObject("Excel.Application")
    ' Open the spreadsheet
    Set xlWrkbk = xlApp.Workbooks.Open(strFullPath)

    ' Rename the sheets
    xlApp.DisplayAlerts = False
    xlApp.Sheets("XB").Select
    xlApp.Sheets("XB").Name = "City"
    xlApp.Sheets("XC").Select
    xlApp.Sheets("XC").Name = "City Pivot"


    With xlApp
    ' Blank sheets
    .Sheets.Add
    .Sheets("Sheet1").Select
    .Sheets("Sheet1").Name = "Reconciliation"
    .Sheets.Add
    .Sheets("Sheet2").Select
    .Sheets("Sheet2").Name = "Reconciliation2"
    .Sheets.Add
    .Sheets("Sheet3").Select
    .Sheets("Sheet3").Name = "Triangle Analysis"

    ' Reorder Sheets
    .Sheets("Core").Move after:=Sheets(22)
    .Sheets("THEST").Move after:=Sheets(21)
    .Sheets("Class").Move Before:=Sheets(21)
    .Sheets("Prior Class").Move Before:=Sheets(20)
    .Sheets("Prior Group").Move Before:=Sheets(19)
    .Sheets("Legacy Analysis").Move Before:=Sheets(18)
    .Sheets("Reconcile Lemons").Move Before:=Sheets(17)
    .Sheets("Change Amounts").Move Before:=Sheets(16)
    .Sheets("Trianglel Analysis").Move Before:=Sheets(15)
    .Sheets("Policy Yr").Move Before:=Sheets(14)
    xlApp.Range("A1").Select

    End With


    Exit_ReorderSheets:

    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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Closing Instance of Excel (2003 SP3)

    When I run your code it does close the instance of Excel.

    Have you tried stepping through the code to see where it gets to?
    Regards
    John



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

    Re: Closing Instance of Excel (2003 SP3)

    It's the same problem that you've had many times before: unqualified references to Excel objects. In the section of the code that changes the order of the sheets, you have lines like this one:
    <code>
    .Sheets("Core").Move after:=Sheets(22)
    </code>
    There's a dot (point) before the first Sheets, so that it refers to xlApp, but not before the second Sheets. This causes the problem. It should be
    <code>
    .Sheets("Core").Move After:=.Sheets(22)
    </code>
    and similar for the other lines.

    By the way, I'd use With xlWrkbk instead of With xlApp. It is not essential, but it makes it clearer what you're doing.

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

    Re: Closing Instance of Excel (2003 SP3)

    John and Hans,

    Thanks. Yes, Hans I get tunnel vision. I normally record the macro in Excel, then paste it into Access and add the "xlapp". I did get it added to the first "Sheet" on each line, but not the ending "Sheet". If I ever fully understand this Excel Object thing I think I'll do better.

    Thank you for your patience and assistance.

    Ken

Posting Permissions

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