Add 'Method (QueryTables Collection) Example

'This example creates a query table based on an ADO recordset. The example preserves 
'the existing column sorting and filtering settings and layout information for backward compatibility.

Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset

Set cnnConnect = New ADODB.Connection
cnnConnect.Open "Provider=SQLOLEDB;" & _
    "Data Source=srvdata;" & _
    "User ID=testac;Password=4me2no;"

Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
    Source:="Select Name, Quantity, Price From Products", _
    ActiveConnection:=cnnConnect, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockReadOnly, _
    Options:=adCmdText

With ActiveSheet.QueryTables.Add( _
        Connection:=rstRecordset, _
        Destination:=Range("A1"))
    .Name = "Contact List"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod =  0 
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With

'This example imports a fixed width text file into a new query table. 
'The first column in the text file is five characters wide and is imported as text. 
'The second column is four characters wide and is skipped. The remainder of the text
'file is imported into the third column and has the General format applied to it.

Set shFirstQtr = Workbooks( 1 ).Worksheets( 1 ) 
Set qtQtrResults = shFirstQtr.QueryTables.Add( _
    Connection := "TEXT;C:\My Documents\19980331.txt", 
    Destination := shFirstQtr.Cells( 1 , 1 ))
With qtQtrResults
    .TextFileParsingType = xlFixedWidth
    .TextFileFixedColumnWidths := Array( 5 , 4 ) 
    .TextFileColumnDataTypes := _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat) 
    .Refresh
End With

'This example creates a new query table on the active worksheet.

sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
    "ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
        Destination:=Range("B1"), Sql:=sqlstring)
    .Refresh
End With