vba 移动电子表格时 Excel 超链接会发生变化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9394443/
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
Excel Hyperlinks change when I move spreadsheet
提问by JC75
I have a spreadsheet in which I add hyperlinks to files using vba as follows:
我有一个电子表格，我在其中使用 vba 添加超链接到文件，如下所示：
Sheet1.Cells.Hyperlinks.Add Sheet1.Cells(1, 1), objFile.Path
This works fine. But if i move the spreadsheet which has the hyperlinks in it to another folder, all the hyperlinks change relative to the folder where I move the spreadsheet to.
这工作正常。但是，如果我将其中包含超链接的电子表格移动到另一个文件夹，则所有超链接都会相对于我将电子表格移动到的文件夹而更改。
Is there a way to stop this happening and fixing the hyperlinks path.
有没有办法阻止这种情况发生并修复超链接路径。
Thanks
谢谢
回答by Raystafarian
Try adding the hyperlink full path formularather than the hyperlink object
尝试添加超链接完整路径公式而不是超链接对象
Sub AddHyperlinkFormula()
Dim strMyPath As String, strMyFile As String, strName As String
strMyPath = "C:\Path\to\"
strMyFile = "Workbook.xlsx!"
strName= "Alt Text!"
ActiveCell.Formula = "=HYPERLINK(""" & strMyPath & strMyFile & """,""" & strName& """)"
End Sub
回答by iNeegma
The same thing drove me nuts.. '=hyperlink' is not always a choice since it has a 255 maxchar limitation. So the best solution is indeed to set the hyperlink base (which is workbook-specific). Can be done in two ways:
同样的事情让我发疯.. '=hyperlink' 并不总是一个选择，因为它有 255 个 maxchar 限制。所以最好的解决方案确实是设置超链接库（这是特定于工作簿的）。可以通过两种方式完成：
1) File -> Properties -> Summary Tab -> Hyperlink base
1) 文件 -> 属性 -> 摘要选项卡 -> 超链接库
2) or with vba ActiveWorkbook.BuiltinDocumentProperties(29)
2) 或使用 vba ActiveWorkbook.BuiltinDocumentProperties(29)
