title: Excel error: Method 'Refresh' of object 'WorkbookConnection' failed. Then it crashes
body: <p>I am using 2013 32-bit Excel. </p>
  
  <p>I am attempting to change the name of the text file within the data connection, in code (see below). It was originally set up manually within the spreadsheet by going recording a macro going:</p>
  
  <blockquote>
    <p><code>Data</code> tab → <code>From Text</code> → <code>Get External Data group</code></p>
  </blockquote>
  
  <p>There are several connections in this spreadsheet, all set up manually, and all need to be updated in code. They are all connections of the same type: <strong>text</strong>. </p>
  
  <p>When I execute the code below, Excel returns an error on the <strong><code>Refresh</code></strong> statement:</p>
  
  <blockquote>
  <pre><code>"Method 'Refresh' of object 'WorkbookConnection' failed"
  </code></pre>
  </blockquote>
  
  <p>Then I step again, and this time it successfully executes the <code>Refresh</code> statement, in the sense that it doesn't return an error. But when I then press <code>F5</code> to continue, Excel crashes. </p>
  
  <pre><code>Dim wC As WorkbookConnection
  Dim rG As Range
  Dim rSummary As Range
  Dim sh As Worksheet
  Dim sChangeValue As String
  Dim sWorksheetName As String
  Dim sDataTableName As String
  Dim sConnection As String
  Dim lo As ListObject
  
  
  For Each wC In ActiveWorkbook.Connections
      If wC.Type = xlConnectionTypeTEXT Then
          'I say "For Each", but in practice there is only one range per sheet
          For Each rG In wC.Ranges
              Set rSummary = Sheets("Summary").ListObjects("d_QueryTable").Range
              sChangeValue = Application.WorksheetFunction.VLookup(wC.Name, rSummary, 7, False)
              If sChangeValue = "Y" Then
                  sWorksheetName = Application.WorksheetFunction.VLookup(wC.Name, rSummary, 2, False)
                  sDataTableName = Application.WorksheetFunction.VLookup(wC.Name, rSummary, 3, False)
                  sConnection = Application.WorksheetFunction.VLookup(wC.Name, rSummary, 10, False)
                  With wC
                      .TextConnection.Connection = sConnection
                      .Refresh
                  End With
                  'sh(sWorksheetName).ListObjects(sDataTableName).TableObject.Refresh
              End If
          Next rG
      End If
  Next wC
  </code></pre>
  
  <p>The <code>wC.TextConnection.Connection</code> field takes the value </p>
  
  <pre><code>"TEXT;Y:\Investigations\run_49.csv"
  </code></pre>
  
  <p>The value I am changing it to, stored in <code>sConnection</code>, for testing purposes, is identical. But why then do I get the <em>Refresh</em> error? And why then does Excel crash?</p>
  
  <p>All help appreciated. </p>