Query Table Properties

Name

Returns

Description

AdjustColumn Width

Boolean

Set/Get whether the column widths automatically adjust to best fit the data every time the query table is refreshed

Background Query

Boolean

Set/Get if the query table processing is done asynchronously

CommandText

Variant

Set/Get the SQL command used to retrieve data (or table name if CommandType is xlCmdTable)

CommandType

XlCmdType

Set/Get the type of ComandText (for example, xlCmdSQL, xlCmdTable)

Connection

Variant

Set/Get the OLE DB connection string, the ODBC string, web data source, path to a text file, or path to a database

Destination

Range

Read-only. Returns the upper-left corner cell where the query table results will be placed

EditWebPage

Variant

Set/Get the URL for a web query

Enable Editing

Boolean

Set/Get whether the query table data can be edited or only refreshed (False)

Enable Refresh

Boolean

Set/Get whether the query table data can be refreshed

FetchedRow Overflow

Boolean

Read-only. Returns whether the last query table refresh retrieved more rows than available on the worksheet

FieldNames

Boolean

Set/Get whether the field names from the data source become column headings in the query table

FillAdjacent Formulas

Boolean

Set/Get whether formulas located to the right of the query table will update automatically when the query table data is refreshed

ListObject

ListObject

Returns a ListObject object

Maintain Connection

Boolean

Set/Get whether the connection to the data source does not close until the workbook is closed. Valid only against an OLE DB source

Name

String

Set/Get the name of the query table

Parameters

Parameters

Read-only. Returns the parameters associated with the query table

PostText

String

Set/Get the post message sent to the web server to return data from a web query

Name

Returns

Column

Info

Boolean

Set/Get whether column location, sorting, and filtering does not disappear when the data query is refreshed

Preserve Formatting

Boolean

Set/Get whether common formatting associated with the first five rows of data is applied to new rows in the query table

QueryType

xlQuery Type

Read-only. Returns the type of connection associated with the query table. (For example, xlOLEDBQuery, xlDAOQuery, xlTextImport)

Recordset

Object

Read-only. Returns a recordset associated with the data source query

Refreshing

Boolean

Read-only. Returns whether an asynchronous query is currently in progress

RefreshOn FileOpen

Boolean

Set/Get whether the query table is refreshed when the workbook is opened

Refresh Period

Long

Set/Get how long (in minutes) between automatic refreshes from the data source. Set to 0 to disable

Insertion

Mode

Set/Get how worksheet rows react when data rows are retrieved from the data source. Worksheet cells can be overwritten (xlOverwriteCells), cell rows can be partially inserted/deleted as necessary (xlInsert-DeleteCells), or only cell rows that need to be added can be added (xlInsertEntireRows)

ResultRange

Range

Read-only. Returns the cell range containing the results of the query table

Robust Connect

XlRobust Connect

Set/Get how the QueryTable connects to its data source

RowNumbers

Boolean

Set/Get whether a worksheet column is added to the left of the query table containing row numbers

SaveData

Boolean

Set/Get whether query table data is saved with the workbook

SavePassword

Boolean

Set/Get whether an ODBC connection password is saved with the query table

Sort

Sort

Read-only. Returns the sort criteria for the Query-Table

SourceConnection File

String

Set/Get the name of the file that was used to create the QueryTable

SourceData File

String

Read-only. Returns the name of the source data file for the QueryTable

Name

Returns

Column

DataTypes

Variant

Set/Get the array of column constants representing the data types for each column. Use the XlColumnDataType constants. Used only when QueryType is xlTextImport

TextFile

Comma

Delimiter

Boolean

Set/Get whether a comma is the delimiter for text file imports into a query table. Used only when QueryType is xlTextImport and for a delimited text file

TextFile

Consecutive

Delimiter

Boolean

Set/Get whether consecutive delimiters (for example, ",,,") are treated as a single delimiter. Used only when QueryType is xlTextImport

TextFile

Decimal

Separator

String

Set/Get the type of delimiter to use to define a decimal point. Used only when QueryType is xlTextImport

TextFile Fixed

ColumnWidths

Variant

Set/Get the array of widths that correspond to the columns. Used only when QueryType is xlTextImport and for a fixed-width text file

TextFile

Other

Delimiter

String

Set/Get the character that will be used to delimit columns from a text file. Used only when QueryType is xlTextImport and for a delimited text file

TextFile ParseType

XlText ParsingType

Set/Get the type of text file that is being imported: xlDelimited or xlFixedWidth. Used only when QueryType is xlTextImport

TextFilePlatform

XlPlatform

Set/Get which code pages to use when importing a text file (for example, xlMSDOS, xlWindows). Used only when QueryType is xlTextImport

TextFile PromptOn Refresh

Boolean

Set/Get whether the user is prompted for the text file to use to import into a query table every time the data is refreshed. Used only when QueryType is xlTextImport. The prompt does not appear on the initial refresh of data

TextFile

Semicolon

Delimiter

Boolean

Set/Get whether the semicolon is the text file delimiter for importing text files. Used only when QueryType is xlTextImport and the file is a delimited text file

TextFile

Space

Delimiter

Boolean

Set/Get whether the space character is the text file delimiter for importing text files. Used only when QueryType is xlTextImport and the file is a delimited text file

TextFile StartRow

Long

Set/Get which row number to start importing from a text file. Used only when QueryType is xlTextImport

Name

Returns

Description

TextFileTab Delimiter

Boolean

Set/Get whether the tab character is the text file delimiter for importing text files. Used only when QueryType is xlTextImport and the file is a delimited text file

TextFileText Qualifier

XlText Qualifier

Set/Get which character will be used to define string data when importing data from a text file. Used only when QueryType is xlTextImport

TextFile

Thousands

Separator

String

Set/Get which character is used as the thousands separator in numbers when importing from a text file (for example, ",")

TextFile

TrailingMinus

Numbers

Boolean

Set/Get whether to treat numbers imported as text that begin with a "-" symbol as negative numbers

TextFile VisualLayout

XlText Visual LayoutType

Returns 1 or 2 depending on the visual layout of the file. A value of 1 represents left-to-right, while 2 represents right-to-left

Consecutive DelimitersAsOne

Boolean

Set/Get whether consecutive delimiters are treated as a single delimiter when importing data from a web page. Used only when QueryType is xlWebQuery

WebDisable Date

Recognition

Boolean

Set/Get whether data that looks like dates is parsed as text when importing web page data. Used only when QueryType is xlWebQuery

WebDisable Redirections

Boolean

Set/Get whether web query redirections are disabled for the QueryTable object

WebFormatting

xlWeb Formatting

Set/Get whether to keep any of the formatting when importing a web page (for example, xlAll, xlNone). Used only when QueryType is xlWebQuery

WebPre

Formatted

TextToColumns

Boolean

Set/Get whether HTML data with the <PRE> tag is parsed into columns when importing web pages. Used only when QueryType is xlWebQuery

Selection

Type

Set/Get which data from a web page is imported, either all tables (xlAllTables), the entire page (xlEntirePage), or specified tables (xlSpeci-fiedTables). Used only when QueryType is xlWebQuery

WebSingleBlock TextImport

Boolean

Set/Get whether all the web page data with the <PRE> tags is imported all at once. Used only when Query-Type is xlWebQuery

Name

Returns

Description

WebTables

Workbook Connection

String

Workbook Connection

Set/Get a comma-delimited list of all the table names that will be imported from a web page. Used only when QueryType is xlWebQuery and WebSelectionType is xlSpecifiedTables

Read-only. Returns the connection that the QueryTable uses

+1 0

Post a comment