Extract multiple html tables via VBA

Question

I've got a URL like this:

http://www.xyz342.net/abc/date_from=24.05.2018 00:00:00&date_to=24.05.2018 00:00:00&abc=2

I've used the following vba code to extract the table into excel:

Sub GetWebTable()
    With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.xyz342.net/abc/date_from=24.05.2018 00:00:00&date_to=24.05.2018 00:00:00&abc=2", Destination:=Range("a1"))
    .Refresh BackgroundQuery:=False
    .SaveData = True
    End With
End Sub

Task: I want to extract the table for each day of 2018 automatically until today. Therefore the date in that above given URL has to be changed every time (e.g. http://www.xyz342.net/abc/date_from=20.09.2018 00:00:00&date_to=20.09.2018 00:00:00&abc=2 gives the table for 20.09.2018). How can I do that?

Perfect would be a new worksheet for each day. Every future day should be automatically added.

Answer 1

This should give you some ideas in terms of generating the dates in a loop and concatenating into the URL the current date. It also demonstrates how to add new sheets. I think there are likely better scraping methods than generating queryTables like this. If you can share HTML for one link (And the lay out is the same for each day) it may be possible to devise a much better approach.

Following on from @Marcucciby2's comment you might also get startdate with something like: startDate = DateSerial(YEAR(Date), 1, 1)

Unless the historic tables are refreshed then you probably only want to run the below once. Then remove the loop and simply have the date generated from dateString = Format$(Date, "dd.mm.yyyy"), or Date-1 to get the prior day. You mention wanting it to be added automatically; You could tie to a change event that is linked to a cell where you select a date from a drop down.

Option Explicit
Public Sub test()
    Dim url  As String, startDate As Long, endDate As Long, i As Long, dateString As String

    startDate = DateValue("2018-01-01")
    endDate = CLng(Date)

    For i = startDate To endDate
        DoEvents
        dateString = Format$(i, "dd.mm.yyyy")
        url = "http://www.xyz342.net/abc/date_from=" & dateString & " 00:00:00&date_to=" & dateString & " 00:00:00&abc=2"
        AddQueryTable url, dateString
    Next
End Sub

Public Sub AddQueryTable(ByVal url As String, ByVal dateString As String)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets.Add
    ws.NAME = dateString
    On Error Resume Next
    With ws.QueryTables.Add(Connection:="URL;" & url, Destination:=ws.Range("a1"))
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
    On Error GoTo 0
End Sub