查询表刷新方法 (Excel)QueryTable.Refresh method (Excel)

更新一个查询表对象中的**** 外部数据区域。Updates an external data range in a QueryTable object.

语法Syntax

表达式刷新(BackgroundQuery)expression.Refresh (BackgroundQuery)

_表达式_一个代表查询表对象**** 的变量。expression A variable that represents a QueryTable object.

参数Parameters

名称Name 必需/可选Required/Optional 数据类型Data type 说明Description
BackgroundQueryBackgroundQuery 可选Optional VariantVariant 只用于基于 SQL 查询结果的 QueryTablesUsed only with QueryTables that are based on the results of a SQL query. 如果为 True,则在数据库建立连接并提交查询之后,将控制返回给过程。True to return control to the procedure as soon as a database connection is made and the query is submitted. QueryTable 在后台进行更新。The QueryTable is updated in the background. 如果为 False,则在所有数据被取回到工作表之后,将控制返回给过程。False to return control to the procedure only after all data has been fetched to the worksheet.

如果未指定此参数, 则**BackgroundQuery** 属性的设置将确定查询模式。If this argument isn't specified, the setting of the BackgroundQuery property determines the query mode.

返回值Return value

布尔值Boolean

注解Remarks

下列说明适用于基于 SQL 查询结果的 QueryTable 对象。The following remarks apply to QueryTable objects that are based on the results of a SQL query.

Refresh 方法使 Microsoft Excel 连接到 QueryTable 对象的数据源,执行 SQL 查询,并将数据返回到基于 QueryTable 对象的区域。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. 除非调用该方法,否则 QueryTable 对象不与数据源通信。Unless this method is called, the QueryTable object doesn't communicate with the data source.

当建立与 OLE DB 或 ODBC 数据源的连接时, Microsoft Excel 将使用**connection** 属性指定的连接字符串。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. 如果**DisplayAlerts** 属性为False, 则不会显示对话框, Refresh方法将失败, 并出现 "连接信息不足" 异常情况。If the DisplayAlerts property is False, dialog boxes aren't displayed and the Refresh method fails with the Insufficient Connection Information exception.

在 Microsoft Excel 建立一个成功的连接之后,将存储完整的连接字符串,这样,以后在同一编辑会话中调用 Refresh 方法时就不会再显示提示。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. 您可以通过检查connection属性的值来获取已完成的连接字符串。You can obtain the completed connection string by examining the value of the Connection property.

完成数据库连接后,将检查 SQL 查询的有效性。After the database connection is made, the SQL query is validated. 如果该查询无效,Refresh 方法将失败并导致“SQL 语法错误”异常。If the query isn't valid, the Refresh method fails with the SQL Syntax Error exception.

如果查询需要参数, 则在调用Refresh方法之前, 必须使用参数绑定信息对**parameters** 集合进行初始化。If the query requires parameters, the Parameters collection must be initialized with parameter binding information before the Refresh method is called. 如果未绑定足够的参数,Refresh 方法将失败并导致“参数错误”异常。If not enough parameters have been bound, the Refresh method fails with the Parameter Error exception. 如果将参数设置为提示输入其值, 则无论DisplayAlerts属性如何设置, 对话框都将显示给用户。If parameters are set to prompt for their values, dialog boxes are displayed to the user regardless of the setting of the DisplayAlerts property. 如果用户取消参数对话框,Refresh 将停止并返回 FalseIf the user cancels a parameter dialog box, the Refresh method halts and returns False. 如果使用parameters集合绑定了额外的参数, 则将忽略这些额外的参数。If extra parameters are bound with the Parameters collection, these extra parameters are ignored.

如果成功地完成或启动查询,则 Refresh 方法返回 True;如果用户取消连接或参数对话框,该方法返回 FalseThe 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.

若要查看读取的行数是否超出了工作表上的可用行数, 请检查**FetchedRowOverflow** 属性。To see whether the number of fetched rows exceeded the number of available rows on the worksheet, examine the FetchedRowOverflow property. 每次调用 Refresh 方法之前,该属性都将被初始化。This property is initialized every time the Refresh method is called.

支持和反馈Support and feedback

有关于 Office VBA 或本文档的疑问或反馈?Have questions or feedback about Office VBA or this documentation? 请参阅 Office VBA 支持和反馈,获取有关如何接收支持和提供反馈的指南。Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.