编辑多个工作簿的公式以删除外部引用/更改对本地工作簿的引用 [英] Edit formulas of multiple workbooks to remove external references / change the reference to the local workbook

问题描述

我将工作表复制到一批工作簿（大约45）。

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

此工作表包含公式和引用，即 = Sheetname！A1 和 = IF（Sheetname！A2 = 1，this，that）。

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

和

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

我想要删除所有的[Original_Book.xlsm]引用，以便公式再次变成本地。

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

应该成为

=Output!D42

同样：

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

应该成为

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

等等。

其实，如果有一个VBA方法来删除字符串的每个实例[Survey_Checker.xlsm]在每个文件中的单元格范围A1：PE5中的工作表（称为DB输出）中，我觉得可能会工作？

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?

编辑：A1和PE之间的每一列5有一个参考forumla需要删除。

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

推荐答案

这是一个原始宏的版本，添加了代码来修复公式

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

这篇关于编辑多个工作簿的公式以删除外部引用/更改对本地工作簿的引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！