vba 如何轻松更改 Excel 中文件的硬编码链接？

How do I easily change hardcoded links to a file in Excel?

提问by phan

I have a project where I maintain a list of all my students and their information in an Excel file labeled "BigList.xlsx". Then, I have about 40-50 other separate ancillary excel files that link to BigList by using VLOOKUP.

我有一个项目，我在一个标有“BigList.xlsx”的 Excel 文件中维护所有学生及其信息的列表。然后，我有大约 40-50 个其他单独的辅助 excel 文件，它们通过使用VLOOKUP.

For example, in cell A1 of an ancillary file you might see a formula that looks like this:

例如，在辅助文件的单元格 A1 中，您可能会看到如下所示的公式：

=Vlookup(B3, 
    'c:\documents and settings\user\desktop\[BigList.xlsx]Sheet1'!$a:$b000,
    2,false).

The vlookup link above references BigList.xlsx. However, I just realized that I need to change that file name to something else, like MasterDatabase.xlsm (notice the different extension). Is there an easy way to do this without having to manually go through all 40-50 files and doing a find & replace?

上面的 vlookup 链接引用了 BigList.xlsx。但是，我刚刚意识到我需要将该文件名更改为其他名称，例如 MasterDatabase.xlsm（注意不同的扩展名）。有没有一种简单的方法可以做到这一点，而不必手动浏览所有 40-50 个文件并进行查找和替换？

I think the basic idea is to change a hardcoded link into a dynamic one where I can change the filename of BigList.xlsx anytime, and not have to go back through all 40-50 files to update their links.

我认为基本的想法是将硬编码链接更改为动态链接，我可以随时更改 BigList.xlsx 的文件名，而不必返回所有 40-50 个文件来更新它们的链接。

回答by assylias

This should do what you require - maybe not super fast but if you only need to do it once on 50 workbooks it should be good enough. Note that the replace line should make the replacement in all the sheets of the workbook.

这应该可以满足您的要求 - 可能不是超快，但如果您只需要在 50 个工作簿上执行一次，它应该足够了。请注意，替换行应该在工作簿的所有工作表中进行替换。

Option Explicit

Public Sub replaceLinks()

    Dim path As String
    Dim file As String
    Dim w As Workbook
    Dim s As Worksheet

    On Error GoTo error_handler

    path = "C:\Users\xxxxxx\Documents\Test\"
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    file = Dir$(path & "*.xlsx", vbNormal)
    Do Until LenB(file) = 0
        Set w = Workbooks.Open(path & file)
        ActiveSheet.Cells.Replace What:="'THE_LINK_YOU_WANT_TO_CHANGE'!", _
                Replacement:="'THE_NEW_LINK'!", LookAt:=xlPart
        w.Save
        w.Close
        file = Dir$
    Loop

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    Exit Sub

error_handler:
    MsgBox Err.Description
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

回答by mischab1

You can do this in Excel 2010 without using any code. (If memory serves, it will also work in earlier versions of Excel.)

您可以在 Excel 2010 中执行此操作，而无需使用任何代码。（如果没记错的话，它也适用于早期版本的 Excel。）

1. Open all 50 ancillary excel files in Excel at the same time.
2. Open BigList.xlsx. (You now have 51 files open in Excel.)
3. Click File- Save Asand save BigList as MasterDatabase.xlsm
4. Close the new MasterDatabase.xlsm file.
5. Look at one of the ancillary files and verify that Excel has it pointed to the new file.
6. Close and save all files.

1. 同时在 Excel 中打开所有 50 个辅助 Excel 文件。
2. 打开 BigList.xlsx。（您现在在 Excel 中打开了 51 个文件。）
3. 单击File-Save As并将 BigList 保存为 MasterDatabase.xlsm
4. 关闭新的 MasterDatabase.xlsm 文件。
5. 查看辅助文件之一并验证 Excel 是否已将其指向新文件。
6. 关闭并保存所有文件。

回答by brettdj

This code will automate the link change directly

此代码将直接自动更改链接

1. Update your paths to BigList.xlsxand MasterDatabase.xlsmin the code
2. Update your path to the 40-50 files (I have used c:\temp\")
3. The code will then open both these files (for quicker relinking), then one by open the files in strFilePath, change the link from WB1 (strOldMasterFile ) to Wb2 (strOldMasterFile ), then close the saved file

1. 更新您的路径BigList.xlsx，并MasterDatabase.xlsm在代码
2. 更新 40-50 个文件的路径（我使用了 c:\temp\"）
3. 然后代码将打开这两个文件（为了更快地重新链接），然后打开 中的文件strFilePath，将链接从 WB1（strOldMasterFile）更改为 Wb2（strOldMasterFile），然后关闭保存的文件

Please note it assumes all these files are closed on code start, as the code will open these file

请注意，它假定所有这些文件在代码开始时都已关闭，因为代码将打开这些文件

    Sub ChangeLinks()
        Dim strFilePath As String
        Dim strFileName As String
        Dim strOldMasterFile As String
        Dim strNewMasterFile As String

        Dim WB1 As Workbook
        Dim WB2 As Workbook
        Dim WB3 As Workbook

        Dim lngCalc As Long    

        strOldMasterFile = "c:\testFolder\bigList.xlsx"
        strNewMasterFile = "c:\testFolder\newFile.xlsm"

        On Error Resume Next
        Set WB1 = Workbooks.Open(strOldMasterFile)
        Set WB2 = Workbooks.Open(strNewMasterFile)
        If WB1 Is Nothing Or WB2 Is Nothing Then
            MsgBox "One (or both) of " & vbnerwline & strOldMasterFile & vbNewLine & strNewMasterFile & vbNewLine & "cannot be found"
            WB1.Close False
            WB2.Close False
            Exit Sub
        End If
        On Error GoTo 0

        With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
            .EnableEvents = False
            lngCalc = .Calculation
            .Calculation = xlCalculationManual 
        End With

        strFilePath = "c:\temp\"
        strFileName = Dir(strFilePath & "*.xls*")

        'Error handling as link may not exist in all files
        On Error Resume Next
        Do While Len(strFileName) > 0
            Set WB2 = Workbooks.Open(strFilePath & strFileName, False)
            WB2.ChangeLink strOldMasterFile, strNewMasterFile, xlExcelLinks
            WB2.Save
            WB2.Close False
            strFileName = Dir
        Loop
        On Error GoTo 0

        WB1.Close False
        WB2.Close False

        With Application
            .DisplayAlerts = True
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = lngCalc
        End With

        End Sub