Share via


QueryTable Object Members

Excel Developer Reference

Represents a worksheet table built from data returned from an external data source, such as an SQL server or a Microsoft Access database.

Methods

  Name Description
Bb225596.methods(en-us,office.12).gif CancelRefresh Cancels all background queries for the specified query table. Use the Refreshing property to determine whether a background query is currently in progress.
Bb225596.methods(en-us,office.12).gif Delete Deletes the object.
Bb225596.methods(en-us,office.12).gif Refresh Updates an external data range (QueryTable).
Bb225596.methods(en-us,office.12).gif ResetTimer Resets the refresh timer for the specified query table or PivotTable report to the last interval you set using the RefreshPeriod property.
Bb225596.methods(en-us,office.12).gif SaveAsODC Saves the QueryTable cache source as an Microsoft Office Data Connection file.

Properties

  Name Description
Bb225596.properties(en-us,office.12).gif AdjustColumnWidth True if the column widths are automatically adjusted for the best fit each time you refresh the specified query table. False if the column widths aren’t automatically adjusted with each refresh. The default value is True. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif Application When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Application object that represents the creator of the specified object (you can use this property with an OLE Automation object to return the application of that object). Read-only.
Bb225596.properties(en-us,office.12).gif BackgroundQuery True if queries for the query table are performed asynchronously (in the background). Read/write Boolean.
Bb225596.properties(en-us,office.12).gif CommandText Returns or sets the command string for the specified data source. Read/write Variant.
Bb225596.properties(en-us,office.12).gif CommandType Returns or sets one of the XlCmdType constants listed in the following table in the remarks section. The constant that is returned or set describes the value of the CommandText property. The default value is xlCmdSQL. Read/write XlCmdType.
Bb225596.properties(en-us,office.12).gif Connection Returns or sets a string that contains one of the following: OLE DB settings that enable Microsoft Excel to connect to an OLE DB data source; ODBC settings that enable Microsoft Excel to connect to an ODBC data source; a URL that enables Microsoft Excel to connect to a Web data source; the path to and file name of a text file, or the path to and file name of a file that specifies a database or Web query. Read/write Variant.
Bb225596.properties(en-us,office.12).gif Creator Returns a 32-bit integer that indicates the application in which this object was created. Read-only Long.
Bb225596.properties(en-us,office.12).gif Destination Returns the cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). The destination range must be on the worksheet that contains the QueryTable object. Read-only Range.
Bb225596.properties(en-us,office.12).gif EditWebPage Returns or sets the web page Uniform Resource Locator (URL) for a web query. Read/write Variant.
Bb225596.properties(en-us,office.12).gif EnableEditing True if the user can edit the specified query table. False if the user can only refresh the query table. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif EnableRefresh True if the PivotTable cache or query table can be refreshed by the user. The default value is True. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif FetchedRowOverflow True if the number of rows returned by the last use of the Refresh method is greater than the number of rows available on the worksheet. Read-only Boolean.
Bb225596.properties(en-us,office.12).gif FieldNames True if field names from the data source appear as column headings for the returned data. The default value is True. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif FillAdjacentFormulas True if formulas to the right of the specified query table are automatically updated whenever the query table is refreshed. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif ListObject Returns a ListObject object for the QueryTable object. Read-only ListObject object.
Bb225596.properties(en-us,office.12).gif MaintainConnection True if the connection to the specified data source is maintained after the refresh and until the workbook is closed. The default value is True. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif Name Returns or sets a String value representing the name of the object.
Bb225596.properties(en-us,office.12).gif Parameters Returns a Parameters collection that represents the query table parameters. Read-only.
Bb225596.properties(en-us,office.12).gif Parent Returns the parent object for the specified object. Read-only.
Bb225596.properties(en-us,office.12).gif PostText Returns or sets the string used with the post method of inputting data into a Web server to return data from a Web query. Read/write String.
Bb225596.properties(en-us,office.12).gif PreserveColumnInfo True if column sorting, filtering, and layout information is preserved whenever a query table is refreshed. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif PreserveFormatting True if any formatting common to the first five rows of data are applied to new rows of data in the query table. Unused cells aren’t formatted. The property is False if the last AutoFormat applied to the query table is applied to new rows of data. The default value is True.
Bb225596.properties(en-us,office.12).gif QueryType Indicates the type of query used by Microsoft Excel to populate the query table. Read-only XlQueryType.
Bb225596.properties(en-us,office.12).gif Recordset Returns or sets a Recordset object that’s used as the data source for the specified query table. Read/write.
Bb225596.properties(en-us,office.12).gif Refreshing True if there is a background query in progress for the specified query table. Read only Boolean.
Bb225596.properties(en-us,office.12).gif RefreshOnFileOpen True if the PivotTable cache or query table is automatically updated each time the workbook is opened. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif RefreshPeriod Returns or sets the number of minutes between refreshes. Read/write Long.
Bb225596.properties(en-us,office.12).gif RefreshStyle Returns or sets the way rows on the specified worksheet are added or deleted to accommodate the number of rows in a recordset returned by a query. Read/write XlCellInsertionMode.
Bb225596.properties(en-us,office.12).gif ResultRange Returns a Range object that represents the area of the worksheet occupied by the specified query table. Read-only.
Bb225596.properties(en-us,office.12).gif RobustConnect Returns or sets how the PivotTable cache connects to its data source. Read/write XlRobustConnect.
Bb225596.properties(en-us,office.12).gif RowNumbers True if row numbers are added as the first column of the specified query table. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif SaveData True if data for the QueryTable report is saved with the workbook. False if only the report definition is saved. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif SavePassword True if password information in an ODBC connection string is saved with the specified query. False if the password is removed. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif Sort Returns the sort criteria for the query table range. Read-only.
Bb225596.properties(en-us,office.12).gif SourceConnectionFile Returns or sets a String indicating the Microsoft Office Data Connection file or similar file that was used to create the QueryTable. Read/write.
Bb225596.properties(en-us,office.12).gif SourceDataFile Returns or sets a String value that indicates the source data file for a query table.
Bb225596.properties(en-us,office.12).gif TextFileColumnDataTypes Returns or sets an ordered array of constants that specify the data types applied to the corresponding columns in the text file that you’re importing into a query table. The default constant for each column is xlGeneral. Read/write Variant.
Bb225596.properties(en-us,office.12).gif TextFileCommaDelimiter True if the comma is the delimiter when you import a text file into a query table. False if you want to use some other character as the delimiter. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif TextFileConsecutiveDelimiter True if consecutive delimiters are treated as a single delimiter when you import a text file into a query table. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif TextFileDecimalSeparator Returns or sets the decimal separator character that Microsoft Excel uses when you import a text file into a query table. The default is the system decimal separator character. Read/write String.
Bb225596.properties(en-us,office.12).gif TextFileFixedColumnWidths Returns or sets an array of integers that correspond to the widths of the columns (in characters) in the text file that you’re importing into a query table. Valid widths are from 1 through 32767 characters. Read/write Variant.
Bb225596.properties(en-us,office.12).gif TextFileOtherDelimiter Returns or sets the character used as the delimiter when you import a text file into a query table. The default value is null. Read/write String.
Bb225596.properties(en-us,office.12).gif TextFileParseType Returns or sets the column format for the data in the text file that you’re importing into a query table. Read/write XlTextParsingType.
Bb225596.properties(en-us,office.12).gif TextFilePlatform Returns or sets the origin of the text file you’re importing into the query table. This property determines which code page is used during the data import. Read/write XlPlatform.
Bb225596.properties(en-us,office.12).gif TextFilePromptOnRefresh True if you want to specify the name of the imported text file each time the query table is refreshed. The Import Text File dialog box allows you to specify the path and file name. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif TextFileSemicolonDelimiter True if the semicolon is the delimiter when you import a text file into a query table, and if the value of the TextFileParseType property is xlDelimited. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif TextFileSpaceDelimiter True if the space character is the delimiter when you import a text file into a query table. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif TextFileStartRow Returns or sets the row number at which text parsing will begin when you import a text file into a query table. Valid values are integers from 1 through 32767. The default value is 1. Read/write Long.
Bb225596.properties(en-us,office.12).gif TextFileTabDelimiter True if the tab character is the delimiter when you import a text file into a query table. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif TextFileTextQualifier Returns or sets the text qualifier when you import a text file into a query table. The text qualifier specifies that the enclosed data is in text format. Read/write XlTextQualifier.
Bb225596.properties(en-us,office.12).gif TextFileThousandsSeparator Returns or sets the thousands separator character thatMicrosoft Excel uses when you import a text file into a query table. The default is the system thousands separator character. Read/write String.
Bb225596.properties(en-us,office.12).gif TextFileTrailingMinusNumbers True for Microsoft Excel to treat numbers imported as text that begin with a "-" symbol as a negative symbol. False for Excel to treat numbers imported as text that begin with a "-" symbol as text. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif TextFileVisualLayout Returns or sets a XlTextVisualLayoutType enumeration that indicates whether the visual layout of the text being imported is left-to-right or right-to-left.
Bb225596.properties(en-us,office.12).gif WebConsecutiveDelimitersAsOne True if consecutive delimiters are treated as a single delimiter when you import data from HTML <PRE> tags in a Web page into a query table, and if the data is to be parsed into columns. False if you want to treat consecutive delimiters as multiple delimiters. The default value is True. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif WebDisableDateRecognition True if data that resembles dates is parsed as text when you import a Web page into a query table. False if date recognition is used. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif WebDisableRedirections True if Web query redirections are disabled for a QueryTable object. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif WebFormatting Returns or sets a value that determines how much formatting from a Web page, if any, is applied when you import the page into a query table. Read/write XlWebFormatting.
Bb225596.properties(en-us,office.12).gif WebPreFormattedTextToColumns Returns or sets whether data contained within HTML <PRE> tags in the Web page is parsed into columns when you import the page into a query table. The default is True. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif WebSelectionType Returns or sets a value that determines whether an entire Web page, all tables on the Web page, or only specific tables on the Web page are imported into a query table. Read/write XlWebSelectionType .
Bb225596.properties(en-us,office.12).gif WebSingleBlockTextImport True if data from the HTML <PRE> tags in the specified Web page is processed all at once when you import the page into a query table. False if the data is imported in blocks of contiguous rows so that header rows will be recognized as such. The default value is False. Read/write Boolean.
Bb225596.properties(en-us,office.12).gif WebTables Returns or sets a comma-delimited list of table names or table index numbers when you import a Web page into a query table. Read/write String.
Bb225596.properties(en-us,office.12).gif WorkbookConnection Returns the WorkbookConnection object that the query table uses. Read-only.

Events

  Name Description
Bb225596.events(en-us,office.12).gif AfterRefresh Occurs after a query is completed or canceled.
Bb225596.events(en-us,office.12).gif BeforeRefresh Occurs before any refreshes of the query table. This includes refreshes resulting from calling the Refresh method, from the user's actions in the product, and from opening the workbook containing the query table.