Extract specific table part of the webpage in excel?

Question

I am new in Excel VBA/Macro

I need to grab the specific part of the page, not the full page. The bellow code works in complete page, but don't need the all parts of the page.

Sub GrabOutStandingTable()

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://dsebd.org/displayCompany.php?name=ABBANK", Destination:=Range( _
    "$A$1"))
    .CommandType = 0
    .Name = "displayCompany.php?name=ABBANK"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = """company"""
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
Sheets.Add After:=ActiveSheet
End Sub

The table part headed as "Other Information of the Company" is in lower part of the page, which is that I am talking about. The macro should extract this part.

Answer 1

XHR request:

You could do a much faster browserless XHR request and simply target the table of interest, which is at position 23, if you collect elements by their (non-unique) company id.

I use querySelectorAll method to grab the matching nodes and then extract the table at index 23.

Note the additional sponsor info that is displayed in then code output.

---

Webpage view:

---

Sample code output:

---

Code:

Option Explicit
Public Sub GetTable()
    Dim sResponse As String, hTable As Object, HTML As New HTMLDocument
    Application.ScreenUpdating = False
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://dsebd.org/displayCompany.php?name=ABBANK", False
        .send
        sResponse = StrConv(.responseBody, vbUnicode)
    End With

    sResponse = Mid$(sResponse, InStr(1, sResponse, "<!DOCTYPE "))
        With HTML
            .body.innerHTML = sResponse
            Set hTable = .querySelectorAll("#company")(23)
        End With
       WriteTable hTable
       Application.ScreenUpdating = True
End Sub

Public Sub WriteTable(ByVal hTable As HTMLTable, Optional ByVal startRow As Long = 1, Optional ByVal ws As Worksheet)

    If ws Is Nothing Then Set ws = ActiveSheet

    Dim tSection As Object, tRow As Object, tCell As Object, tr As Object, td As Object, R As Long, C As Long, tBody As Object
    R = startRow
    With ws
        Dim headers As Object, header As Object, columnCounter As Long
        Set headers = hTable.getElementsByTagName("th")
        For Each header In headers
            columnCounter = columnCounter + 1
            .Cells(startRow, columnCounter) = header.innerText
        Next header
        startRow = startRow + 1
        Set tBody = hTable.getElementsByTagName("tbody")
        For Each tSection In tBody               'HTMLTableSection
            Set tRow = tSection.getElementsByTagName("tr") 'HTMLTableRow
            For Each tr In tRow
                R = R + 1
                Set tCell = tr.getElementsByTagName("td")
                C = 1
                For Each td In tCell             'DispHTMLElementCollection
                    .Cells(R, C).Value = td.innerText 'HTMLTableCell
                    C = C + 1
                Next td
            Next tr
        Next tSection
    End With
End Sub

---

References:

VBE > Tools > References > HTML Object Library

Answer 2

The old Data > From Web won't handle this due to the way that website is structured. The data you need is nested deeply into other tables and consists of several tables.

Suggest using Power Query instead (no VBA required). Here is how to use Power Query in XL2013 for this. Use Excel's ribbon and find tab POWER QUERY.

1. Use menu option: POWER QUERY > From Web
2. From Web dialog displays. Enter your URL.
3. Click OK
4. The data you need is in Table 30. Find and click it, then click Load.

If we have XL2016 (office 365) we already have Power Query. If we have XL2010 or XL2013 we can download it from: https://www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=1441491e-917e-43de-8d6a-21f98287c3c2