I figured it would be pretty nice to be able use one of the cloud based noSQL databases directly from VBA, so that I could share data easily between various platforms. On Excel Liberation , I recently released a few APIs to open up cloud based noSQL databases to VBA access to make this possible.
In this post, I’m going to use the VBA APIs for Google Apps Script ScriptDB and Parse.com to copy data between the two databases using VBA as the agent.
The APIs have very similar interfaces, so the coding is exactly the same once the connection has been made.
Here’s the main procedure.
The test will be to copy an entire class from parse.com to scriptDb and back again, and do a few counts and queries to compare before and after.
Private Sub scriptDBandParseCopy() ' copy from scriptDB to Parse Dim dbParse As cParseCom, dbScriptDb As cScriptDbCom, Class As String Class = "VBAParseCustomers" Set dbParse = getParsed(Class) Set dbScriptDb = getScriptDb(Class, "dbTest") ' copy from scriptdb to parse dbCopyAny dbScriptDb, dbParse ' see what we have Debug.Print dbParse.count Debug.Print dbParse.getObjectsByQuery(JSONParse("{'country':'Turkey'}")).jObject.stringify(True) ' copy back again dbCopyAny dbParse, dbScriptDb ' see what we have Debug.Print dbScriptDb.count Debug.Print dbScriptDb.getObjectsByQuery(JSONParse("{'country':'Turkey'}")).jObject.stringify(True) ' clean up dbParse.tearDown dbScriptDb.tearDown End Sub
And here’s the common copy procedure – which remains oblivious to the flavor of data base being used.
Private Sub dbCopyAny(dbSource As Object, dbTarget As Object) Dim jobSkip As cJobject, job As cJobject ' delete everything in target db of this class dbTarget.batch.deleteObjects 'we have to do it in chunks because of potential query limits Set jobSkip = JSONParse("{'skip':0}") ' we'll just use the default limit for a big query Do With dbSource.getObjectsByQuery(Nothing, jobSkip).jObject.child("results") If .children.count = 0 Or Not dbSource.isOk Or Not dbTarget.isOk Then Exit Do ' There are special reserved fields we need to delete between databases For Each job In .children dbTarget.createObject _ job.deleteChild("objectId").deleteChild("updatedAt").deleteChild("createdAt").deleteChild("siloId") Next job jobSkip.child("skip").value = jobSkip.child("skip").value + .children.count End With Loop ' clean up dbTarget.batch (False) End Sub
And here’s the result of the query – they match
100 { "status":"good", "count":3, "results":[ { "region":"SO", "zip":"25163", "country":"Turkey", "city":"Bridgwater", "customerid":50, "email":"aliquet.molestie.tellus@inaliquetlobortis.edu", "address":"Ap #157-9046 At, Rd.", "name":"Harris", "company":"Vulputate Eu Odio Limited", "coordinates":"65.15408, -59.50909", "objectId":"S321392559781" }, { "region":"Pays de la Loire", "zip":"55125", "country":"Turkey", "city":"La Roche-sur-Yon", "customerid":63, "email":"quis@parturientmontesnascetur.edu", "address":"Ap #433-7194 Ante St.", "name":"Jacobs", "company":"Vestibulum Company", "coordinates":"23.12752, -77.15718", "objectId":"S321468256370" }, { "region":"Northern Territory", "zip":"5199", "country":"Turkey", "city":"Palmerston", "customerid":91, "email":"condimentum.Donec.at@Morbi.com", "address":"251-8346 Vestibulum, Av.", "name":"Reeves", "company":"Mi Tempor Lorem Incorporated", "coordinates":"-80.71595, -106.84", "objectId":"S321468256341" } ] }
Here’s a couple of primer decks to get some background on how all this works.
For more like this see Excel Liberation.