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: 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 = ""

    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


    End Sub

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!