Excel VBA - Delay auto run macro so user can bypass it?

[nmualum]

I typically work in Access, not Excel, so pardon my ignorance...
I have an Excel Workbook that is designed to pull in data from two different SQL Servers, that is intended to be automatically run in the middle of the night. This is done because it can take 5 minutes to run over our VPN, and the bosses are impatient. They want to be able to just open the workbook and see the updated data instantly.

So I've created the workbook, and put this code in the ThisWorkbook Module:

Private Sub Workbook_Open()
Call LoadPriceCheck ' this is the vba code that does the data pull from the two separate SQL servers...

End Sub

I have at the end of that module (LoadPriceCheck) this logic to save and close the workbook:

ThisWorkbook.Save
ThisWorkbook.Close ' this closes the workbook, but not Excel itself... how can I do that??

According to the documentation, if a user opens this workbook from within Excel, while holding down the SHIFT key, the autorun logic is supposed to be bypassed. That is not working for me.

So my dilemma is this:
I have an autorun process to pre-load the data to save the bosses time that works.
But when they open the spreadsheet to view the results, the autorun kicks in and reloads the data then closes the workbook before they can actually see the results. Defeating the purpose of the pre-run process...

So I have three options (that I can think of), some of them probably a bit off the wall.
(1) Try to add in some logic to the auto run step that would delay the auto-run for maybe 30 seconds. I don't want the workbook to just lock for those 30 seconds. I want the user to be able to click a button to abort the auto run steps... So when the task scheduler runs this in the middle of the night, it would start the workbook, wait the 30 seconds, sense that nobody has aborted it, and run the load process, save the workbook, and close the workbook. Then when the user comes in the next day, they would open the workbook, click on 'abort load process' button or something similar, and be able to fiddle with the pre-loaded results.

(2) I could write the data generated during the pre-load process to a sql table, then create a different workbook that would allow the bosses to load either all of the data, or select specific customers. There would then be two workbooks - one that loads/saves the data, and one for merely viewing (or manually reloading the data). That workbook would not have any auto run steps built in. It would also have a 'manually load data' button if they needed to manually refresh the data on demand. This approach has the advantage that if in the future I want to create an Access front-end with reporting capabilities, the data is already in a SQL table.

(3) Create a separate Excel workbook and link the cells in the new workbook to the pre-load workbook. I feel that this is using the power of Excel more efficiently.

As I said, I'm more comfortable in Access and VBA than in Excel. So I'm open to suggestions as to the best way to accomplish this task.

 

[offthelip]

Why not just check the time when workbook open runs and if it is between 1am an 6 am do the update otherwise just open

 

[nmualum]

Why not just check the time when workbook open runs and if it is between 1am an 6 am do the update otherwise just open

So in the auto run module, just add the time check?
Jeez, that sounds way too simple...
Thanks!

 

[offthelip]

you can do it in the workbook open sub which is better because it means you can run the loadpricecheck module at anytime if you want to: like this:

Private Sub Workbook_Open()
hr = Hour(Time())
If hr > 1 And hr < 6 Then
Call LoadPriceCheck ' this is the vba code that does the data pull from the two separate SQL servers...
End If
End Sub