title: Modify an embedded Connection String in microsoft excel macro
body: <p>I have an Excel document that has a macro which when run will modify a <code>CommandText</code> of that connection to pass in parameters from the Excel spreadsheet, like so:</p>
  
  <pre><code>Sub RefreshData()
  ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary")
    .OLEDBConnection.CommandText = "Job_Cost_Code_Transaction_Summary_Percentage_Pending @monthEndDate='" &amp; Worksheets("Cost to Complete").Range("MonthEndDate").Value &amp; "', @job ='" &amp; Worksheets("Cost to Complete").Range("Job").Value &amp; "'"
  ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").Refresh
  End Sub
  </code></pre>
  
  <p>I would like the refresh to not only modify the connection command but also modify the connection as I would like to use it with a different database also:</p>
  
  <p><img src="https://i.stack.imgur.com/3NCTp.jpg" alt="enter image description here"></p>
  
  <p>Just like the macro replaces the command parameters with values from the spreadsheet I would like it to also replace the database server name and database name from values from the spreadsheet.</p>
  
  <p>A complete implementation is not required, just the code to modify the connection with values from the sheet will be sufficient, I should be able to get it working from there.</p>
  
  <p>I tried to do something like this:</p>
  
  <pre><code> ActiveWorkbook
   .Connections("Job_Cost_Code_Transaction_Summary")
   .OLEDBConnection.Connection = "new connection string"
  </code></pre>
  
  <p>but that does not work. Thanks.</p>