title: How to stop Excel from mangling a formula inserted by my VBA script
body: <p>I have a VBA script in an Excel workbook that gathers results from all the workbooks in a particular folder.</p>
  
  <pre><code>Private Sub Workbook_Open()
  
      Dim path As String
      Dim fso As Object
      Dim folder As Object
      Dim file As Object
  
      Dim i As Integer
  
      Dim data As Object
  
      Set data = Worksheets("RawData")
      path = data.Range("A1").Value
  
      i = 3
  
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set folder = fso.GetFolder(path)   
  
      data.Rows("2:" &amp; data.Rows.Count).ClearContents
  
      For Each file In folder.Files
          If Right(UCase(file.name), 5) = ".XLSX" And Left(file.name, 1) &lt;&gt; "~" Then
              data.Cells(i, 2) = "='" + path + "\[" + file.name + "]Summary'!A1:J1"
              i = i + 1
          End If
      Next file
  
  End Sub
  </code></pre>
  
  <p>The idea is for each <code>.xlsx</code> file in a given folder, I add a reference to the results range in that file. For example, if there is a file <code>Test1.xlsx</code> in folder <code>C:\Sheets</code>, the VBA puts the following formula into some row on the sheet containing the script:</p>
  
  <pre><code>='C:\Sheets\[Test1.xlsx]Summary'!A1:J1
  </code></pre>
  
  <p>Excel then pulls values out of <code>Test1</code> and puts them in the current workbook's <code>RawData</code> sheet.</p>
  
  <p>This worked until today, when my formulas started ending up with <code>@</code> right after the <code>=</code> sign, like this:</p>
  
  <pre><code>=@'C:\Sheets\[Test1.xlsx]Summary'!A1:J1
  </code></pre>
  
  <p>This gives me a <code>#VALUE?</code>.</p>
  
  <p>Excel helpfully gave me a message stating that it has just now started inserting <code>@</code> into formulas due to some syntax changes, but that it wouldn't affect calculations. I can't get this message to show up again, but that was the gist of it. Obviously it does affect calculations. If I manually remove the <code>@</code> from the formula, it works fine.</p>
  
  <p>I have Office 365 and I guess I must have received an update recently that added this "feature" because all this used to work fine.</p>
  
  <p>If I modify the VBA script to reference only a single cell, the <code>@</code> does not get inserted. But using a named range for the results (rather than <code>A1:J1</code>) still has the problem.</p>
  
  <p>Anyone have any ideas for a workaround?</p>