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