How to - Pull current list of registrants into Excel Spreadsheet

Highlighted
Occasional Contributor

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}"
            .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.

1 REPLY 1
Highlighted
Moderator

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

Hi @NCJTC,

 

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


Regards,
Jimmy D.
Tier II API Support Engineer
Developer Portal

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

Visit Page

Product Updates: July 2020

Alongside introducing our new branding (check out that fresh new logo!), our teams were busy bringing you new updates to your Constant Contact account. Read on to find out what was new in your Constant Contact account during the month of July

Read More
Featured