Edit

Share via

QueryTable.Connection property (Excel)

  • Article

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 Excel to connect to an ODBC data source
  • A URL that enables Excel to connect to a web data source
  • The path to and file name of a text file
  • The path to and file name of a file that specifies a database or web query

Read/write Variant.

Syntax

expression.Connection

expression An expression that returns a QueryTable object.

Remarks

Setting the Connection property doesn't immediately initiate the connection to the data source. You must use the Refresh method to make the connection and retrieve the data.

For more information about the connection string syntax, see the Add method of the QueryTables collection.

Alternatively, you may choose to access a data source directly by using the Microsoft ActiveX Data Objects (ADO) library instead.

If you import data by using the user interface, data from a web query or a text query is imported as a QueryTable object, while all other external data is imported as a ListObject object.

If you import data by using the object model, data from a web query or a text query must be imported as a QueryTable, while all other external data can be imported as either a ListObject or a QueryTable.

Use the QueryTable property of the ListObject to access the Connection property.

Example

This example supplies new ODBC connection information for the first query table on the first worksheet.

Worksheets(1).QueryTables(1) _ 
 .Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"

This example specifies a text file.

Worksheets(1).QueryTables(1) _ 
 Connection := "TEXT;C:\My Documents\19980331.txt"

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.