Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-01-2022, 06:13 PM
justa_guy_32405 justa_guy_32405 is offline Assist with a VBA URL use with variables Windows 7 64bit Assist with a VBA URL use with variables Office 2010
Novice
Assist with a VBA URL use with variables
 
Join Date: Jul 2022
Posts: 16
justa_guy_32405 is on a distinguished road
Default 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 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

Last edited by justa_guy_32405; 08-02-2022 at 11:26 AM. Reason: Solved
Reply With Quote
Reply

Tags
excel 2010, url, vba defined name

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Assist with a VBA URL use with variables 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:49 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft