VBA超链接名称和地址

Question

我试图将excel电子表格中的所有超链接拉到新的工作表中。我希望列A显示来自超链接的文本，列B显示超链接地址。

我已经写了下面的代码，所有的B列工作得很好，但是在A列的值不是全部过来，他们不匹配在B列的超链接地址。我做错了什么?

Thanks in advance

Sub extract_links()
    Dim hyp As Hyperlink
    Dim ReadCols As Long
    Dim ReadWriteRow As Long
    ReadWriteRow = 1
    ReadCols = 6
ActiveWorkbook.Sheets(2).Range("a:b").Clear
For c = 1 To ReadCols
    For Each hyp In ActiveWorkbook.Sheets(1).Columns(c).Hyperlinks
        ActiveWorkbook.Sheets(2).Range("a" & ReadWriteRow).Value = ActiveWorkbook.Sheets(1).Cells(ReadWriteRow, c).Value
        ActiveWorkbook.Sheets(2).Range("b" & ReadWriteRow).Value = hyp.Address
        ReadWriteRow = ReadWriteRow + 1
    Next
Next c
End Sub

Answer 1

这次你需要改变这个:

ActiveWorkbook.Sheets(2).Range("a" & ReadWriteRow).Value = ActiveWorkbook.Sheets(1).Cells(ReadWriteRow, c).Value

这:

ActiveWorkbook.Sheets(2).Range("a" & ReadWriteRow).Value = hyp.Range.Value