Problem with VBA Macro to scrape and validate IBANs and BICs in Excel|VBA & Macros|Excel Forum|My Online Training Hub

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 = 52323432
End If
Next htmlim

Next htmlim
Next

End Sub

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