Refresh Method
Refresh method as it applies to the QueryTable object.
Updates the query table. Boolean.
expression.Refresh(BackgroundQuery)
expression Required. An expression that returns one of the above objects.
BackgroundQuery Optional Variant. Used only with query tables based on the results of an SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted (the query is updated in the background). False to return control to the procedure only after all data has been fetched to the worksheet. If this argument isn't specified, the setting of the BackgroundQuery property determines the query mode.
Refresh method as it applies to the Chart and PivotCache objects.
Updates the Chart and PivotTable cache.
expression.Refresh
expression Required. An expression that returns one of the above objects. For the PivotCache object, the cache must have at least one PivotTable report associated with it.
Remarks
Remarks as it applies to the QueryTable object.
The following remarks apply to QueryTable objects based on the results of a SQL query.
The Refresh method causes Microsoft Excel to connect to the query table's data source, execute the SQL query, and return data to the query table destination range. Until this method is called, the query table doesn't communicate with the data source.
When making the connection to the OLE DB or ODBC data source, Microsoft Excel uses the connection string specified by the Connection property. If the specified connection string is missing required values, the data access driver manager or the driver (or both) will display modal dialog boxes to prompt the user for the required information. If the DisplayAlerts property is False, dialog boxes aren't displayed and the Refresh method fails with the Insufficient Connection Information exception.
After Microsoft Excel makes a successful connection, it stores the completed connection string so that prompts won't be displayed for subsequent calls to the Refresh method during the same editing session. You can obtain the completed connection string by examining the value of the Connection property.
After the database connection is made, the SQL query is validated. If the query isn't valid, the Refresh method fails with the SQL Syntax Error exception.
If the query requires parameters, the Parameters collection must have been initialized with parameter binding information. If not enough parameters have been bound, the Refresh method fails with the Parameter Error exception. If parameters are set to prompt for their values, dialog boxes are displayed to the user regardless of the setting of the DisplayAlerts property. If the user cancels a parameter dialog box, the Refresh method halts and returns False. If there are extra parameters bound with the Parameters collection, the extra parameters are ignored.
The Refresh method returns True if the query is successfully completed or started; it returns False if the user cancels a connection or parameter dialog box.
To see whether the number of fetched rows exceeded the number of available rows on the worksheet, examine the FetchedRowOverflow property. This property is initialized every time the Refresh method is called.
Example
This example refreshes the PivotTable cache for the first PivotTable report on worksheet one.
Worksheets(1).PivotTables(1).PivotCache.Refresh