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
updateBitcoin.vbs
Dim args, o
Set args = WScript.Arguments
Set o = CreateObject("Excel.Application")
o.Workbooks.Open args(0)
o.Visible = false
o.Run args(1)
o.ActiveWorkbook.Save
o.ActiveWorkbook.Close(0)
o.Quit
Next we’ll create a windows batch file, that calls it, with the appropriate arguments.
updateBitcoin.bat
cscript "C:\Users\Bruce\Google Drive\Under development\ramblings\updateBitcoin.vbs" "C:\Users\Bruce\Google Drive\Under Development\ramblings\bitCoinAuto.xlsm" "doBTCUpdates"
Scheduling
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