Update Nov2017
UPDATE: parse.com as now been closed and moved to parseplatform.org. I will update the content of this page an move the back-end to this platform
In parse.com – nosql database for VBA, I described how to use cParseCom to be able to use parse.com directly from VBA. You can find the implementation details here parse.com api class for VBA
Here’s a few examples – we’ll walk through the code shown below
Page Content
hide
The Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 |
Option Explicit 'v1.2 Private Sub firstTimeParseCom() Dim parseCom As cParseCom Set parseCom = New cParseCom With parseCom.init("ColorTable", , , "your app id", _ "your restapikey") .tearDown End With End Sub Private Sub populates() ' copy two sheets to parse.com populateFromSheet "VBAParseCustomers" populateFromSheet "VBAParseData" End Sub Private Sub populateFromSheet(sheetName As String) Dim parseCom As cParseCom, job As cJobject, dset As cDataSet ' this will clear out an existing parse class, and create a new one from a worksheet ' we'll use batch mode throughout Set parseCom = getParsed(sheetName).batch ' clear out existing any existing data parseCom.deleteObjects 'get the data from the sheet and populate database Set dset = New cDataSet With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True) For Each job In .children With parseCom.createObject(job) ' clear this error handling up Debug.Assert .isOk End With Next job ' clear up .tearDown End With ' commit any outstanding and clean up With parseCom.flush.batch(False) Debug.Assert .isOk ' show how many are there now Debug.Print .count & " in class" & sheetName .tearDown End With End Sub Private Sub testGetItemByUniqueId() ' get an item by unique object ID With getParsed("VBAParseCustomers") .getObjectById ("SmnyjZKs9m") ' test if it worked, and do something with the results If .isOk Then Debug.Print .jObject.stringify(True) Else Debug.Print "failed to get object:" & .browser.url & ":" & .browser.status & ":" & .browser.Text End If .tearDown End With End Sub Private Sub testparseUpdate() ' get some items by query and change the scheme name to something else With getParsed("VBAParseData").batch With .updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}")) ' test if it worked, and do something with the results If .isOk Then Debug.Print "all is good", .jObject.stringify Else Debug.Print "failed to update:" & .browser.url & ":" & .browser.status & ":" & .browser.Text End If End With .flush.tearDown End With End Sub Private Sub testparsequery() ' get a number of items that match a query by example With getParsed("VBAParseData") With .getObjectsByQuery(JSONParse("{'customerid':1}"), JSONParse("{'limit':2}")) 'test if it worked, and do something with the results If .isOk Then Debug.Print "all is ok", .jObject.stringify(True) Else Debug.Print "failed to do query:" & .browser.url & ":" & .browser.status & ":" & .browser.Text End If End With .tearDown End With End Sub Private Sub testparseCount() ' get a number of items that match a query by example Debug.Print getParsed("VBAParseData").count(JSONParse("{'customerid':1}")) End Sub Private Sub parseMatch() Dim pCustomer As New cParseCom, pData As cParseCom, job As cJobject, joc As cJobject, queryJob As cJobject ' look up data in another table based on data in another ' data abot the pantone colors of the year Set pCustomer = getParsed("VBAParseCustomers") Set pData = getParsed("VBAParseData") ' set up a query by example, restricting to a particular customer Set queryJob = New cJobject queryJob.init(Nothing).add "country", "United States" ' go through all matching customers With pCustomer.getObjectsByQuery(queryJob) If .isOk Then With .jObject.child("results") For Each job In .children With pData.getObjectsByQuery(job.child("customerid")) If .isOk Then With .jObject.child("results") For Each joc In .children Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value Next joc End With End If End With Next End With End If End With ' clean up queryJob.tearDown pCustomer.tearDown pData.tearDown End Sub Public Function getParsed(parseClass As String) As cParseCom Dim p As cParseCom Set p = New cParseCom Set getParsed = p.init(parseClass) End Function |
Initial authentication
Before you can start, you need to get your own credentials from parse.com. It’s free and easy. As described in parse.com – nosql database for VBA, once you have those you should register yourself on this PC. You won’t need to run this again after the first time.
1 2 3 4 5 6 7 8 |
Private Sub firstTimeParseCom() Dim parseCom As cParseCom Set parseCom = New cParseCom With parseCom.init("ColorTable", , , "your app id", _ "your restapikey") .tearDown End With End Sub |
1 2 3 4 5 6 |
Public Function getParsed(parseClass As String) As cParseCom Dim p As cParseCom Set p = New cParseCom Set getParsed = p.init(parseClass) End Function |
Populate from a sheet – walkthrough
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Private Sub populateFromSheet(sheetName As String) Dim parseCom As cParseCom, job As cJobject, dset As cDataSet ' this will clear out an existing parse class, and create a new one from a worksheet ' we'll use batch mode throughout Set parseCom = getParsed(sheetName).batch ' clear out existing any existing data parseCom.deleteObjects 'get the data from the sheet and populate database Set dset = New cDataSet With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True) For Each job In .children With parseCom.createObject(job) ' clear this error handling up Debug.Assert .isOk End With Next job ' clear up .tearDown End With ' commit any outstanding and clean up With parseCom.flush.batch(False) Debug.Assert .isOk ' show how many are there now Debug.Print .count & " in class" & sheetName .tearDown End With End Sub |
Getting credentials from the registry
Before we can do anything, we have to pick up the encrypted credentials we stored as a one off exercise. The should be one instance of cParseCom for every parse.com class – in this case I’m creating or updating a class with the same name as the sheet I’m importing .Adding .batch() will set this instance to batch Mode. That means that rest Queries will be automatically batched up as much as possible to minimize fetches.
1 |
Set parseCom = getParsed(sheetName).batch |
Deleting objects
Since we are populating sheets, we want to empty out the current contents. deleteObjects with no arguments will do that
1 |
parseCom.deleteObjects |
Getting Data
We can use cDataSet (see How to use cDataSet) to minimize playing around with spreadsheet contents. It also has a useful property (.JObject) which returns a useful JSON friendly cJobject (see How to use cJobject), each child of which represents a worksheet row, already in the correct format for createObject() to create a row in the parse.com class database.
1 2 3 4 5 6 7 8 9 10 11 12 |
'get the data from the sheet and populate database Set dset = New cDataSet With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True) For Each job In .children With parseCom.createObject(job) ' clear this error handling up Debug.Assert .isOk End With Next job ' clear up .tearDown End With |
Clearing up
As with most classes on this site, cParseCom features a .tearDown function which ensures that any memory it is using gets cleaned up. Since we using batch Mode, we have to flush, before exiting to clear any outstanding requests. Finally .count() will show how many rows are in the newly created table
1 2 3 4 5 6 7 |
' commit any outstanding and clean up With parseCom.flush.batch(False) Debug.Assert .isOk ' show how many are there now Debug.Print .count & " in class" & sheetName .tearDown End With |
Get an object by its unique ID – walkthrough
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Private Sub testGetItemByUniqueId() ' get an item by unique object ID With getParsed("VBAParseCustomers") .getObjectById ("3RGpNBMncB") ' test if it worked, and do something with the results If .isOk Then Debug.Print .jObject.stringify Else Debug.Print "failed to get object:" & .browser.url & ":" & .browser.status & ":" & .browser.Text End If .tearDown End With End Sub |
Each object (row) has a unique ID. Here’s how to get it (you’ll need to look at your parse.com dashboard to get a valid objectID for the data you’ve just loaded).
1 |
getParsed("VBAParseCustomers").getObjectById ("3RGpNBMncB") |
1 |
Debug.Print .jObject.stringify(True) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
{ "address":"584-5478 Et Road", "city":"Hastings", "company":"Eu Accumsan Sed Inc.", "coordinates":"38.2264, 75.04849", "country":"Comoros", "customerid":100, "email":"tincidunt.nibh@Curabitur.net", "name":"Contreras", "region":"NI", "zip":"12396", "createdAt":"2013-11-26T14:36:40.517Z", "updatedAt":"2013-11-26T14:36:40.517Z", "objectId":"SmnyjZKs9m" } |
Do a query- walkthrough
Queries in parse are by example. You can see the details here. You can set up any query by making a cJobject with the filters required. Here Im using JSONParse to create a jobject which will select all objects with customerid = 1.
1 |
.getObjectsByQuery(JSONParse("{'customerid':1}")) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Private Sub testparsequery() ' get a number of items that match a query by example With getParsed("VBAParseData") With .getObjectsByQuery(JSONParse("{'customerid':1}")) 'test if it worked, and do something with the results If .isOk Then Debug.Print "all is ok", .jObject.stringify(True) Else Debug.Print "failed to do query:" & .browser.url & ":" & .browser.status & ":" & .browser.Text End If End With .tearDown End With End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
{ "results":[ { "customerid":1, "quantity":9, "value":4230, "invoiceid":145, "date":{ "__type":"Date", "iso":"2013-10-08T00:00:00.000Z" }, "createdAt":"2013-11-26T14:36:43.286Z", "updatedAt":"2013-11-26T14:36:43.286Z", "objectId":"TXOAXukaLS" }, { "customerid":1, "quantity":3, "value":1560, "invoiceid":151, "date":{ "__type":"Date", "iso":"2013-05-21T00:00:00.000Z" }, "createdAt":"2013-11-26T14:36:43.430Z", "updatedAt":"2013-11-26T14:36:43.430Z", "objectId":"8IF3UNIy69" }, |
Counting objects – walkthrough
The .count() function will take the same arguments as getObjectsByQuery() and return the number of objects that match
1 2 3 4 5 6 |
Private Sub testparseCount() ' get a number of items that match a query by example Debug.Print getParsed("VBAParseData").count(JSONParse("{'customerid':1}")) End Sub |
Updating objects – walkthrough
The .updateObjects() function will take the same arguments as getObjectsByQuery() and return the objects that match, and then update any fields in those objects mentioned in the second argument; so this updates the customerId for all objects belonging to customerId 39 form 39 to 1.
1 |
.updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}")) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Private Sub testparseUpdate() ' get some items by query and change the scheme name to something else With getParsed("VBAParseData").batch With .updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}")) ' test if it worked, and do something with the results If .isOk Then Debug.Print "all is good", .jObject.stringify Else Debug.Print "failed to update:" & .browser.url & ":" & .browser.status & ":" & .browser.Text End If End With .flush.tearDown End With End Sub |
A small application – walkthrough
In this example, we’ll find any customers in United States, look up their orders, and print out some data about them.
We need 2 instances of cParseCom
1 2 |
Set pCustomer = getParsed("VBAParseCustomers") Set pData = getParsed("VBAParseData") |
Set up a query for US
1 2 |
Set queryJob = New cJobject queryJob.init(Nothing).add "country", "United States" |
Find Them
1 |
With pCustomer.getObjectsByQuery(queryJob) |
Look through each one
1 2 |
With .jObject.child("results") For Each job In .children |
Do a query on their orders
1 |
With pData.getObjectsByQuery(job.child("customerid")) |
Print out each of their orders
1 2 3 4 5 |
With .jObject.child("results") For Each joc In .children Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value Next joc End With |
Here’s the whole thing
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
Private Sub parseMatch() Dim pCustomer As New cParseCom, pData As cParseCom, job As cJobject, joc As cJobject, queryJob As cJobject ' look up data in another table based on data in another ' data abot the pantone colors of the year Set pCustomer = getParsed("VBAParseCustomers") Set pData = getParsed("VBAParseData") ' set up a query by example, restricting to a particular customer Set queryJob = New cJobject queryJob.init(Nothing).add "country", "United States" ' go through all matching customers With pCustomer.getObjectsByQuery(queryJob) If .isOk Then With .jObject.child("results") For Each job In .children With pData.getObjectsByQuery(job.child("customerid")) If .isOk Then With .jObject.child("results") For Each joc In .children Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value Next joc End With End If End With Next End With End If End With ' clean up queryJob.tearDown pCustomer.tearDown pData.tearDown End Sub |
United States Hammond 85 161
United States Nash 97 282
United States Nash 97 391
Get involved
See parse.com – nosql database for VBA for more on this topic
For help and more information join our forum, follow the blog or follow me on Twitter