Find and replace specific string inside a formula in multiple excel workbooks

Question

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.

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.

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.

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.

I've used the following macro to unprotect/protect each workbook in the directory which works perfectly

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 & Application.PathSeparator & obj.Name
    End If
Next
End Sub

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.

EEM EEM · Accepted Answer · 2017-03-08T04:00:32

No need to find and replace the csv fullname (path & filename) within all formulas, just change the links source at once within each workbook.

Try this within a loop through all workbooks that need to be changed.

Dim Wbk As Workbook

    Application.DisplayAlerts = False
    Set Wbk = Workbooks.Open(Filename:="WbkTarget.Fullname", UpdateLinks:=3)
    With Wbk
        .ChangeLink _
            Name:="CsvFile.Fullname", _
            NewName:="XlsFile.Fullname", _
            Type:=xlExcelLinks
        .Save
        .Close
    End With
    Application.DisplayAlerts = True

where:

WbkTarget.Fullname: Path and name of the workbook with the link to be replaced

CsvFile.Fullname: Path and name of the csv file to be replaced

XlsFile.Fullname: Path and name of the xls that replaces the csv file

Find and replace specific string inside a formula in multiple excel workbooks

1 Answers