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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #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



  4. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 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.

  5. #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
  •