CODEWITHSUNDEEP
vbaurldynamic
bjefko
bjefko

Reputation: 45

Dynamic URL with QuertyTables.Add VBA

I'm trying to download data from a weather website (weather.gov) and trying to use variables for the latitude and longitude in the URL. If I go to the website and switch out the latitude and longitude manually, I will be directed to the correct city's weather forecast. However, when I try to set the latitude and longitude as a variable and combine the part of the URL that doesn't change with the variables for each city, it give me a compile error, Expected: list separator or ). It keeps getting stuck on this section ("&lon=-") in the URL. I'm not sure if there's a better way to declare the variable or add them in, but it doesn't make much sense to me why it doesn't like that middle section. Code below. Thanks!

P.S. The cityLat and cityLong variables would be values based on the city, but for right now I just have the actual latitude and longitude just to test it with.

Sub forecast_weather()
    Dim cityLon As String
    Dim cityLat As String

    cityLat = "41.8781"
    cityLong = "87.6298"

    ActiveWorkbook.Worksheets("Chicago Weather").Select

    With ActiveSheet.QueryTables.Add(Connection:= _
         "URL;http://forecast.weather.gov/MapClick.php?lat="&cityLat&"&lon=-
         "&cityLong&"&unit=0&lg=english&FcstType=text&TextType=2", _
         Destination:=Range("$A$1"))
         .Name = "q?s=usdCAd=x_1"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .WebSelectionType = xlEntirePage
         .WebFormatting = xlWebFormattingNone
         .WebPreFormattedTextToColumns = True
         .WebConsecutiveDelimitersAsOne = True
         .WebSingleBlockTextImport = False
         .WebDisableDateRecognition = False
         .WebDisableRedirections = False
         .Refresh BackgroundQuery:=False
    End With
End Sub

Upvotes: 0

Views: 374

Answers (2)

jsotola
jsotola

Reputation: 2278

this gets Chicago weather

Sub forecast_weather()
    Dim cityLon As String
    Dim cityLat As String

    cityLat = "41.8781"
    cityLong = "-87.6298"

    With Worksheets("sheet1").QueryTables.Add( _
            Connection:="URL;http://forecast.weather.gov/MapClick.php?" _
            & "lat=" & cityLat _
            & "&lon=" & cityLong _
            & "&unit=0&lg=english&FcstType=text&TextType=2", Destination:=Range("$A$1"))
        .Name = "q?s=usdCAd=x_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166296

You need spaces around the &

With ActiveSheet.QueryTables.Add(Connection:= _
   "URL;http://forecast.weather.gov/MapClick.php?lat=" & cityLat & _
    "&lon=" & cityLong & "&unit=0&lg=english&FcstType=text&TextType=2", _
        Destination:=Range("$A$1"))

Upvotes: 1

Related Questions