使用 VBA 更改工作表而不是工作簿中的源链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28588857/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using VBA to change source links within sheets instead of workbook
提问by Thomas Almond
I'm currently attempting to write some VBA into a userform that will allow me to change say all the external links in sheet1 to workbookA, then all the external links in sheet2 to workbookB, then all in sheet3 to workbookC etc. etc.
我目前正在尝试将一些 VBA 写入用户表单，这将允许我将 sheet1 中的所有外部链接更改为 workbookA，然后将 sheet2 中的所有外部链接更改为 workbookB，然后将 sheet3 中的所有外部链接更改为 workbookC 等等。
I've used this code to do all the links in the workbook
我已使用此代码完成工作簿中的所有链接
Private Sub btnUpdate_Click()
Dim Source As String
Application.ScreenUpdating = False
currentsource = ActiveWorkbook.LinkSources(xlExcelLinks)
'This is just a record of the changing source files
Sheets("Notes").Range("C2") = currentsource
'txtDirectory is the new source location chosen in my userform
Sheets("Notes").Range("C3") = txtDirectory
Source = Sheets("Notes").Range("C2")
ActiveWorkbook.ChangeLink Name:=Source, NewName:=txtDirectory, Type:=xlExcelLinks
Application.ScreenUpdating = True
End Sub
This works perfectly, however I now need to adapt it to change the links depending on the sheet I'm in.
这非常有效，但是我现在需要调整它以根据我所在的工作表更改链接。
I've done a lot of googling to no avail, I've also spent quite a bit of time with my head in my VBA books.
我已经做了很多谷歌搜索无济于事，我也花了很多时间在我的 VBA 书籍中。
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by Kyle
The code below will print the name of each link in the workbook to the immediate window. From there you should be able to write logic around which links to deal with based on sheet name.
下面的代码将工作簿中每个链接的名称打印到直接窗口。从那里您应该能够根据工作表名称围绕要处理的链接编写逻辑。
Sub link()
Dim i As Integer
Dim x As Variant
x = ThisWorkbook.LinkSources(xlLinkTypeExcelLinks)
For i = 1 To UBound(x)
Debug.Print x(i)
Next i
End Sub
回答by Patrick Lepelletier
i use a similar code to do stuff, you will need to adapt this piece of code :
我使用类似的代码来做事，你需要修改这段代码：
Dim Sh As Worksheet
with Thisworkbook
For Each Sh In .Worksheets
For q = 1 To Sh.Hyperlinks.Count
h = Sh.Hyperlinks(q).Address
If Left(h, Len(.Path)) <> Left(.Path, Len(.Path)) Then
If InStr(1, h, "\OBJETS\") > 0 Then 'obtenir le nom du fichier sans chemin
h = Mid(h, InStrRev(h, "\OBJETS\", Len(h)) + 1, Len(h))
ElseIf InStr(1, h, "\Règles\") > 0 Then 'obtenir le nom du fichier sans chemin
h = Mid(h, InStrRev(h, "\Règles\", Len(h)) + 1, Len(h))
Else
h = Mid(h, InStrRev(h, "\", Len(h)) + 1, Len(h))
End If
Sh.Hyperlinks(q).Address = Replace(Replace(.Path, "\OBJETS", ""), "\Règles", "") & "\" & h
End If
Next q
Next Sh
Set Sh = Nothing
End With
