Microsoft Office Forums - View Single Post - [Solved] Assist with a VBA URL use with variables

justa_guy_32405 · 08-01-2022, 06:13 PM

I have a URL that in the sheet is put together with variables in 2 step method.. One cell I95 is the non-url with the variables, the second cell is I96 =HYPERLINK($I$95,"Visualcrossing Weather Data"), it works fine..

Code:

Cell I95
=CONCATENATE("https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/",$I$94,"/",$L$92,"-",$M$92,"-",$N$92,"/",$L$92,"-",$M$92,"-",$N$92,"?unitGroup=us&include=days&key=",$K$94,"&contentType=csv"

Cell I96 results in "https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/21220/1980-01-06/1980-01-06?unitGroup=us&include=days&key=EWLMYKEYNAR&contentType=csv" and it has a Defined Name of 'Visualcrossing_Weather_Data'

The objective is to utilize the finished hyperlink in vba to fetch the csv.. I found and started a code that used Define Name method.. but the method for building the query doesn't seem to be in E'10.. I had thought I could change the connection properties in the Defined Name.. It let me set it to "Name="Visualcrossing_Weather_Data", and I thought that I had found the early query edit mentioned..

The following code works with normal url, but I can't find any references of Defined name utilization..I may not be using the right search terms..

Code:

Sub GetWeatherData()
 
Dim str As String
 
'Delete existing data
Sheets("Weather_Import").Activate 'Name of sheet the data will be downloaded into. Change as required.
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
 
str = "https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/"$I$94"/"$L$92"-"$M$92"-"$N$92"/"$L$92"-"$M$92"-"$N$92"?unitGroup=us&include=days&key=",$K$94,"&contentType=csv" '[just url put here to show where the defined name str is thought to be defined]
QueryQuote:
            With Sheets("Weather__Import").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("Status_Import").Range("b3"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
 
Sheets("Weather_Import").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Weather_Import").Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)
 
Sheets("Weather_Import").Columns("A:B").ColumnWidth = 20
Range("B3").Select
 
 End Sub

Solved