We all started somewhere! Share your experience on the Get Advice: Let's Get Started Sweepstakes thread and be entered to win a $100 credit on your Constant Contact account.

How to - Pull current list of registrants into Excel Spreadsheet


How to - Pull current list of registrants into Excel Spreadsheet

This requires VBA-JSON (https://github.com/VBA-tools/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 = "https://api.constantcontact.com/v2/eventspot/events/" & 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 = "https://api.constantcontact.com/v2/eventspot/events/" & 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.




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

Jimmy D.
Tier II API Support Engineer
Developer Portal

View API documentation, code samples, get your API key.

Visit Page