vba Excel 超链接到带有 id 或命名锚点的网页位置

Excel hyperlink to web page location with id or named anchor

提问by Graham

In Excel, I can create a hyperlink to a web page. However, if the URL contains a # character, Excel always converts the hash sign to space-hyphen-space so the link no longer works. The URL http://www.example.com/page#locationbecomes http://www.example.com/page%20-%20location

在 Excel 中，我可以创建指向网页的超链接。但是，如果 URL 包含 # 字符，Excel 总是将井号转换为空格-连字符-空格，因此链接不再有效。URL http://www.example.com/page#location变为http://www.example.com/page%20-%20location

I have tried three ways

我试过三种方法

1. manually, right-click a cell, click "hyperlink" and type the URL in the address box

2. programatically, providing the full URL:

   Worksheets(1).Hyperlinks.Add Address:="http://www.example.com/page#location"

3. programatically, giving the location as a "subaddress":

   Worksheets(1).Hyperlinks.Add Address:="http://www.example.com/page", SubAddress:="location"

1. 手动，右键单击一个单元格，单击“超链接”并在地址框中键入 URL

2. 以编程方式，提供完整的 URL：

   Worksheets(1).Hyperlinks.Add Address:="http://www.example.com/page#location"

3. 以编程方式，将位置作为“子地址”：

   Worksheets(1).Hyperlinks.Add Address:="http://www.example.com/page", SubAddress:="location"

All three give the same problem. Is it possible to create a link to a specific location (id or named anchor) not just to a web page? I would be grateful for any help.

所有三个都给出了同样的问题。是否可以创建指向特定位置（id 或命名锚点）的链接，而不仅仅是指向网页？我将不胜感激任何帮助。

Note: the code examples are incomplete; for clarity, I have omitted the parameters Anchor and TextToDisplay.

注意：代码示例不完整；为清楚起见，我省略了参数 Anchor 和 TextToDisplay。

Excel version: 2010 (14.0.6126.5003) SP1

Excel 版本：2010 (14.0.6126.5003) SP1

回答by Graham

It appears to be a browser problem not an Excel problem. Switching from Google Chrome to Internet Explorer solved it.

这似乎是浏览器问题而不是 Excel 问题。从 Google Chrome 切换到 Internet Explorer 解决了这个问题。

Thanks everyone for your suggestions ... just saying that you didn't find the same problem was what prompted me to look elsewhere.

谢谢大家的建议......只是说你没有发现同样的问题是促使我寻找其他地方的原因。

回答by QA Collective

This is not a browser problem. MS Office applications are mangling URLs with a # being replaced with ' - ' (the %20 is a space).

这不是浏览器问题。MS Office 应用程序正在使用 # 替换为“-”（%20 是一个空格）来修改 URL。

This annoying problem is admitted to exist by Microsoft themselves. To quote from that page:

这个恼人的问题是微软自己承认存在的。从该页面引用：

These problems may occur when the name of file to which you create the hyperlink contains a pound sign (#).

NOTE: The pound sign is a valid character to use in a file name but is not accepted in hyperlinks in Office documents.

当您创建超链接的文件名称包含井号 (#) 时，可能会出现这些问题。

注意：井号是在文件名中使用的有效字符，但在 Office 文档的超链接中不被接受。

Their only workarounds are to remove the offending # or to copy and paste the URL from the document ... utterly ridiculous considering the reason most people would be using the # is to make it easierfor users to find content.

他们唯一的解决方法是删除有问题的 # 或从文档中复制和粘贴 URL ......考虑到大多数人使用 # 的原因是让用户更容易找到内容，这完全荒谬。

Request that Microsoft improve this behavior by voting here: https://office365.uservoice.com/forums/264636-general/suggestions/32273917-stop-mangling-urls-containing-a-so-we-can-link-t

通过在此处投票请求 Microsoft 改进此行为：https: //office365.uservoice.com/forums/264636-general/suggestions/32273917-stop-mangling-urls- contains-a-so-we-can-link-t

回答by JRHelgeson

I was able to get around this by using %23 in place of the # (hashtag|pound sign). My URL's now work within Excel 2013. The 'hovertext' shows the # symbol in the URL, but editing the link shows the %23.

我能够通过使用 %23 代替 #（标签|磅符号）来解决这个问题。我的 URL 现在可以在 Excel 2013 中使用。“悬停文本”在 URL 中显示 # 符号，但编辑链接会显示 %23。

http://www.example.com/page#locationdoes not work

http://www.example.com/page#location不起作用

http://www.example.com/page%23locationworks

http://www.example.com/page%23location作品

回答by Martin Sauriol

The best solution we have found, without manually changing registry entries, is to reset Internet Explorer to be the default browser. This resets the url, protocol and extension associations and resolves the issue.

我们找到的最佳解决方案是将 Internet Explorer 重置为默认浏览器，无需手动更改注册表项。这将重置 url、协议和扩展关联并解决问题。

Once you do that, even changing the default browser to Firefox and changing it back to IE will not break it again.

一旦你这样做了，即使将默认浏览器更改为 Firefox 并将其改回 IE 也不会再次破坏它。

This solution is "cleaner" than playing around the registry and has been logged as the official solution at our company.

此解决方案比在注册表中玩“更干净”，并且已被记录为我们公司的官方解决方案。

回答by Matthew B Milton

I had a coworker with this exact issue (including the %20-%20error) in Word 2010 instead of Excel.

我有一个同事%20-%20在 Word 2010 而不是 Excel 中解决了这个确切的问题（包括错误）。

Solved the following way:

通过以下方式解决：

In Internet Explorer:

在 Internet Explorer 中：

• Internet Options
• Programs tab
• HTML Editor: ensure this is not blank

• 互联网选项
• 程序选项卡
• HTML 编辑器：确保这不是空白

回答by Profex

Unfortunately, in Excel, I still ran into issues with safe URL encoded hyperlinks.

不幸的是，在 Excel 中，我仍然遇到了安全 URL 编码超链接的问题。

They would work once, then changed to a file://hyperlink after you clicked on it once, and was broken again.

它们会工作一次，然后file://在您单击一次后更改为超链接，然后再次损坏。

So I used the =Hyperlink()function and all was good because Excel can't change my concatenated URL string in the formula.

所以我使用了这个=Hyperlink()函数，一切都很好，因为 Excel 无法更改我在公式中连接的 URL 字符串。

Yay, I win...nope

是的，我赢了……不

When files are in protected view, due to security settingsor if the user is previewing the file as an attachment in Outlook, the #symbol still gets turned into %20-%20

当文件处于受保护视图时，由于安全设置或用户在 Outlook 中将文件作为附件预览，#符号仍会变成%20-%20

Since our IT department doesn't allowed us to install another browser, we cant make Internet explorer the default browser (since it already is).

由于我们的 IT 部门不允许我们安装其他浏览器，因此我们无法将 Internet Explorer 设为默认浏览器（因为它已经是）。

Comparing two PCs, one that I was able to change the default browser vs a standard load, I noticed that the following registry entry was missing from the standard load.

比较两台 PC，其中一台我能够更改默认浏览器与标准负载，我注意到标准负载中缺少以下注册表项。

Solution:Save asOffice Hyperlink Fix.reg(and double click)

解决方案：另存为Office Hyperlink Fix.reg（并双击）

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Windows\Shell\Associations\UrlAssociations\http]

[HKEY_CURRENT_USER\Software\Microsoft\Windows\Shell\Associations\UrlAssociations\http\UserChoice]
"Progid"="IE.HTTP"

After adding it to the standard load (required per every user) the links worked without any issues!

将其添加到标准负载（每个用户都需要）后，链接工作没有任何问题！

It's not all the registry entries required, but it was enough to get IE to ask to be the default browser again and add the rest (HTTPS...).

这并不是所有需要的注册表项，但足以让 IE 再次要求成为默认浏览器并添加其余的（HTTPS...）。

回答by Czymaupy

If the problem concerns links to the Share Point Server portals you can cut part of the hyperlink (in my case bolded part: Beginning_of_the_address/_layouts/15/start.aspx#/End_of_address).

如果问题涉及到 Share Point Server 门户的链接，您可以剪切部分超链接（在我的情况下，粗体部分：Beginning_of_the_address /_layouts/15/start.aspx#/ End_of_address）。

For me works fine (MSO 16 + SPS 2k16).

对我来说工作正常（MSO 16 + SPS 2k16）。

回答by Stepan1010

You can create a hyperlink in a cell just by typing in the URL and hitting enter. Excel will recognize it as a URL if it has things like

您只需输入 URL 并按 Enter 键即可在单元格中创建超链接。如果它有类似的东西，Excel 会将其识别为 URL

".com"

“.com”

and/or

和/或

"http://"

“http://”

I tried your problem and didn't get it. Maybe there is some other problem with your URL. You might want to post a more similar URL to the one you are actually using(same character types)I have excel 2010 as well.

我试过你的问题，但没有解决。也许您的网址存在其他问题。您可能想要发布一个与您实际使用的 URL 更相似的 URL（相同的字符类型）我也有 excel 2010。