Web Query in VBA Loop through tables on page to choose get tags from table instead of text

[Shloime]

I have a macro to get the last working day of last month, to check if it's a bank holiday (UK, England) I tried to do with a web query, there are a few issues with it
1) It takes the text which Doesn't have the year is there any way to get the date from the row in html table on web page?, <time datetime="2023-12-26">26 December</time>
2)there mast be a more efficient way with the query I want to get all tables for ENGLAND AND WALES
3)do you have any other vba code to check for bank holidays

Below is the code I have:

Dim DT As String
Dim lastWorkingDay As Date
Dim WS As Worksheet

Sub UpdateDate()
BankHoliday
For i = 1 To 88
lastWorkingDay = DateSerial(Year(Date), Month(Date), 1) - i
'lastWorkingDay = DateSerial(Year(Date), 5, 1) - 1
Do While Weekday(lastWorkingDay) = vbSaturday Or Weekday(lastWorkingDay) = vbSunday Or IsNumeric(Application.Match(lastWorkingDay, WS.ListObjects(1).ListColumns(1).DataBodyRange, 0))
lastWorkingDay = lastWorkingDay - 1
Loop
DT = Format(lastWorkingDay, "dddd d""" & GetSuffix(day(lastWorkingDay)) & """ MMMM yyyy")


Debug.Print DT
Next
End Sub
Function GetSuffix(day As Integer) As String
Select Case day
Case 1, 21, 31
GetSuffix = "st"
Case 2, 22
GetSuffix = "nd"
Case 3, 23
GetSuffix = "rd"
Case Else
GetSuffix = "th"
End Select
End Function
Sub BankHoliday()
Dim WB As Workbook
Set WB = Workbooks.Add ' name:="Temp.xlsx"
'WB.SaveAs (ThisWorkbook.Path & "\Temp.xlsx")
WB.Queries.Add Name:= _
"Upcoming bank holidays in England and Wales 2024", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(3) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(3) > TABLE." & _
"gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(3) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(3) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHe" & _
"aders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Queries.Add Name:= _
"Upcoming bank holidays in England and Wales 2025", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(4) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(4) > TABLE." & _
"gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(4) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(4) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHe" & _
"aders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Queries.Add Name:= _
"Upcoming bank holidays in England and Wales 2026", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(5) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(5) > TABLE." & _
"gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(5) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(5) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHe" & _
"aders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Queries.Add Name:= _
"Past bank holidays in England and Wales 2024", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(12) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(12) > TABL" & _
"E.gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(12) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(12) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.Promo" & _
"teHeaders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Queries.Add Name:= _
"Past bank holidays in England and Wales 2023", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(13) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(13) > TABL" & _
"E.gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(13) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(13) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.Promo" & _
"teHeaders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Queries.Add Name:= _
"Past bank holidays in England and Wales 2022", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(14) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(14) > TABL" & _
"E.gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(14) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(14) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.Promo" & _
"teHeaders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Queries.Add Name:= _
"Past bank holidays in England and Wales 2021", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(15) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(15) > TABL" & _
"E.gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(15) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(15) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.Promo" & _
"teHeaders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Queries.Add Name:= _
"Past bank holidays in England and Wales 2020", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(16) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(16) > TABL" & _
"E.gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(16) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(16) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.Promo" & _
"teHeaders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Queries.Add Name:= _
"Past bank holidays in England and Wales 2019", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(17) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(17) > TABL" & _
"E.gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(17) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(17) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.Promo" & _
"teHeaders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Queries.Add Name:= _
"Past bank holidays in England and Wales 2018", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.BrowserContents(""UK bank holidays"")," & Chr(13) & "" & Chr(10) & " #""Extracted Table From Html"" = Html.Table(Source, {{""Column1"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(18) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(1)""}, {""Column2"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(18) > TABL" & _
"E.gem-c-table.govuk-table > * > TR > :nth-child(2)""}, {""Column3"", ""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(18) > TABLE.gem-c-table.govuk-table > * > TR > :nth-child(3)""}}, [RowSelector=""SECTION[id='england-and-wales'] > DIV.app-c-calendar:nth-child(18) > TABLE.gem-c-table.govuk-table > * > TR""])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.Promo" & _
"teHeaders(#""Extracted Table From Html"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Day of the week"", type text}, {""Bank holiday"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
WB.Connections.Add2 _
"Query - Upcoming bank holidays in England and Wales 2024", _
"Connection to the 'Upcoming bank holidays in England and Wales 2024' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Upcoming bank holidays in England and Wales 2024;Extended Properties=" _
, """Upcoming bank holidays in England and Wales 2024""", 6, True, False
WB.Connections.Add2 _
"Query - Upcoming bank holidays in England and Wales 2025", _
"Connection to the 'Upcoming bank holidays in England and Wales 2025' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Upcoming bank holidays in England and Wales 2025;Extended Properties=" _
, """Upcoming bank holidays in England and Wales 2025""", 6, True, False
WB.Connections.Add2 _
"Query - Upcoming bank holidays in England and Wales 2026", _
"Connection to the 'Upcoming bank holidays in England and Wales 2026' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Upcoming bank holidays in England and Wales 2026;Extended Properties=" _
, """Upcoming bank holidays in England and Wales 2026""", 6, True, False
WB.Connections.Add2 _
"Query - Past bank holidays in England and Wales 2024", _
"Connection to the 'Past bank holidays in England and Wales 2024' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Past bank holidays in England and Wales 2024;Extended Properties=" _
, """Past bank holidays in England and Wales 2024""", 6, True, False
WB.Connections.Add2 _
"Query - Past bank holidays in England and Wales 2023", _
"Connection to the 'Past bank holidays in England and Wales 2023' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Past bank holidays in England and Wales 2023;Extended Properties=" _
, """Past bank holidays in England and Wales 2023""", 6, True, False
WB.Connections.Add2 _
"Query - Past bank holidays in England and Wales 2022", _
"Connection to the 'Past bank holidays in England and Wales 2022' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Past bank holidays in England and Wales 2022;Extended Properties=" _
, """Past bank holidays in England and Wales 2022""", 6, True, False
WB.Connections.Add2 _
"Query - Past bank holidays in England and Wales 2021", _
"Connection to the 'Past bank holidays in England and Wales 2021' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Past bank holidays in England and Wales 2021;Extended Properties=" _
, """Past bank holidays in England and Wales 2021""", 6, True, False
WB.Connections.Add2 _
"Query - Past bank holidays in England and Wales 2020", _
"Connection to the 'Past bank holidays in England and Wales 2020' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Past bank holidays in England and Wales 2020;Extended Properties=" _
, """Past bank holidays in England and Wales 2020""", 6, True, False
WB.Connections.Add2 _
"Query - Past bank holidays in England and Wales 2019", _
"Connection to the 'Past bank holidays in England and Wales 2019' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Past bank holidays in England and Wales 2019;Extended Properties=" _
, """Past bank holidays in England and Wales 2019""", 6, True, False
WB.Connections.Add2 _
"Query - Past bank holidays in England and Wales 2018", _
"Connection to the 'Past bank holidays in England and Wales 2018' query in the workbook." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Past bank holidays in England and Wales 2018;Extended Properties=" _
, """Past bank holidays in England and Wales 2018""", 6, True, False

'Windows("Book10").Activate
WB.Queries.Add Name:="Append1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Table.Combine({#""Upcoming bank holidays in England and Wales 2024"", #""Upcoming bank holidays in England and Wales 2025"", #""Upcoming bank holidays in England and Wales 2026"", #""Past bank holidays in England and Wales 2024"", #""Past bank holidays in England and Wales 2023"", #""Past bank holidays in England and Wales 2022"", #""Past bank holi" & _
"days in England and Wales 2021"", #""Past bank holidays in England and Wales 2020"", #""Past bank holidays in England and Wales 2019"", #""Past bank holidays in England and Wales 2018""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source" & _
""
Set WS = WB.Worksheets.Add
With WS.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Append1;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Append1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Append1"
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Shloime]

This worked but after importing the file from github I had to change in it

Private Function json_ParseObject(json_String As String, ByRef json_Index As Long) As Dictionary
    Dim json_Key As String
    Dim json_NextChar As String

    Set json_ParseObject = New Dictionary

to

Private Function json_ParseObject(json_String As String, ByRef json_Index As Long) As Object
    Dim json_Key As String
    Dim json_NextChar As String

    Set json_ParseObject = CreateObject("Scripting.Dictionary")

 

[Domenic]

Oh yes, that's right, since the JSON parser uses early binding.

I'm glad you were able to sort that out.

Cheers!

 

[Shloime]

I used the parse JSON for different projects,
I'm confused about how to work with the dictionary/collection, there are multiple layers in the JSON so the collection would have objects within objects within objects

 

[Domenic]

Yeah, it can get confusing. Whenever you need help, though, simply start a new thread and post your question there and someone will likely be able to help.

Cheers!