Problem with VBA Macro to scrape and validate IBANs and BICs in Excel

  • Hi all,


    I have a list of IBANs in Excel and wish to validate them via scraping data through VBA from this website: https://www.iban.com/iban-checker. In column A (cells A2:A3000), I have the IBANs and in column B I wish to see whether they are valid or not based on the IBAN Checker website.


    It may be also worthwhile to extract/validate the BIC code in another column. Thus, would be glad to hear your opinion on this as well.


    I have the following VBA code for the IBAN validation for now, which is not working, unfortunately:


    Sub Iban()



    Application.ScreenUpdating = False




    Dim XMLPage As New MSXML2.XMLHTTP60


    Dim htmldoc As New MSHTML.HTMLDocument


    Dim htmlim As MSHTML.IHTMLElement


    Dim htmlims As MSHTML.IHTMLElementCollection


    Dim Sh As Worksheet


    Dim URL As String


    Dim sBody As String




    Dim Iban As String




    Set Sh = ThisWorkbook.Sheets("Sheet1")




    uf = Range("F" & Rows.Count).End(xlUp).Row



    ' Sh.Range ("f2:F" & uf).ClearContents



    URL = "https://es.iban.com/iban-checker"




    For x = 2 To Range("A" & Rows.Count).End(xlUp).Row




    Iban = Sh.Cells(x, 1).Value




    sBody = "iban=" & Iban




    XMLPage.Open "Post", URL, False


    XMLPage.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"


    XMLPage.setRequestHeader "X-Requested-With", "XMLHttpRequest"




    XMLPage.send sBody




    htmldoc.body.innerHTML = XMLPage.responseText




    Set htmlims = htmldoc.getElementsByTagName("strong")


    For Each htmlim In htmlims


    If htmlim.innerText = "VÁLIDO" Then


    Sh.Cells(x, 2).Value = 100


    End If


    If htmlim.innerText = "Invalid IBAN check digit!" Then


    Sh.Cells(x, 2).Value = 0


    End If


    Next htmlim




    Next htmlim


    Next



    End Sub



    Can you please help me with this? Thank you very much in advance.