title: Dynamic data connection string without VBA
body: <p>Is it possible to have a connection string depend on a cell value or a named formula in an <code>.xlsx</code> file (i.e. non-macro enabled sheet)?</p>
  
  <hr>
  
  <p>Background:</p>
  
  <p>Every month I create a new workbook and update the external data source. This way I have workbooks with the results for each month and can always go back to look at a past month's data. </p>
  
  <p>Now, since I use multiple pivot tables on the same data, I use a data connection. The connection string looks something like this, of which the <strong>data source</strong> is the relevant one. </p>
  
  <pre><code>Provider=Microsoft.ACE.OLEDB.12.0;
  User ID=Admin;
  Data Source=C:\pathtofile\myworkbook1705.xlsx;
  Mode=Share Deny Write;
  Extended Properties="HDR=NO;
  ";
  Jet OLEDB // etc. etc //
  </code></pre>
  
  <p>I usually update the data source path manually, but since I want to expand the project I am doing to multiple other tasks, this manual update procedure becomes tiresome (change <code>1705</code> in the file path to <code>1706</code> from a cell value).</p>
  
  <p>A few reasons keep me from making the solution in VBA:</p>
  
  <ul>
  <li>Security warning from macro-enabled file.</li>
  <li>Users know Excel-sheets as <code>.xlsx</code> and may not recognize <code>.xlm</code>.</li>
  <li>Inheritance. My successor, who eventually needs to edit something in the sheet might not know VBA.</li>
  </ul>
  
  <hr>
  
  <p>I have searched around with no success. Relevant links that unfortunately do not answer the question are linked below.</p>
  
  <ul>
  <li><a href="https://support.office.com/en-us/article/Change-the-source-data-for-a-PivotTable-ad8ed968-ada1-4dde-9f72-30e07782dccd" rel="nofollow noreferrer">support.office.com: Change the source data for a PivotTable</a> (Standard click way of updating)</li>
  <li><a href="https://stackoverflow.com/questions/2708086/excel-macro-to-change-external-data-query-connections-e-g-point-from-one-data">Excel macro to change external data query connections - e.g. point from one database to another</a> (VBA solution)</li>
  <li><a href="https://stackoverflow.com/questions/16695704/microsoft-excel-data-connections-alter-connection-string-through-vba">Microsoft Excel Data Connections - Alter Connection String through VBA</a> (VBA solution to alteration of connection string)</li>
  </ul>