Refresh an excel spreadsheet on a scheduled basis

[Guest]

We subscribe to stock market data from a third party data provider. They have
an Excel extension which allows us to embed formulas which will pull in
current stock market data to a spreadsheet. I am building an excel
spreadsheet that will use these formulas to track data on a list of
companies. I want to refresh this data on a weekly basis using some kind of
automatic scheduler. My goal is to automate this process, so that every week
the spreadsheet updates itself.

What's the best way to automatically refresh the data in an excel
spreadsheet on a scheduled basis?

I'm using Excel 2000.

 

[Chip Pearson]

The best way is to create a Workbook_Open procedure in the ThisWorkbook code
module.

Private Sub Workbook_Open()
' any code you need to run
Application.CalculateFull
ThisWorkbook.Close SaveChanges:=True
End Sub

This code goes in the ThisWorkbook code module. Then use Windows Scheduler
to open the workbook at whatever interval you want.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

Great - this helps alot. To open the spreadsheet manually without this
launching, do I hold down "shift"?

Thanks again.

- Andy

 

[Chip Pearson]

Yes, if you hold down the SHIFT key when opening the workbook, its startup
procedures won't run.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)