使用VBScript替换Excel中的超链接的一部分

Question

i有一排，数百个超链接指向一个已更改的路径。有人可以帮助我在Excel中创建VBScript。我发现了一些代码，但是它的工作不太好。我需要考虑一些条件，其中一些链接是正确的，不需要编辑，而我只需要进行一些编辑即可。这是三行的示例。

\US.MyCompany.netMain_FolderDATASub_folderafile1.pdf
\US.MyCompany.netMain_FolderDATASub_folderbfile1.pdf
\US.MyCompany.netMain_FolderDATASub_foldercfile1.pdf

我基本上需要编辑当前路径并立即添加一个名为newFolder的文件夹，因此看起来如下。

\US.MyCompany.netMain_FolderDATANewFolderSub_folderafile1.pdf
\US.MyCompany.netMain_FolderDATANewFolderSub_folderbfile1.pdf
\US.MyCompany.netMain_FolderDATANewFolderSub_foldercfile1.pdf

我到目前为止获得的代码可行，但仅适用于确切的搜索，即使存在，它也不会跳过。

当我运行代码几次时，即使存在，它也会继续添加newFolder。

Sub ReplaceHyperlinkAdresses()
Dim hypLink As Hyperlink
Dim ws As Worksheet
For Each ws In Worksheets
For Each hypLink In ws.Hyperlinks
If hypLink.Address Like "\US.MyCompany.netMain_FolderDATA*" Then
hypLink.Address =Replace
(hypLink.Address, "\US.MyCompany.netMain_FolderDATA",
"\US.MyCompany.netMain_FolderDATANewFolder")
End If
Next hypLink
Next ws
End Sub

---

Answer 1

Sub ReplaceHyperlinkAdresses()
Dim hypLink As Hyperlink
Dim ws As Worksheet
For Each ws In Worksheets
    For Each hypLink In ws.Hyperlinks
        If hypLink.Address Like "\US.MyCompany.netMain_FolderDATA*" AND _
           Not hypLink.Address Like "\US.MyCompany.netMain_FolderDATANewFolder*"Then
            hypLink.Address =Replace(hypLink.Address, _
                             "\US.MyCompany.netMain_FolderDATA", _
                             "\US.MyCompany.netMain_FolderDATANewFolder")
        End If
    Next hypLink
Next ws
End Sub