How to get a table data from a website which use POST method with VB Excel?

Question

I wanted to collect the data (name) from www.181.bh . This website use POST method and does not allow to change the search with help of URL.

I am using Excel VB Macro to collect the data with help of following code. I need to collect names from A to Z. For the code provided I used to get it with URL help, but since it use a POST method my macro cannot crawl in it.

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+n
'
    Dim ie As Object, continueLoop As Boolean
    Dim uRL As String
    Dim doc As Object, hDiv As Object, hRef As Object
    Dim hA As Object
    Dim aChars(1 To 26) As String
    Dim x As Long, y As Long, z As Long
    Dim wb As Excel.Workbook, ws As Excel.Worksheet
    Set wb = Excel.ActiveWorkbook
    Set ws = wb.ActiveSheet
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    aChars(1) = "A"
    aChars(2) = "B"
    aChars(3) = "C"
    aChars(4) = "D"
    aChars(5) = "E"
    aChars(6) = "F"
    aChars(7) = "G"
    aChars(8) = "H"
    aChars(9) = "I"
    aChars(10) = "J"
    aChars(11) = "K"
    aChars(12) = "L"
    aChars(13) = "M"
    aChars(14) = "N"
    aChars(15) = "O"
    aChars(16) = "P"
    aChars(17) = "Q"
    aChars(18) = "R"
    aChars(19) = "S"
    aChars(20) = "T"
    aChars(21) = "U"
    aChars(22) = "V"
    aChars(23) = "W"
    aChars(24) = "X"
    aChars(25) = "Y"
    aChars(26) = "Z"
    y = 1   'Column A in Excel
    z = 1   'Row 1 in Excel
    x = 1   'Start array
    continueLoop = True
     ie.navigate "http://www.181.bh/Surname?alpha=A", , , , "Content-Type: application/x-www-form-urlencoded" & vbCrLf
    Do While ie.busy: DoEvents: Loop
    Do While ie.ReadyState <> 4: DoEvents: Loop
    Set doc = ie.document
        Do
            Set hDiv = doc.GetElementById("NamesIndex")
            Set hRef = hDiv.GetElementsByTagName("a")
            For Each hA In hRef
                y = 1 ' Resets back to column A
                ws.Cells(z, y).Value = hA.innertext
                DoEvents
                z = z + 1
            Next hA
            If x < 26 Then
                x = x + 1
                uRL = "http://www.181.bh/Surname?alpha=" + aChars(x)
                ie.navigate uRL, , , , "Content-Type: application/x-www-form-urlencoded" & vbCrLf
                Do While ie.busy: DoEvents: Loop
                Do While ie.ReadyState <> 4: DoEvents: Loop
                Set doc = ie.document
            Else
                continueLoop = False
            End If
        Loop Until continueLoop = False
    ActiveWorkbook.Save
End Sub

Answer 1

There is a way to send a POST request with VBA using the Microsoft WinHTTP Services, version 5.1 library. You can even find examples already posted like this one or this one.

However, in your situation I would suggest instead to go with Selenium and use it to fill the textbox and then submit the request by pressing the button.

Here's how you could go about doing this by using the script execution method with Selenium:

Sub FillValueAndClick()

    Dim bot As New WebDriver
    bot.start "chrome", "http://www.181.bh/"
    bot.Get "/"

    Dim InputBoxValue As String
    InputBoxValue = "Test"

    Dim JavaScriptCode As String
    JavaScriptCode = "document.getElementById('ContentPlaceHolder1_txtName').value='" & InputBoxValue & "'"
    bot.ExecuteScript JavaScriptCode 

    bot.FindElementByName("ctl00$ContentPlaceHolder1$btnSubmit1").Click


End Sub

Note that you might need to update the Chrome driver located in C:\Users\YourUserName\AppData\Local\SeleniumBasic