How to - Pull current list of registrants into Excel Spreadsheet

[NCJTC]

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}"
            .send
        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}"
            .send
        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")
             
        
    Next
Next

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.

[Jimmy_D]

Hi @NCJTC,

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