Closing Instance of Excel (2003 SP3)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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:

.Sheets("Core").Move after:=Sheets(22)

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

.Sheets("Core").Move After:=.Sheets(22)

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.

Reply | Quote