Excel VBA获取特定单元格的超链接地址 [英] Excel VBA Get hyperlink address of specific cell

问题描述

我如何编写Excel VBA来检索特定单元格中的超链接的url / address？

How do I code Excel VBA to retrieve the url/address of a hyperlink in a specific cell?

我正在处理我的工作簿的sheet2，它包含关于300行。每行在AD列有一个唯一的超链接。我想要去的是在J列中的每个空白单元格上循环，并将其值从空白更改为AD单元格的超链接URL。我正在使用以下代码：

I am working on sheet2 of my workbook and it contains about 300 rows. Each rows have a unique hyperlink at column "AD". What I'm trying to go for is to loop on each blank cells in column "J" and change it's value from blank to the hyperlink URL of it's column "AD" cell. I am currently using this code:

do while....
    NextToFill = Sheet2.Range("J1").End(xlDown).Offset(1).Address
    On Error Resume Next
    GetAddress = Sheet2.Range("AD" & Sheet2.Range(NextToFill).Row).Hyperlinks(1).Address
    On Error GoTo 0
loop

上述代码的问题是它总是获得第一个超链接的地址，因为代码是 .Hyperlinks（1）.Address 。有没有办法通过范围地址获取超链接地址，例如可能 sheet1.range（AD32）。Hyperlinks.Address ？

Problem with the above code is it always get the address of the first hyperlink because the code is .Hyperlinks(1).Address. Is there anyway to get the hyperlink address by range address like maybe sheet1.range("AD32").Hyperlinks.Address?

推荐答案

这应该有效：

Dim r As Long, h As Hyperlink
For r = 1 To Range("AD1").End(xlDown).Row
    For Each h In ActiveSheet.Hyperlinks
        If Cells(r, "AD").Address = h.Range.Address Then
            Cells(r, "J") = h.Address
        End If
    Next h
Next r

这是一个有点混乱，因为Range.Address是完全不同于Hyperlink.Address（这是你的URL），声明你的类型将会有很多帮助。另一种情况是，将Option Explicit放在模块的顶部将有所帮助。

It's a bit confusing because Range.Address is totally different than Hyperlink.Address (which is your URL), declaring your types will help a lot. This is another case where putting "Option Explicit" at the top of modules would help.

这篇关于Excel VBA获取特定单元格的超链接地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！