Google Docs to Excel Asynchronously

A different approach.

An example

Private Sub smallTest()
    ' you have to add your callbacks in a class as below
    Dim callbacks As yourCallbacks, load As cPromise
   
    ' in case anything old async remnants are hanging around
    clearRegister
   
    ' whatever you will be calling back should be defined here
    Set callbacks = New yourCallbacks
   
    ' add a playing worksheets if not already there
    addPlaySheets Array("rest")
   
    ' we'll pass the range so that it gets carried forward when resolved
    Set load = loadData("https://ajax.googleapis.com/ajax/services/search/patent?v=1.0&rsz=8&q=mouse", Range("rest!a1"))
   
    ' and then do these things when it's all over
    load.done(callbacks, "populate") _
        .done(register, "tearDown") _
        .fail callbacks, "show"


    ' when we get here, all the previous activity is still probably going on.
    Debug.Print "i could be doing something else while Im waiting"
   
End Sub

Something more complicated

This time we’ll go to Google Spreadsheets, read the schema of a workbook, then retrieve each of the worksheets in that workbook at the same time. Many web services, Google Docs included, will fail if too many requests come at once or if they are busy. So in addition, exponential backoff is also implemented. All this is orchestrated through promises. Here is the calling procedure.

Option Explicit
' example application using promises to get multiple data streams from google docs
Public Sub asynchDocs()
    Dim url As String, key As String
    key = "0At2ExLh4POiZdFd0YUhpZVRPUGxFcW85X2xkMm1vY2c"
    url = "https://spreadsheets.google.com/feeds/worksheets/" + key + "/public/basic?alt=json"
    
    ' kick this off in the background
    doDocsWithBackoff url
    '
    ' we can go and do something else now
    Debug.Print "Im doing something else - have a nice day"
    
End Sub
Public Sub doDocsWithBackoff(url As String)
    Dim b As cBackoff, p As cPromise, prs As cPromise, _
        process As cDocsImporter, doneSchema As cDeferred, doneSheets As cDeferred
        
    ' always to this for a fresh start - it will clear up any memory/timers from previously
    clearRegister
    
    ' this class knows how to get google spreadsheet
    Set process = New cDocsImporter
    Set doneSchema = New cDeferred
    
    ' first phase, we get the schema, and release up memory
    Set b = New cBackoff
    b.execute(url) _
        .done(process, "getSchema", doneSchema) _
        .fail process, "show"
    
    ' although this will be done synchronously, using a promise means we can know its done
    Set prs = deleteAllTheSheets
    
    ' we'll have resolved doneSchema when the previous .done is executed
    ' we'll need another deferred to tell us when all this is over
    Set doneSheets = New cDeferred
    when(Array(prs, doneSchema.promise)) _
        .done(process, "getSheets", doneSheets, url) _
        .fail process, "show"


    ' now we can clear everything up
    doneSheets.promise _
        .done(register, "teardown") _
        .done(process, "teardown") _
        .fail process, "show"


End Sub

More information

For more information on how this is implemented, or to download the workbook see Excel Liberation. This is fairly extreme VBA so this early version will be a little fragile and buggy Check back often for new versions.

Plugins classes

So far I’ve created event driven timers, getting data from a web service and asynchronous ADO operations. I may add others over time, and welcome any contributions you make. The details of how to create these and the code for the promises structure can also be found on the Excel Liberation site.