使用vba更改excel超链接中的通配符 [英] changing a wildcard in an excel hyperlink with vba

问题描述

我想帮助删除/替换excel超链接中的通配符。
逻辑上看起来很容易，但它超出了我的能力。

I would like some help with removing/replacing a wildcard character in an excel hyperlink. Logically it seems very easy but it's beyond my abilities.

我有一个包含PDF文档超链接的电子表格。超链接包含＃字符，可以阻止文件路径工作。在超链接中我只需要将＃更改为％23并且链接有效。由于链接数量的原因，我不想手动执行此操作。有没有办法用VBA实现这一目标。改变文件路径似乎很容易，但是搜索超链接并改变＃似乎不可能。

I have a spreadsheet with hyperlinks to PDF documents. The hyperlinks contain the "#" character and that stops the file path from working. In the hyperlink I simply need to change the "#" to "%23" and the link works. I don't want to do this manually because of the amount of links. Is there any way of achieving this with VBA. It seems easy enough to change a file path but searching a hyperlink and changing the # doesn't seem to be possible.

所有超链接都在A列。

推荐答案

Excel对待＃左侧的文本为 .Address ，右侧为。 SubAddress - 因为它建议一个锚类型链接。您需要在每个链接上进行修复，如下所示：

Excel treats text to the left of the # as the .Address and to the right as the .SubAddress - as it suggests an anchor type link. You'd need to repair this on each link like so:

For Each lk In Sheets("YourSheetName").Range("A:A").Hyperlinks
    If lk.SubAddress <> "" Then
        lk.Address = lk.Address & "%23" & lk.SubAddress
        lk.SubAddress = ""
    End If
Next

这篇关于使用vba更改excel超链接中的通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！