Dynamic URL with QuertyTables.Add VBA

Question

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

Tim Williams · Accepted Answer · 2017-08-30 16:06:10Z

1

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"))

answered Aug 30, 2017 at 16:06

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bjefko Over a year ago

facepalm Thanks so much! So easy and it works great!

jsotola · Accepted Answer · 2017-08-30 16:10:40Z

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

Collectives™ on Stack Overflow

Dynamic URL with QuertyTables.Add VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related