I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

Since this is a ticker collection application we want this to run every now and again. At first I thought about using excel to schedule itself, but too many things generally go wrong with that, and we want to make this completely unattended. Let’s say every hour, 24 hours a day. 
We’ll use the windows task scheduler – and we’ll also put the workbook on Google Drive. That way, some PC somewhere can take care of updating, and any other PC can access the latest version, courtesy of google drive. 

Using windows scripting

We can write a simple windows script to open a workbook, run something, and save the updated file. It looks like this , and will take the argument of the workbook name, and the function to run


Dim args, o
Set args = WScript.Arguments
Set o = CreateObject("Excel.Application")

o.Workbooks.Open args(0)
o.Visible = false

o.Run args(1)


Next we’ll create a windows batch file, that calls it, with the appropriate arguments. 


cscript "C:\Users\Bruce\Google Drive\Under development\ramblings\updateBitcoin.vbs" "C:\Users\Bruce\Google Drive\Under Development\ramblings\bitCoinAuto.xlsm" "doBTCUpdates"


The point of doing all of this is so that we can run the thing automatically. For that we’ll use the windows scheduler. You can bring that up on windows 8 by typing “schedule tasks” at the metro interface. We create a task that will eventually look like this

with properties like this – running the .bat file we created.

set up like this.

Google Drive Synchronization

Without doing anything else, this will be synchronized to Google Drive. I’ve shared it out publicly, so you can access the latest update at the link here.

For more on this see. Data driven VBA apps with JSON