Feeding an Excel QueryTable object from memory

Question

Greetings,

The VBA code below will create an Excel QueryTable object and display it starting on Range("D2"). The specific address of this target range is immaterial.

My question is -- is it possible to manually feed in values to an in-memory Recordset, and then have the table read from it? In other words, I want to specify the table columns and values in VBA, not have them come from a database or file.

Public Sub Foo()

    Dim blah As QueryTable
    Dim rngTarget As Range

    Dim strQuery As String

    strQuery = "SELECT * FROM MY_TABLE"
    Set rngTarget = Range("D2")
    Dim qt As QueryTable
    Set qt = rngTarget.Worksheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=MY_SQL_SERVER;APP=MY_APP;Trusted_Connection=Yes", Destination:=rngTarget)

    With qt
        .CommandText = strQuery
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .Name = "MY_RANGE_NAME"
        .MaintainConnection = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
   End With

End Sub

Answer 1

From the Excel VB Help The connection parameter can be:

"An ADO or DAO Recordset object. Data is read from the ADO or DAO recordset. Microsoft Excel retains the recordset until the query table is deleted or the connection is changed. The resulting query table cannot be edited"

So yes it looks like you can do it.

Answer 2

Can I ask why you're building a recordset in VBA? There may be be an easier way to do what you want.

Answer 3

Yes, sure.

  Dim vConnection As Variant, vCommandText As Variant
  Dim r As ADODB.Recordset
  Dim i As Long

  'Save query table definition
  vConnection = QueryTable.Connection
  vCommandText = QueryTable.CommandText


  Set r = New ADODB.Recordset
  <populate r>

  Set QueryTable.Recordset = r
  QueryTable.Refresh False

  'Restore Query Table definition
  Set QueryTable.Recordset = Nothing
  QueryTable.Connection = vConnection
  QueryTable.CommandText = vCommandText