title: Excel pivot connection renamed to "Connection" in VBA
body: <p>I have been battling with this issue for a couple of days and read a lot of posts about renaming connections and still cannot achieve what I need to.</p>
  
  <p>Essentially I have a workbook that has multiple sheets each with a different pivot table using a different connection.</p>
  
  <p>I need to use VBA to insert date range criteria from a "Control" tab into the sql statement of each connection because as we all know pivots can't use the ? parameter.</p>
  
  <p>However, as we also know, Excel renames the connection when you modify it, so changing the command text with VBA ends up creating new connections and leaving redundant ones. </p>
  
  <p>Using some other very useful posts in this forum I have successfully managed to 
   - change the command text
   - delete the old connection and rename the new one assuming its name to be "Connection"</p>
  
  <p>thus:</p>
  
  <pre><code>text = "... new SQL query here..."
  ActiveWorkbook.Connections("GLCosts").ODBCConnection.CommandText = text
     ' assume excel has renamed the "GLCosts" connection to "Connection"    
  ActiveWorkbook.Connections("Connection").Refresh
     ' delete the original and rename the new one to the old name
  ActiveWorkbook.Connections("GLCosts").Delete
  ActiveWorkbook.Connections("Connection").name = "GLCosts"
  </code></pre>
  
  <p>As long as each Sub() does this in turn then it should be fine as the "new" connection name will always be "Connection" (i.e. not "Connection1" etc)</p>
  
  <p>HOWEVER
  What I have found is that Excel only renames the connection IF THERE IS A CHANGE TO THE COMMAND.TEXT</p>
  
  <p>Therefore, if the user doesn't actually change the date criteria values (held in Sheets("Control").Range("D4")) but DOES run the macro to refresh the pivots then the macro fails because the command text hasn't been modified, and there's no new connection string to rename as the old name.</p>
  
  <p>So....
  I tried to be clever. In each Sub() I first set text = "... a valid sql query but not the one i want...", then replace the command.text of the connection with this dummy query thus forcing excel to create a new connection which I can rename. Then when i set text = "...the correct sql query" excel will again create a new connection called "Connection" and I can repeat the renaming and deleting steps.</p>
  
  <p>This should have been fine but for some reason it just crashed excel and I haven't tried it again</p>
  
  <p>Does anyone have an actual solution to this? Should I try the clever stuff bit again?</p>
  
  <p>Any ideas gratefully received</p>