title: Find and replace specific string inside a formula in multiple excel workbooks
body: <p>I have a directory with 6 sub-folders and ~300 excel workbooks(Growing every day).
  Each workbook has multiple formulas (~1200 per workbook) that reference a CSV data dump stored on a server path.</p>
  
  <p>My issue is that excel treats the CSV data dump as "dirty data" and prompts warnings every time a workbook is opened claiming it can't update the links(But when the links are checked, excel then says there's no issue).
  In all my research I've found there doesn't seem to be a way to fix this other than replace the datasource with a .xsl file which excel doesn't have any issues referencing.</p>
  
  <p>What I need to do, is perform a find and replace on ~300 workbooks, find the CSV server path inside the formulas and replace it with the new server path for the .xls file.</p>
  
  <p>I've tried "Sobolsoft's Excel Find and Replace" software, but that doesn't seem to want to look inside formulas to replace. I've used "Easy-XL" and "Kutools" both of which only work on open workbooks (Which I could live with, if I had to open 20-50 workbooks at a time, run the find and replace, then open the next batch) but neither of them wanted to work either.</p>
  
  <p>I've used the following macro to unprotect/protect each workbook in the directory which works perfectly</p>
  
  <pre><code>Const cStartFolder = "M:\Transfer\DrillHole_Interaction\4.For_Survey" 'no slash at end
  Const cFileFilter = "*.xlsm"
  Const cPassword = "" 'use empty quotes if blank
  
  Sub UnprotectAllWorksheets()
  Dim i As Long, j As Long, arr() As String, wkb As Workbook, wks As Worksheet
  
  ExtractFolder cStartFolder, arr()
  
  On Error Resume Next
  j = -1: j = UBound(arr)
  On Error GoTo 0
  
  For i = 0 To j
      Set wkb = Workbooks.Open(arr(i), False)
      For Each wks In wkb.Worksheets
          wks.Protect cPassword, True, True
      Next
      wkb.Save
      wkb.Close
  Next
  End Sub
  
  Sub ExtractFolder(Folder As String, arr() As String)
  Dim i As Long, objFS As Object, objFolder As Object, obj As Object
  
  Set objFS = CreateObject("Scripting.FileSystemObject")
  Set objFolder = objFS.GetFolder(Folder)
  
  For Each obj In objFolder.SubFolders
      ExtractFolder obj.Path, arr()
  Next
  
  For Each obj In objFolder.Files
      If obj.Name Like cFileFilter Then
          On Error Resume Next
          i = 0: i = UBound(arr) + 1
          On Error GoTo 0
          ReDim Preserve arr(i)
          arr(i) = objFolder.Path &amp; Application.PathSeparator &amp; obj.Name
      End If
  Next
  End Sub
  </code></pre>
  
  <p>If it would help, I'm also open to copying from a 'Master' workbook and copying the specific range into each other workbook (Copy range to range for each book) but I'm at my wits end and do not know how to proceed.
  Any help would be appreciated.</p>