title: How to automate a power query in VBA?
body: <p>I have data in sheet 1. Normally I go to power query and do my transformations, then close, and load to an existing sheet 2.</p>
  <p>I would like to automate this using VBA, where I can just run my power query automatically and populate the transformation to sheet 2.</p>
  <p>Macro recorder doesn't seem to allow me to record the steps. And there isn't much online about doing this.</p>
  <p>Trying some simpler code:</p>
  <pre><code>Sub LoadToWorksheetOnly()
  
  'Sub LoadToWorksheetOnly(query As WorkbookQuery, currentSheet As Worksheet)
      ' The usual VBA code to create ListObject with a Query Table
      ' The interface is not new, but looks how simple is the conneciton string of Power Query:
      ' &quot;OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=&quot; &amp; query.Name
       
      query = Sheets(&quot;Sheet6&quot;).Range(&quot;A1&quot;).value 'here is where my query from power query is. I put the text from power query avanced editor in another sheet cell.
      currentSheet = ActiveSheet.Name
      With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
          &quot;OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=&quot; &amp; query.Name _
          , Destination:=Sheets(&quot;target&quot;).Range(&quot;$A$1&quot;)).QueryTable
          .CommandType = xlCmdDefault
          .CommandText = Array(&quot;SELECT * FROM [&quot; &amp; query.Name &amp; &quot;]&quot;)
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .BackgroundQuery = True
          .RefreshStyle = xlInsertDeleteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .PreserveColumnInfo = False
          .Refresh BackgroundQuery:=False
      End With
       
  End Sub
  </code></pre>
  <p>Here is my issue when trying to load to new sheet manually.</p>
  <p><a href="https://i.stack.imgur.com/GSIGK.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/GSIGK.jpg" alt="enter image description here" /></a></p>