Web Query: How to set URL to be the text of a cell ?

[Pawn_pt]

Hi there,

I don't know VBA, but wanted to create the following macro, in order to do a web query, being the url the text that I copy paste to cell B1, in this case it is BetExplorer Results - Sports results for soccer, hockey, but i've made it variable in order to get the data for several years, months & days.

How can I just "tell" Excel that the URL for the web query is at cell "B1" ?

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Previous.Select
Range("C1:D1").Select
Selection.Copy
ActiveSheet.Next.Select
Range("C1").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.betexplorer.com/results/tennis/?year=2017&month=01&day=10", _
Destination:=Range("$B$2"))
.Name = "?year=2017&month=01&day=10"
.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
Columns("C:C").EntireColumn.AutoFit
Columns("A:A").Select
Selection.ColumnWidth = 1
Range("A1").Select

Dim rs As Worksheet


For Each rs In Sheets
rs.Name = rs.Range("C1")
Next rs

Thanks in advance!

 

[Pawn_pt]

Got it :D

Did this:

Sub DayMinus1()
'
' DayMinus1 Macro
'


'
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Previous.Select
Range("B1:D1").Select
Selection.Copy
ActiveSheet.Next.Select
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Dim URL As String
Dim Day As String
URL = "http://www.betexplorer.com/results/tennis/?" & Range("D1").Value
Day = Range("D1").Value
With ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("$B$2"))
.Name = Day
.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
Columns("C:C").EntireColumn.AutoFit
Columns("A:A").Select
Selection.ColumnWidth = 1
Range("A1").Select

Dim rs As Worksheet


For Each rs In Sheets
rs.Name = rs.Range("C1")
Next rs


End Sub

The date is on C1, and the following formula on D1: ="year="&TEXT(YEAR(C1);"0000")&"&month="&TEXT(MONTH(C1);"00")&"&day="&TEXT(DAY(C1);"00")