QueryTable Interface

Definition

Represents a worksheet table built from data returned from an external data source, such as a SQL server or a Microsoft Access database. The QueryTable object is a member of the QueryTables collection.

public interface class QueryTable : Microsoft::Office::Interop::Excel::_QueryTable, Microsoft::Office::Interop::Excel::RefreshEvents_Event
[System.Runtime.InteropServices.Guid("00024428-0000-0000-C000-000000000046")]
public interface QueryTable : Microsoft.Office.Interop.Excel._QueryTable, Microsoft.Office.Interop.Excel.RefreshEvents_Event
Public Interface QueryTable
Implements _QueryTable, RefreshEvents_Event
Derived
Attributes
Implements

Remarks

This is a .NET interface derived from a COM coclass that is required by managed code for interoperability with the corresponding COM object. Use this derived interface to access all method, property, and event members of the COM object. However, if a method or event you want to use shares the same name under the same COM object, cast to the corresponding primary interface to call the method, and cast to the latest events interface to connect to the event. Refer to this topic for information about the COM object. For information about the method and property members of the COM object, see _QueryTable.

Use QueryTables(index), where index is the index number of the query table, to return a single QueryTable object.

Properties

AdjustColumnWidth

True if the column widths are automatically adjusted for the best fit each time you refresh the specified query table or XML map. False if the column widths aren’t automatically adjusted with each refresh. The default value is True. Read/write Boolean.

(Inherited from _QueryTable)
Application

Returns an Application object that represents the Microsoft Excel application. Read-only.

(Inherited from _QueryTable)
BackgroundQuery

True if queries for the PivotTable report or query table are performed asynchronously (in the background). Read/write Boolean.

(Inherited from _QueryTable)
CommandText

Returns or sets the command string for the specified data source. Read/write Object.

(Inherited from _QueryTable)
CommandType

Returns or sets an XlCmdType constant that describes the command type associated with the CommandText property. The default value is xlCmdSQL. Read/write XlCmdType.

(Inherited from _QueryTable)
Connection

Returns or sets a string that contains connection information for the query table. Read/write Object.

(Inherited from _QueryTable)
Creator

Returns a constant in the XlCreator enumeration that indicates the application in which this object was created. If the object was created in Microsoft Excel, this property returns the string XCEL, which is equivalent to the hexadecimal number 5843454C. Read-only XlCreator.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
EditWebPage

Returns or sets the web page uniform resource locator (URL) for a web query. Read/write Object.

(Inherited from _QueryTable)
EnableEditing

True if the user can edit the specified query table. False if the user can only refresh the query table. Read/write Boolean.

(Inherited from _QueryTable)
EnableRefresh

True if the PivotTable cache or query table can be refreshed by the user. The default value is True. Read/write Boolean.

(Inherited from _QueryTable)
FetchedRowOverflow

True if the number of rows returned by the last use of the Refresh(Object) method is greater than the number of rows available on the worksheet. Read-only Boolean.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
FillAdjacentFormulas

True if formulas to the right of the specified query table are automatically updated whenever the query table is refreshed. Read/write Boolean.

(Inherited from _QueryTable)
HasAutoFormat

Reserved for internal use.

(Inherited from _QueryTable)
ListObject

Returns a ListObject object for the Range object or QueryTable object. Read-only ListObject object.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
Name

Returns or sets the name of the object. Read/write String.

(Inherited from _QueryTable)
Parameters

Returns a Parameters collection that represents the query table parameters. Read-only.

(Inherited from _QueryTable)
Parent

Returns the parent object for the specified object. Read-only.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
PreserveFormatting

This property is 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 (unless the query table was created in Microsoft Excel 97 and the HasAutoFormat property is True, in which case PreserveFormatting is False). Read/write Boolean.

(Inherited from _QueryTable)
QueryType

Indicates the type of query used by Microsoft Excel to populate the query table or PivotTable cache. Read-only XlQueryType.

(Inherited from _QueryTable)
Recordset

Returns or sets a Recordset object that’s used as the data source for the specified query table or PivotTable cache. Read/write.

(Inherited from _QueryTable)
Refreshing

True if there’s a background query in progress for the specified query table. Read/write Boolean.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
RefreshPeriod

Returns or sets the number of minutes between refreshes. Read/write Integer.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
ResultRange

Returns a Range object that represents the area of the worksheet occupied by the specified query table. Read-only.

(Inherited from _QueryTable)
RobustConnect

Returns or sets how the PivotTable cache connects to its data source. Read/write XlRobustConnect.

(Inherited from _QueryTable)
RowNumbers

True if row numbers are added as the first column of the specified query table. Read/write Boolean.

(Inherited from _QueryTable)
SaveData

True if data for the PivotTable report is saved with the workbook. False if only the report definition is saved. Read/write Boolean.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
Sort

Returns the sort criteria for the query table range. Read-only.

(Inherited from _QueryTable)
SourceConnectionFile

Returns or sets a String indicating the Microsoft Office Data Connection file or similar file that was used to create the PivotTable. Read/write.

(Inherited from _QueryTable)
SourceDataFile

Returns or sets a String indicating the source data file for a query table.

(Inherited from _QueryTable)
Sql

Reserved for internal use.

(Inherited from _QueryTable)
TablesOnlyFromHTML

Creates a data table based on input values and formulas that you define on a worksheet.

(Inherited from _QueryTable)
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 Object.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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 32,767 characters. Read/write Object.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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. The default value is the current setting of the File Origin option in the Text File Import Wizard. Read/write XlPlatform.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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 32,767. The default value is 1. Read/write Integer.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
TextFileThousandsSeparator

Returns or sets the thousands separator character that Microsoft Excel uses when you import a text file into a query table. The default is the system thousands separator character. Read/write String.

(Inherited from _QueryTable)
TextFileTrailingMinusNumbers

True for Microsoft Excel to treat numbers imported as text that begin with a "-" symbol as a negative number. False for Excel to treat numbers imported as text that begin with a "-" symbol as text. Read/write Boolean.

(Inherited from _QueryTable)
TextFileVisualLayout

Returns or sets a XlTextVisualLayoutType constant that indicates whether the visual layout of the text being imported is left-to-right or right-to-left.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
WebDisableRedirections

True if Web query redirections are disabled for a QueryTable object. The default value is False. Read/write Boolean.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
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.

(Inherited from _QueryTable)
WorkbookConnection

Returns the WorkbookConnection object that the query table uses. Read-only.

(Inherited from _QueryTable)

Methods

CancelRefresh()

Cancels all background queries for the specified query table. Use the Refreshing property to determine whether a background query is currently in progress.

(Inherited from _QueryTable)
Delete()

Deletes the object.

(Inherited from _QueryTable)
Refresh(Object)

Updates an external data range (QueryTable). Boolean.

(Inherited from _QueryTable)
ResetTimer()

Resets the refresh timer for the specified query table or PivotTable report to the last interval you set using the RefreshPeriod property.

(Inherited from _QueryTable)
SaveAsODC(String, Object, Object)

Saves the PivotTable cache source as a Microsoft Office Data Connection file.

(Inherited from _QueryTable)

Events

AfterRefresh

Occurs after a query is completed or canceled.

(Inherited from RefreshEvents_Event)
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.

(Inherited from RefreshEvents_Event)

Applies to