#1
|
|||
|
|||
Assist with a VBA URL use with variables
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 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 Last edited by justa_guy_32405; 08-02-2022 at 11:26 AM. Reason: Solved |
Tags |
excel 2010, url, vba defined name |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can't arrange 4 tables on a single page (Was:Can somebody assist please with a 2 second task) | Ivylodge | Word | 2 | 02-03-2022 03:19 AM |
Array to iterate through variables and trap blank variables | Marrick13 | Word VBA | 5 | 08-04-2015 06:19 AM |
Need assist to solve corrupted PST file | Rama2684 | Outlook | 1 | 01-22-2015 09:28 PM |
can word: make variables, find appropriate pages, fill out pages with variables, print only those | 20GT | Word VBA | 1 | 10-15-2014 09:48 PM |
Normal.dot Help? Fatally Corrupted; No Fix. Please Assist. | atm0073 | Word | 2 | 09-21-2009 04:02 AM |