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