Pausing before macro

**dylanemcgregor** · 07-07-2009, 03:04 PM

I have a workbook that I need to

1) open at a specific time each day,
2) grabs some information from Bloomberg (updating takes about 15-20 seconds after opening the workbook)
3) and then run a macro after the updating is finished.

Right now I've:

1) created a batch file to open the file
2) set Windows Scheduled Tasks to run that batch file at 4:15pm
3) Created a workbook open event that calls my macro if the workbook is opened between 4:14pm and 4:17pm

What I'm looking for is the best way to pause before running my macro until the workbook has been open for say 30 seconds.

I have read through a lot of threads asking this question, but the threads have not been marked solved, and it seems that a lot of the proposed solutions have the problem of pausing all activity in the sheet, so the information from Bloomberg doesn't update while the macro is paused.

I'd appreciate any help on the best way to proceed.

Thanks,
Dylan

**shg** · 07-07-2009, 03:12 PM

See VBE Help for the OnTime method.

**dylanemcgregor** · 07-07-2009, 03:57 PM

Thanks shg, this is helpful. Can I ask if it makes more sense to put the

Please Login or Register  to view this content.

in my workbook open event, or in a new module, or somewhere else?

**shg** · 07-07-2009, 07:41 PM

In the open event, I reckon.

But in order for macros to run without a prompt to enable them (and without setting macro security to Low, which is an invitation to disaster), you need to sign the project. See Excel Help for Create your own digital certificate

**dylanemcgregor** · 07-08-2009, 05:39 PM

I haven't had a chance to string everything together and make sure it "works in the wild" but my test of just this chunk went well this afternoon. Hopefully nothing will break when I try the whole thing tomorrow.

I appreciate your help shg. I'm marking this thread as solved.