带有查询表的动态URL.添加VBA

Question

我正在尝试从天气网站(weather.gov(下载数据，并尝试在URL中使用纬度和经度的变量。如果我去网站手动切换纬度和经度，我会被引导到正确的城市天气预报。然而，当我试图将纬度和经度设置为一个变量，并将URL中不变的部分与每个城市的变量相结合时，会出现一个编译错误Expected: list separator or )。它一直被URL中的("&lon=-")部分卡住。我不确定是否有更好的方法来声明变量或添加它们，但对我来说，为什么它不喜欢中间部分并没有多大意义。下面的代码。谢谢

附言：cityLat和cityLong变量将是基于城市的值，但目前我只有实际的纬度和经度来测试它。

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

Answer 1

您需要&周围的空间

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

Answer 2

这得到了芝加哥天气

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