CODEWITHSUNDEEP
excelvba
Gideon
Gideon

Reputation: 1886

Edit formulas of multiple workbooks to remove external references / change the reference to the local workbook

I have copied a worksheet into a batch of workbooks (around 45).

This worksheet contains formulas and references i.e. =Sheetname!A1 and =IF(Sheetname!A2=1, "this", "that").

However when the sheet has been copied, these formulas have become 

=[Original_Book.xlsm]Sheetname!$A$1

and

=IF([Original_Book.xlsm]Sheetname!A2=1, "this", "that").

I'd like a way to remove all of the "[Original_Book.xlsm]" references so that the formulas become local again.

Actual examples (the above being for illustrative purposes only

=[Survey_Checker.xlsm]Output!D42

Should become

=Output!D42

Likewise:

=INDEX([Survey_Checker.xlsm]QR1!E3:[Survey_Checker.xlsm]QR1!E9,[Survey_Checker.xlsm]Output!$G$17,0)

Should become

=INDEX(QR1!E3:QR1!E9,Output!$G$17,0)

and so on.

In fact, if there was a VBA method to remove every instance of the string "[Survey_Checker.xlsm]" from the worksheet (which is called "DB Output") in the cell range A1:PE5 across each file, I beleive that might work?

EDIT: Every single column between A1 and PE5 has a reference forumla in that needs removing.

Upvotes: 2

Views: 4841

Answers (1)

Ripster
Ripster

Reputation: 3595

This is a version of the original macro with added code to fix the formulas.

Sub Example()
    Dim path As String
    Dim file As String
    Dim wkbk As Workbook
    Dim rCell As Range

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    path = "C:\Test\"
    file = Dir(path)

    Do While Not file = ""
        Workbooks.Open (path & file)
        Set wkbk = ActiveWorkbook
        Sheets.Add After:=Sheets(Sheets.Count)
        On Error GoTo Sheet_Exists
        ActiveSheet.Name = "DB Output"
        On Error GoTo 0
        ThisWorkbook.Sheets("DB Output").Range("A1:PE5").Copy Destination:=wkbk.Sheets("DB Output").Range("A1")

        For Each rCell In wkbk.Sheets("DB Output").UsedRange
            If InStr(rCell.Formula, ThisWorkbook.Name) > 0 Then
                rCell.Replace What:="[*]", Replacement:=""
            End If
        Next

        wkbk.Save
        wkbk.Close
        file = Dir
    Loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Exit Sub

Sheet_Exists:
    Sheets("DB Output").Delete
    Resume
End Sub

This is a macro that will simply remove the workbook reference from the formulas

Sub Example()
    Dim path As String
    Dim file As String
    Dim rCell As Range

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    path = "C:\Test\"
    file = Dir(path)

    Do While Not file = ""
        Workbooks.Open (path & file)

        Sheets("DB Output").Select

        For Each rCell In ActiveWorkbook.Sheets("DB Output").UsedRange
            rCell.Replace What:="[*]", Replacement:=""
        Next

        ActiveWorkbook.Save
        ActiveWorkbook.Close
        file = Dir
    Loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Upvotes: 5

Related Questions