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