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.
Hi @NCJTC,
Thanks once again for posting some of your hard work to share with fellow users.
The holidays have come and gone. For many seasonal businesses, this means the rush of shoppers has decreased as well. Instead of turning off the lights and waiting for spring, make your email marketi...
See Article