Using VBA to look up EAN barcodes

17 Mar 2023

The API tutorial on our website covers many popular programming languages. But sometimes, you might want to use a less popular language and sometimes you might want to be a bit more sophisticated than simply getting the first result. Our EAN API is very flexible and can be used from almost any programming language.

Here is an example how to fetch the first 3 result pages of a product keyword search in Excel 2016 using VBA:

Sub ProductSearch()
    ' insert your own API token here
    Dim token As String: token = "abcdef"
    ' keywords to search for, replace space with plus sign
    Dim keyword As String: keyword = "macbook pro"
    keyword = Replace(keyword, " ", "+")
    
    Dim api_result As String
    ' page counter, get max 3 pages from API
    Dim page As Integer: page = 0
    Dim display_row As Integer: display_row = 0
    Dim xpath As String
    Dim product As String
    Dim ean As String
    ' set a number format that displays all EAN digits
    ActiveSheet.Columns("A").NumberFormat = "0000000000000"
    Do While page < 3
        api_result = Application.WorksheetFunction.WebService(
          "https://api.ean-search.org/api?token=" & token &
          "&language=1&op=product-search&name=" & keyword &
          "&page=" & page)
        xpath = "//ProductSearchResponse/product/"
        productList = WorksheetFunction.FilterXML(api_result, xpath & "ean")
        For i = LBound(productList) To UBound(productList)
            xpath = "//ProductSearchResponse/product[" & i & "]/"
            ean = WorksheetFunction.FilterXML(api_result, xpath & "ean")
            product = WorksheetFunction.FilterXML(api_result, xpath & "name")
            display_row = display_row + 1
            ActiveSheet.Range("A" & display_row).Value = ean
            ActiveSheet.Range("B" & display_row).Value = product
        Next
        If UBound(productList) < 10 Then Exit Sub
        page = page + 1
    Loop
End Sub

As you can see, we are setting the API to return results in XML instead of JSON and we use VBA's FilterXML() function to extract the information we need.

The next step would be to add your own logic what you want to do with the matching products.

We also provide Excel marcos to do barcode lookup directly from Excel.

To run this code, you need to get your own API token and insert it at the top of the code.

Update 16 Dec 2025:

We also provide libraries for many other programming languages.

Requesting product names in different languages >

EAN-Search Blog

Using VBA to look up EAN barcodes