VBA: Data from URL on worksheet

[thomasje1234]

Hi guys,

I'm trying to get data from a URL, which is working. But I would like to be able to change the URL without having to change the VBA code. So for example: If I paste the URL in cell B3, how can I get the VBA code to get the data from the URL in that cell?

The part of the code that works looks like this:

Sheets("Sheet1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.bom.gov.au/jsp/ncc/cdio/weatherData/av?p_nccObsCode=139&p_display_type=dataFile&p_startYear=&p_c=-326660605&p_stn_num=040412" _
, Destination:=Range("$A$1"))
.Name = _
"av?p_nccObsCode=139&p_display_type=dataFile&p_startYear=&p_c=-326660605&p_stn_num=040412"
.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



So the question is: How can I change that piece of code, so that it gets the data from the URL in a cell on sheet1?

It sounds like it shouldn't be too hard, but I can't find a solution. Thanks for reading this

Kind regards, Thomas

 

[Gary McMaster]

Hi Thomas,

Welcome aboard!

Try the following with the URL in B3:

Public Sub Test()

Sheets("Sheet1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;"[COLOR=#ff0000] & ActiveSheet.Range("B3").Text & ""[/COLOR], Destination:=Range("$A$1"))
.Name = _
"av?p_nccObsCode=139&p_display_type=dataFile&p_startYear=&p_c=-326660605&p_stn_num=040412"
.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

 

[thomasje1234]

Thanks a lot Gary! That worked perfectly!