use a generated URL in VBA not a static URL

[StuartKStout]

I am having a problem pulling a URL that is generated in one area of a spreadsheet 'particular cell' and using it in some VBA code,
Typing the URL into the VBA statically works fine, but I dont seem to be able to get the VBA to use the generated URL

 

[Norie]

How are you trying to get/use the URL?

 

[StuartKStout]

At the moment its just statically typed



Sub gethtmltableurl()
Dim objWeb As QueryTable
Dim sWebTable As String

' Clearsheet
Sheets("URLs").Select
Range("F16:N76").Select
Selection.Clear
Range("G14:H14").Select

'You have to count down the tables on the URL listed in your query

sWebTable = 1

'Sets the url to run the query and the destination in the excel file

Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;https://www.swimmingresults.org/eventrankings/eventrankings.php?Pool=L&Stroke=1&Sex=M&TargetYear=S&AgeGroup=1718&AgeAt=D&StartNumber=1&RecordsToView=60&Level=N&TargetNationality=A&TargetRegion=P&TargetCounty=XXXX&TargetClub=XXXX", _
Destination:=Range("F16"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = sWebTable
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Set objWeb = Nothing

End Sub

 

[Norie]

How are you trying to use the value from the cell in the code?

 

[StuartKStout]

That is where I am stuck, how to pass the URL into the VBA. i have been trying Dim geturl As Hyperlink

 

[Norie]

What exactly is in the cell 'generating' the URL?

Is it just the URL as a string or is it an actual hyperlink?

 

[StuartKStout]

Hi Norie

Sorry for not being clear, in cell G2 I am using the formula =HYPERLINK(C4) so its the contents of G2 that I need to use. new to VBA and its a whole new world.

 

[Norie]

What happens with this code?

Sub gethtmltableurl()
Dim objWeb As QueryTable
Dim sWebTable As String
Dim strURL As String

    ' Clearsheet
    Sheets("URLs").Select
    Range("F16:N76").Select
    Selection.Clear
    Range("G14:H14").Select

    ' get URL from G2
    strURL = Range("G2").Value

    'You have to count down the tables on the URL listed in your query

    sWebTable = 1

    'Sets the url to run the query and the destination in the excel file

    Set objWeb = ActiveSheet.QueryTables.Add( _
                 Connection:="URL;" & strURL, _
                 Destination:=Range("F16"))

    With objWeb
        .WebSelectionType = xlSpecifiedTables
        .WebTables = sWebTable
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
    
    Set objWeb = Nothing

End Sub

 

[StuartKStout]

OMG, Perfect

Thank you very much.