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

问题描述

如何编写 Excel VBA 代码以检索特定单元格中超链接的 url/地址?

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?

推荐答案

不知道为什么要搞大，代码很简单

Not sure why we make a big deal, the code is very simple

Sub ExtractURL()
    Dim GetURL As String
    For i = 3 To 500
        If IsEmpty(Cells(i, 1)) = False Then
            Sheets("Sheet2").Range("D" & i).Value = 
               Sheets("Sheet2").Range("A" & i).Hyperlinks(1).Address
        End If
    Next i
End Sub

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