Can I get data from power query to a vba variable?

Question

I have a power query function, e.g. getData with one parameter filename Can I invoke that function directly in a vba variable? Something like:

Sub MyTest()
  Dim MyVar
  MyVar = ThisWorkbook.Queries("getData").Invoke("mytable.xls")
  MsgBox "The Value is " & MyVar
End Sub

I use Excel 2016

Answer 1

Unfortunately, you cannot load the Power Query result to a VBA variable. You have to first load it to the sheet. Here is a thread, where a Microsoft employee confirms that: Link to Microsoft Employee relating to that issue

Answer 2

Contrary to the link in the accepted answer, it is possible to get an array from a query if you load the query to the data model. It can be a pain, and there are lots of pitfalls (spaces in table names, and I haven't tested where the query output is a list or otherwise not a table).

These functions aren't cleaned up, but they have given me success, and show where to poke around.

Function GetModelADOConnection()
'We just need the ADOConnection; the rest is for perusal

    Set wbConnections = ThisWorkbook.Connections
    Set Model = ThisWorkbook.Model
    Set ModelDMC = Model.DataModelConnection
    Set ModelDMCMC = ModelDMC.ModelConnection
    Set GetModelADOConnection = ModelDMCMC.ADOConnection
    
End Function

Sub ListConnectionTables()
'Run this to dump a list of available tables in the immediate window, so you can see what you'll need to query
    
    'https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/schemaenum
    Set conn = GetModelADOConnection
    
    Set TablesSchema = conn.OpenSchema(20)

    Debug.Print "TABLE_SCHEMA", "TABLE_NAME", "COLUMN_NAME" 'headers for immediate window
    Do While Not TablesSchema.EOF
        Set ColumnsSchema = conn.OpenSchema(4, Array(Empty, Empty, "" & TablesSchema!TABLE_NAME))
        Do While Not ColumnsSchema.EOF
            If TablesSchema!TABLE_SCHEMA <> "$SYSTEM" Then
                Debug.Print TablesSchema!TABLE_SCHEMA, TablesSchema!TABLE_NAME, ColumnsSchema!COLUMN_NAME
            End If
            ColumnsSchema.MoveNext
        Loop
        TablesSchema.MoveNext
    Loop

End Sub

Function GetRecordSetFromConnection(TABLE_NAME)
'Requires that connection is added to data model.
'Watch out for table names with spaces in them - would need additional handling
'Use the ListConnectionTables function provided above to try to sniff out what to use for the TABLE_NAME, and additional trial and error may be needed

    Set conn = GetModelADOConnection

    Set rs = CreateObject("ADODB.RecordSet")
    rs.Open "SELECT * From $" & TABLE_NAME & ".$" & TABLE_NAME, conn

    Set GetRecordSetFromConnection = rs
       
End Function

If you succeed in getting your query into a recordset, then hopefully you know where to go from there. The simplest way to turn that into an array is with myRecordSet.GetRows(). That gives a transposed version of the table, but for help from there it will just require some Googling.