The ClosePIVOTTable Function

In PIVOT, click the Close button on the Pivot toolbar to run the ClosePIVOTTable function. This function does one of two things, depending on the value of fCloseExcel, a global variable set by the CreatePIVOTTable function.

• If fCloseExcel = False, Microsoft Excel was already open when the user created the pivot table, so ClosePIVOTTable doesn't close Microsoft Excel.

• If fCloseExcel = True, Microsoft Excel wasn't open before the user created the pivot table, so ClosePIVOTTable closes Microsoft Excel.

In either case, the ClosePIVOTTable function calls the RestoreExcel subroutine, which restores Microsoft Excel's screen position to its original settings. These settings are global variables stored by the CreatePIVOTTable function. The code for the ClosePivotTable function follows.

Function ClosePIVOTTable ()
    On Error GoTo CloseProblem
    Dim iTest As Integer

'Check the visible property of the Microsoft Excel application object to see
'if the object variable is set. If not, it generates an error
'which is trapped below.
    iTest = objExcelApp.Visible

'Set the focus to Microsoft Excel.
    hExcel = FindWindow("XLMAIN", ByVal 0&)
    hAccess = SetFocusAPI(hExcel)

'If the flag fCloseExcel is true, then Microsoft Excel wasn't running
'before. In this case, close Microsoft Excel.
    If fCloseExcel Then
        RestoreExcel
        objExcelApp.[Quit]

'Otherwise, just close the workbook and reset the focus in Access
'("OMain" is the class name of Access's main window).
    Else
        objWorkbook.[Close]
        RestoreExcel
        hAccess = FindWindow("OMain", ByVal 0&)
        hExcel = SetFocusAPI(hAccess)
    End If

ExitClosePIVOTTable:
'Free the application object variable.
    Set objWorkbook = Nothing
    Set objExcelApp = Nothing
    Exit Function

CloseProblem:
'If the user has closed the table already, an error occurs.
'Error 91 occurs if the user closed the table using the
'Inventory toolbar, and Error 2731 occurs if the user closed
'Microsoft Excel using it's own File Exit command.
    If (Err = 91) Or (Err = 2731) Then
        MsgBox "The PIVOT table is not open."
    ElseIf Err <> 0 Then
        MsgBox "Error " & Str$(Err) & ": " & Error$
    End If
    Resume ExitClosePIVOTTable
End Function

The code for the RestoreExcel subroutine follows.

Note Remember that if you close Microsoft Excel using the Exit command on its File menu, Microsoft Excel doesn't restore its original settings.

Sub RestoreExcel ()

'Restore Microsoft Excel's display settings as recorded in the
'CreatePIVOTTable function.
    objExcelApp.WindowState = ExcelAppState
    objExcelApp.ActiveWindow.WindowState = ExcelCurrWindowState
    objExcelApp.Toolbars("Query and PIVOT").Visible = 
        PIVOTToolbarState

    If ExcelAppState = xlNormal Then
        objExcelApp.Left = ExcelLeft
        objExcelApp.Top = ExcelTop
        objExcelApp.Width = ExcelWidth
        objExcelApp.Height = ExcelHeight
    End If

    If fMinimized Then objExcelApp.WindowState = xlMinimized
End Sub