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

nmualum

New Member
Joined
Jul 26, 2017
Messages
9
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,279
Office Version
  1. 2010
Platform
  1. Windows
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
 
Solution

nmualum

New Member
Joined
Jul 26, 2017
Messages
9
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

Well-known Member
Joined
Dec 23, 2017
Messages
2,279
Office Version
  1. 2010
Platform
  1. Windows
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:
VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,962
Messages
5,767,344
Members
425,405
Latest member
jaskimo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top