vba Excel 中的完整超链接路径
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17490204/
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
Full Hyperlink path in Excel
提问by Andrei Doanca
I've got a column in an Excel file, and in it I have only values like 'ReleaseDoc' but they are all hyperlinks to something like this:
我在 Excel 文件中有一个列，其中只有“ReleaseDoc”之类的值，但它们都是指向以下内容的超链接：
\server1\folder1\subFolderA\subFolderB\SubFolderC\RealseaseDoc.doc
Now, what I want to do is to have in another column the path for the file, extracted from the hyperlink, and I've done that with this macro function
现在，我想要做的是在另一列中有从超链接中提取的文件路径，我已经用这个宏函数完成了
Function HLink(rng As Range) As String
If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function
in each cell I call this function with the appropriate cell from where I want to extract the address
在每个单元格中，我使用我想要从中提取地址的适当单元格调用此函数
The problem is that what I get displayed is something like this:
问题是我得到的显示是这样的：
../../../SubFolderB/SubFolderC/RealeasesDoc.doc
I don't see the full path to the docs, can anyone help with this vba function?
我没有看到文档的完整路径，有人可以帮忙使用这个 vba 函数吗？
PS: I've already searched the posts and found nothing like this
PS：我已经搜索了帖子，没有找到这样的
Thank you
谢谢
回答by Dick Kusleika
Excel will change the address to a relative address whenever it can. To prevent this, you need to set the Hyperlink Base property. In Excel 2010, click File - Info, Show More Properties if necessary, and enter a Hyperlink Base of C:.
Excel 会尽可能将地址更改为相对地址。为了防止这种情况，您需要设置 Hyperlink Base 属性。在 Excel 2010 中，单击文件 - 信息，如有必要，显示更多属性，然后输入 C: 的超链接库。
If there is no Hyperlink Base, it's assumed to be the path of the workbook. By setting it to C:, you force Excel to show you the whole path because it can't create a path relative to C:. If you make your Hyperlink Base \server\folder1\, you will get a relative path because Excel canmake a path relative to that.
如果没有 Hyperlink Base，则假定它是工作簿的路径。通过将其设置为 C:，您可以强制 Excel 向您显示整个路径，因为它无法创建相对于 C: 的路径。如果您创建超链接基\server\folder1\，您将获得一个相对路径，因为 Excel可以创建一个相对路径。
So if you want to guarantee full paths in Address, make the Hyperlink Base property a different share than the workbook or a different local drive than the workbook.
因此，如果您想保证 Address 中的完整路径，请将 Hyperlink Base 属性设为与工作簿不同的共享或与工作簿不同的本地驱动器。
回答by Mauro Brambilla
A solution is indicated here: https://support.microsoft.com/en-us/help/903163/how-to-create-absolute-hyperlinks-and-relative-hyperlinks-in-word-documents
此处指出了解决方案：https: //support.microsoft.com/en-us/help/903163/how-to-create-absolute-hyperlinks-and-relative-hyperlinks-in-word-documents
The link drives to MSWord support page however it works also for MSExcel (I verified it with MSExcel2010). Shortly two solutions are presented: the first applies to all documents while the second applies to a single document.
该链接指向 MSWord 支持页面，但它也适用于 MSExcel（我使用 MSExcel2010 进行了验证）。很快就提出了两种解决方案：第一种适用于所有文档，而第二种适用于单个文档。
