Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Thread Tools Display Modes
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
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..

Cell I95

Cell I96 results in "" 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..

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(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
str = ""$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]
            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
 End Sub


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

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 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. is not affiliated with Microsoft