QueryTable.Connection Property

[flaviu123]

Hello! Does anyone know how to adjust for Connection to navigate to the address that was under the hyperlink?

Sub GetHtmlLinck()

    Dim objWeb As QueryTable
    Dim sWebTable As String
    Dim vAddr As String
    
     'You have to count down the tables on the URL listed in your query
     'This example shows how to retrieve the 2nd table from the web page.
    sWebTable = 12
     'Sets the url to run the query and the destination in the excel file
     'You can change both to suit your needs
    ThisWorkbook.Sheets("Sheet4").Activate
    vAddr = ActiveCell.Hyperlinks(1).Address
    Set objWeb = ActiveSheet.QueryTables.Add( _
    Connection:="URL;vAddr", _
    Destination:=Range("C4"))
     
    With objWeb
        
        .WebSelectionType = xlSpecifiedTables
        .WebTables = sWebTable
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
    Set objWeb = Nothing
End Sub

 

[strooman]

Instead of this:

Set objWeb = ActiveSheet.QueryTables.Add(Connection:=[COLOR="#FF0000"]"URL;vAddr"[/COLOR], Destination:=Range("C4"))

Try this:

Set objWeb = ActiveSheet.QueryTables.Add(Connection:=[COLOR="#FF0000"]"URL;" & vAddr[/COLOR], Destination:=Range("C4"))

 

[flaviu123]

It works! Thanks very much! Nice day!