XML : Parse/Scrape column from table using MSHTML.HTMLDocument

I wrote this piece of code that scrapes the whole table from the webpage in the ULR var. I would like to only scrape/parse the column by the name "Extrapolated Vol". My html/xml is not strong so a solution along with an explanation would be appreciated!

https://services.tcpl.ca/cor/public/gdsr/GdsrNGTLImperial20151122.htm

Thanks

  Sub ExtractAlbertaAIL()    Application.ScreenUpdating = False  Application.EnableEvents = False    Dim URL As String  Dim Request As MSXML2.XMLHTTP  Dim doc As MSHTML.HTMLDocument  Dim tr As MSHTML.HTMLGenericElement  Dim td As MSHTML.HTMLGenericElement    Dim RowNumber As Integer  Dim ColNumber As Integer    ActiveWorkbook.Worksheets("Gas Day Summary").Range("A5:H10000") = ""    Set Request = CreateObject("msxml2.xmlhttp")  If Request Is Nothing Then    MsgBox "For some reason I wasn't able to make a MSXML2.XMLHTTP object"    Exit Sub  End If      URL = "https://services.tcpl.ca/cor/public/gdsr/GdsrNGTLImperial20151122.htm"  With Request      .Open "GET", URL, False      .send        Set doc = New MSHTML.HTMLDocument      doc.body.innerHTML = .responseText  End With        RowNumber = 1  For Each tr In doc.getElementsByTagName("table").Item(2).getElementsByTagName("tr")     ColNumber = 1      For Each td In tr.getElementsByTagName("td")          Worksheets("Gas Day Summary").Cells(RowNumber, ColNumber) = td.innerText          ColNumber = ColNumber + 1      Next td      RowNumber = RowNumber + 1  Next tr       Application.ScreenUpdating = True  Application.EnableEvents = True  End Sub    

No comments:

Post a Comment