expression.Refresh()
expression Required. An expression that returns a ListObject object.
Remarks
Calling the Refresh method does not commit changes to the list in the Excel workbook. Uncommitted changes in the list in Excel are discarded when the Refresh method is called. To avoid losing any uncommitted changes, call the UpdateChanges method of the ListObject object before calling the Refresh method.
Refresh method as it applies to the QueryTable object.
Updates an external data range (QueryTable). Boolean.
expression.Refresh(BackgroundQuery)
expression Required. An expression that returns a QueryTable object.
BackgroundQuery Optional Variant. Used only with QueryTables that are based on the results of a SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted. The QueryTable 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.
Remarks
The following remarks apply to QueryTable objects that are based on the results of a SQL query.
The Refresh method causes Microsoft Excel to connect to the data source of the QueryTable object, execute the SQL query, and return data to the range that is based on the QueryTable object. Unless this method is called, the QueryTable object 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, dialog boxes will be displayed 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 be initialized with parameter binding information before the Refresh method is called. 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 extra parameters are bound with the Parameters collection, these 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.
Refresh method as it applies to the Chart and PivotCache objects.
Updates the cache of the Chart or PivotTable object.
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.
Refresh method as it applies to the XmlDataBinding object.
Retrieves XML data using the current connection settings of the specified XmlDataBinding object. Returns XlXmlImportResult.
| XlXmlImportResult can be one of the following XlXmlImportResult constants |
| xlXmlImportElementsTruncated The contents of the specified XML data file have been truncated because the XML data file is too large for the worksheet. |
| xlXmlImportSuccess The XML data file was successfully imported. |
| xlXmlImportValidationFailed The contents of the XML data file do not match the specified schema map. |
expression.Refresh()
expression Required. An expression that returns an XmlDataBinding object.
Example
This example refreshes the PivotTable cache for the first PivotTable report on the first worksheet in a workbook.
Worksheets(1).PivotTables(1).PivotCache.Refresh