Excel VBA获取特定单元的超链接地址

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

如何编写Excel VBA来检索特定单元格中的超链接的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:

我正在编写我的工作簿的sheet2，它包含大约300行。每一行在“AD”列上都有一个唯一的超链接。我要做的是对列“J”中的每个空单元格进行循环，并将其值从空改为其列“AD”单元格的超链接URL。我目前正在使用这个代码:

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

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?

上面代码的问题是它总是得到第一个超链接的地址，因为代码是. hyperlinks (1). address。是否存在按范围地址获取超链接地址，比如sheet1.range("AD32"). hyperlink . address ?

3 个解决方案

#1

This should work:

这应该工作:

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

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.

这有点让人困惑，因为范围。地址和超链接完全不同。地址(即你的URL)，声明你的类型将会有很大帮助。这是另一个在模块顶部放置“选项显式”会有所帮助的例子。

#2

My understanding from the comments is that you already have set the column J to a string of the URL. If so this simple script should do the job (It will hyperlink the cell to the address specified inside the cell, You can change the cell text if you wish by changing the textToDisplay option). If i misunderstood this and the string is in column AD simply work out the column number for AD and replace the following line:

我的理解是，您已经将列J设置为URL的字符串。如果是这样的话，这个简单的脚本应该可以完成这项工作(它会将单元格超链接到单元格中指定的地址，如果您愿意，您可以通过更改textToDisplay选项来更改单元格文本)。如果我理解错了，字符串在AD列，只需计算出AD的列号，并替换如下行:

fileLink = Cells(i, the number of column AD)

The script:

脚本:

Sub AddHyperlink()

Dim fileLink As String

Application.ScreenUpdating = False

With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row


For i = 4 To lastrow

    fileLink = Cells(i, 10)

    .Hyperlinks.Add Anchor:=Cells(i, 10), _
    Address:=fileLink, _
    TextToDisplay:=fileLink

Next i

End With

Application.ScreenUpdating = True

End Sub

#3

Try to run for each loop as below:

试着为每个循环运行如下:

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

#1