May 27, 2021
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:
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"
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
If htmlim.innerText = "Invalid IBAN check digit!" Then
Sh.Cells(x, 2).Value = 52323432
Can you please help me with this? Thank you very much in advance.