title: Using VBA To Add or change Power Pivot data connection
body: <p>Even after searching and trying for a long time I am not sure whether it is possible it or not.</p>
  
  <p>I have a PP model using SQL source. Let's say command is: </p>
  
  <pre><code>select * from FactInternetSales where CustomerKey  = 11001
  </code></pre>
  
  <p>and I have filter, 'CustomerKey' and based on the filter selection I want to update the command. For e.g:</p>
  
  <pre><code>select * from FactInternetSales where CustomerKey  = 11002
  </code></pre>
  
  <p>The code I am trying is:</p>
  
  <pre><code>Dim mdl As ModelTable
      Dim wcon As WorkbookConnection
      Dim cmd As String
  
      Set mdl = ActiveWorkbook.Model.ModelTables("Customers")
      Debug.Print mdl.SourceWorkbookConnection.OLEDBConnection.CommandText
      mdl.SourceWorkbookConnection.OLEDBConnection.CommandText = "select * from      FactInternetSales where CustomerKey  = 11002" 
  </code></pre>
  
  <p>Trying to assign a command results in 'Run-time error 1004'</p>
  
  <p>I just come across this <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7ca8357a-ba39-496e-a4fb-fab5bdcdc2ab/using-vba-to-add-or-change-power-pivot-data-connection?forum=sqlkjpowerpivotforexcel&amp;prof=required" rel="nofollow">link</a>, it seems possible. But I am not getting the 'temporary tables' part. Please share your opinion (and the VBA) code you have used to address this.</p>
  
  <p>Thanks,
  Prakash</p>