trjhtr

Situation:

I am trying to download files, for the latest available month, from the webpage NHS Delayed Transfers of Care. I am unable to locate these files, within the HTML, with confidence that my current code will continue to work over time.

Question:

How do I isolate the latest month with confidence from the HTML and then download the associated files?

Target latest files (at time of writing):

Note: Latest month at time of writing is November 2017 - the date is related to the time period of the data contained within the file, not the actual current month. Lag for publication is c.2 months

Locating the latest files:

The website, at present, generally has the latest month files for download available after the third <h3> tag in the <div class="page-content" id="main-content">.

Approaches I have tried:

1) Hard coded the index (10) for the <p> tag item of interest, which is obviously not robust, in the following:

html.getElementsByTagName("p")(10).getElementsByTagName("a") 

Code:

Public Sub DownloadFiles()

 Dim http As New XMLHTTP60
 Dim html As New HTMLDocument

 With http
 .Open "GET", "https://www.england.nhs.uk/statistics/statistical-work-areas/delayed-transfers-of-care/delayed-transfers-of-care-data-2017-18/", False
 .send
 html.body.innerHTML = .responseText
 End With

 Dim posts As MSHTML.IHTMLElementCollection
 Dim post As MSHTML.IHTMLElement

 Set posts = html.getElementsByTagName("p")(10).getElementsByTagName("a") 'html.getElementById("main-content").getElementsByTagName("a")

 For Each post In posts

 post.Click

 Next post

End Sub

2) There doesn't seem to be a local className or id I can use to limit the elements returned. So I tried using a variable, holding the latest month, e.g. myStringVar = "November 2017", and testing for the presence of this within <a> tag elements' .innerText.

• The danger here is that there may be additional files, which I don't want to download, with this substring present.


• This also loops all <a> tags when I would like to be more targeted if possible.

Code:

Option Explicit

Public Sub DownloadFiles()

 Dim http As New XMLHTTP60
 Dim html As New HTMLDocument

 With http
 .Open "GET", "https://www.england.nhs.uk/statistics/statistical-work-areas/delayed-transfers-of-care/delayed-transfers-of-care-data-2017-18/", False
 .send
 html.body.innerHTML = .responseText
 End With

 Dim posts As MSHTML.IHTMLElementCollection
 Dim post As MSHTML.IHTMLElement

 Set posts = html.getElementById("main-content").getElementsByTagName("a")

 Dim targetMonth As String
 targetMonth = "2017-18 November"

 For Each post In posts

 If InStr(1, post.innerText, targetMonth) > 0 Then
 post.Click
 End If

 Next post

End Sub

I have listed a few of the references from the research I did at the bottom of this post. I think my lack of knowledge of HTML5 is hindering my ability to use the right search terms to find examples pertinent to my task hence my posting!

Here is a reduced version of the HTML. Apologies, I am not used to formatting HTML.

<div class="page-content" id="main-content">
 <nav class="breadcrumbs">
 <ul>
 <li><a href="/">Home</a></li>
 <li><a href="https://www.england.nhs.uk/statistics">Statistics</a></li>
 <li><a href="https://www.england.nhs.uk/statistics/statistical-work-areas/">Statistical work areas</a></li>
 <li><a href="https://www.england.nhs.uk/statistics/statistical-work-areas/delayed-transfers-of-care/">Delayed Transfers of Care</a></li>
 <li>Delayed Transfers of Care Data 2017-18</li>
 </ul>
 </nav>
 <header>
 <h1>Delayed Transfers of Care Data 2017-18</h1>
 </header>
 <article class="rich-text">
 <p></p>
 <p></p>
 <p></p>
 <p></p>
 <p>.</p>
 <p><a title="Delayed Transfers of Care" href="https://www.england.nhs.uk/statistics/delayed-transfers-of-care/">Further information for this collection, including the guidance document, can be found here</a></p>
 <hr>
 <h3>Time Series</h3>
 <p style="text-align: left"><a href="https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/01/DTOC-England-Timeseries-November-2017-2ayZP.xls" class="xls-link" onclick="ga('send', 'event', 'Downloads', 'XLS', 'https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/01/DTOC-England-Timeseries-November-2017-2ayZP.xls');">Delayed Transfers of Care Time Series (XLS, 123KB)</a></p>
 <h3>Statistical Press Notice</h3>
 <p><a</a></p>
 <hr>
 <ul>
 <li></li>
 <li></li>
 <li></li>
 </ul>
 <hr>
 <p><a href="https://www.england.nhs.uk/statistics/delayed-transfers-of-care/">Delayed Transfers of Care</a> </p>
 <h3>November 2017</h3>
 <p><a href="https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/01/LA-Type-B-November-2017-2ayZP.xls" class="xls-link" onclick="ga('send', 'event', 'Downloads', 'XLS', 'https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/01/LA-Type-B-November-2017-2ayZP.xls');">Total Delayed Days Local Authority 2017-18 November (XLS, 121KB)</a><br>
 <a href="https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/01/Trust-Type-B-November-2017-2ayZP.xls" class="xls-link" onclick="ga('send', 'event', 'Downloads', 'XLS', 'https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/01/Trust-Type-B-November-2017-2ayZP.xls');">Total Delayed Days Trust 2017-18 November (XLS, 205KB)</a><br>
 <a href="https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/01/Monthly-SITREPs-DTOC-Extracts-NOVEMBER-CSV-2017-18-Onwards-PO097.csv" class="csv-link" onclick="ga('send', 'event', 'Downloads', 'CSV', 'https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/01/Monthly-SITREPs-DTOC-Extracts-NOVEMBER-CSV-2017-18-Onwards-PO097.csv');">CSV Format Monthly Delayed Transfers of Care 2017-18 November (CSV, 4MB)</a>
 </p>
 <h3>October&nbsp;2017</h3>
 <h3></h3>

Additional references:

1. convert-html-table-to-excel-using-vba


2. getelementsbyclassname


3. get-href-value-from-specific-class-in-vba


4. vba-to-get-the-href-value


5. how-to-call-onclick-with-vba-in-html-code


6. getting-links-url-from-a-webpage-excel-vba

Instead of relying on an exact position in the website, you could process all the links based on their naming conventions.

Here are the steps that I would take:

• Process all the Anchor Tags on the page that contain the ####-## date pattern.


• Extract the Date from their InnerHTML


• Categorize them by keywords in their InnerHTML


• Click the post with the greatest date for each category

Option Explicit

Public Sub DownloadFiles()
 Const CSV_FORMAT = "CSV Format", LOCAL_AUTHORITY = "Local Authority", Trust = "Trust"

 Dim HRef As HTMLAnchorElement, html As New HTMLDocument, http As New XMLHTTP60
 Dim aDate As Date
 Dim lists As Object: Set lists = CreateObject("Scripting.Dictionary")

 lists.Add CSV_FORMAT, CreateObject("System.Collections.SortedList")
 lists.Add LOCAL_AUTHORITY, CreateObject("System.Collections.SortedList")
 lists.Add Trust, CreateObject("System.Collections.SortedList")

 With http
 .Open "GET", "https://www.england.nhs.uk/statistics/statistical-work-areas/delayed-transfers-of-care/delayed-transfers-of-care-data-2017-18/", False
 .send
 html.body.innerHTML = .responseText
 End With

 For Each HRef In html.getElementsByTagName("A")
 If HRef.innerHTML Like "####-##" Then
 aDate = getDate(HRef.innerHTML)
 If InStr(HRef.innerHTML, CSV_FORMAT) Then
 lists(CSV_FORMAT).Add aDate, HRef
 ElseIf InStr(HRef.innerHTML, LOCAL_AUTHORITY) Then
 lists(LOCAL_AUTHORITY).Add aDate, HRef
 ElseIf InStr(HRef.innerHTML, Trust) Then
 lists(Trust).Add aDate, HRef
 End If
 End If
 Next

 If lists(CSV_FORMAT).Count > 0 Then lists(CSV_FORMAT).GetByIndex(lists(CSV_FORMAT).Count - 1).Click
 If lists(LOCAL_AUTHORITY).Count > 0 Then lists(LOCAL_AUTHORITY).GetByIndex(lists(LOCAL_AUTHORITY).Count - 1).Click
 If lists(Trust).Count > 0 Then lists(Trust).GetByIndex(lists(Trust).Count - 1).Click
End Sub

Function getDate(Title As String) As Date
 Dim arr() As String, x As Long
 arr = Split(Title, " ")

 For x = 0 To UBound(arr)
 If arr(x) Like "*####-##*" Then
 On Error Resume Next
 getDate = DateValue(arr(x) & " " & arr(x + 1))
 On Error GoTo 0
 Exit Function
 End If
 Next
End Function

Here is how you can traverse over the Dictionary of SortedLists of HRefs.

PrintItems lists

Sub PrintItems(ParentDic As Object)
 Dim sList As Variant, Item As Variant, HRef As HTMLAnchorElement
 Dim x As Long
 For Each Item In ParentDic
 Set sList = ParentDic(Item)
 Debug.Print "Parent Dictionary Item: "; Item
 For x = 0 To sList.Count - 1
 Set HRef = sList.GetByIndex(x)
 Debug.Print HRef.HRef
 Next
 Next
End Sub