Showing results for 
Search instead for 
Did you mean: 

How to - Pull current list of registrants into Excel Spreadsheet

Occasional Contributor

How to - Pull current list of registrants into Excel Spreadsheet

This requires VBA-JSON (


In Excel, open Visual Basic (alt+F11). 

Select tools -> References and select Microsoft XML, v6.0


Create a new module and paste this. You can customize it as needed.

Public Sub GetRegistrations()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim strURL, strRURL, eID As String

eID =  {your event ID}
strEURL = "" & eID & "/registrants?limit=500&api_key={Your API Key}"

    Dim objrequest As New MSXML2.XMLHTTP60
        With objrequest
            .Open "Get", strEURL, False
            .setRequestHeader "Authorization", "Bearer " & "{Your Token}"
        End With
    strPostResponse = objrequest.responseText
    Dim eParsed As Dictionary
    Set eParsed = JsonConverter.ParseJson(strPostResponse)
For a = 1 To eParsed("results").Count

rID = eParsed("results")(a)("id")
strRURL = "" & eID & "/registrants/" & rID & "?api_key={Your API Key}"

Dim objrequest2 As New MSXML2.XMLHTTP60
        With objrequest2
            .Open "Get", strRURL, False
            .setRequestHeader "Authorization", "Bearer " & "{Your Token}"
        End With

    strPostResponse2 = objrequest2.responseText
    Dim rParsed As Dictionary
    Set rParsed = JsonConverter.ParseJson(strPostResponse2)

    Debug.Print JsonConverter.ConvertToJson(rParsed, Whitespace:=2)

    For b = 2 To rParsed("sections").Count
        Cells(LR + a + 1, 1).Value = eParsed("results")(a)("first_name")
        Cells(LR + a + 1, 2).Value = eParsed("results")(a)("last_name")
        Cells(LR + a + 1, 3).Value = eParsed("results")(a)("email")
        Cells(LR + a + 1, 4).Value = rParsed("sections")(3)("fields")(1)("value")  'these can be updated depending on your requirements
        Cells(LR + a + 1, 5).Value = rParsed("sections")(2)("fields")(6)("value")
        Cells(LR + a + 1, 6).Value = rParsed("sections")(2)("fields")(7)("value")
        Cells(LR + a + 1, 7).Value = rParsed("sections")(3)("fields")(2)("value")
        Cells(LR + a + 1, 8).Value = rParsed("sections")(3)("fields")(3)("value")
        Cells(LR + a + 1, 9).Value = rParsed("sections")(3)("fields")(6)("value")
        Cells(LR + a + 1, 10).Value = rParsed("sections")(3)("fields")(7)("value")

Range("M1").Value = "Last Updated " & Format(Now(), "MM/DD/YYYY")

Application.ScreenUpdating = True
Application.Calculation = xlCalculateAutomatic

End Sub

In this particular event, the rParsed sections look like this:

rParsed("sections")(1) is Custom1

rParsed("sections")(2) is Personal Information

rParsed("sections")(3) is Business Information

rParsed("sections")(4) is Custom2


I don't know if that will be true for every event. 


In my case, I have this running in a spreadsheet -- I call the sub on open, and I added a button to process it on demand.


Re: How to - Pull current list of registrants into Excel Spreadsheet



Thanks once again for posting some of your hard work to share with fellow users.

Jimmy D.
Tier II API Support Engineer
Still need help?
You can post a new message in the Community or find us on Twitter Mon-Fri 8am - 8pm ET. We've got real people waiting to help you out. Click below to start a conversation!