How to open additional workbooks WITHOUT taking focus?

Ok, the problem I have is this.
I have a Workbook that is a 'summary' workbook.
This workbook opens several other workbooks, one at a time, writes values to them, reads values back from them, enters those values into the 'summary' workbook, and then saves & closes the individual workbook.
This is repeated perhaps 50 times or so, ideally I want to be able to start the macro and then do something else whilst Excel sits peacefully in the background doing it's thing.

However, it doesn't seem to want to! As soon as I open a new workbook the new workbook seems to get the focus, then instantly pass it back to the summary workbook, (which you can see by watching the buttons on the start bar if you step through the code).

The problem I am getting is that if I start the macro going, then go to another application and do something that involves me holding down the shift key for a period of time, then the Excel macro just stops. It doesn't stop on a particular line according to the VBA window, it just finishes, but it is only halfway through.

As an example to what happens I have the following code.
First create 2 blank excel workbooks in a folder, one called 'Main.xls' and one called '0 - Main.xls'
In 'Main.xls' insert a code module with the following:

Please Login or Register  to view this content.

The 'setup_init' macro will setup 50 workbooks to use as samples.

Then run the 'test' macro.
This will obviously just open each workbook, enter a value, and close it again (saving the change).

If you just let the macro run then all is fine, but if once you have started it you switch to another application, (I used Outlook), and then hold the left shift to select multiple messages MOST the time the Excel macro stops.

I can only think this is due to it somehow catching the 'shift' and that interrupting the macro somehow.

I know there is a VBA command that you can use to stop Excel accepting ANY keyboard or mouse input, but I don't like to use that because if something unexpected happens you have no way (that I know of) of getting back into Excel.

Any ideas?

I have not tried this, but perhaps

Please Login or Register  to view this content.

Along the same lines as SHG. The code you listed should be called from another workbook. We'll just call it Controller.xls. Controller.xls creates a separate instance of Excel, opens the workbook that does the processing in this instance, which in turn processes your many workbooks. The instance should have its Application.IgnoreRemoteRequests set to True and its Application.UserControl property set to False. This instance also runs invisibly in the background. As for losing an invisible session of Excel if something goes wrong, you provide callbacks to Controller.xls. Controller.xls can respond to any problems. This may sound overly complicated, but it is not. You are basically using Excel as an ActiveX.exe server which is similiar to a DLL except it runs in another process leaving you alone in your visible session unless Controller.xls is notified of problems and eventual successful completion of the task. If you are interested and will stick with me through several more posts, I'll walk you through it...

Sounds interesting, in particular the callbacks & notifications.

If you are willing to walk through it I'm more than happy to sit and take notes

Thankyou!

At my first read through that seems very informative and well written, thank you!
I shall try it out hopefully this evening and let you know how I get on, (once I have adjusted my VBA to run in this 'staggered' formation, (ie. using the 'controller' level).

I must admit I have not yet had a chance to implement this 'controller' scheme as I have been tied up on the main project.

However as an interesting note I stumbled across this today which seems to exactly describe the problem (and workaround) that I was having, (albeit that I didn't know thats where it was stopping!):

Macro in Excel Stops After A Workbook.Open Command

Just thought I'd link it in here as it is relevant