I'm facing a problem. I open two separate applications of Excel, one application has a spreadsheet that will run a Macro (every 10 mins. or so) and the other application has a spreadsheet that links (I think via OLE) to the spreadsheet that is running the macro. If both spreadsheets are open in the same application of Excel, every time the macro runs it takes me away from the spreadsheet I am trying to work on and puts in the spreadsheet the macro is running on, interrupting me. Ideally, I'd like to have one excel application open and two spreadsheets - me being able to work on the one that links to the macro spreadsheet uninterrupted and the other running the macro in the background. My question is: Must I continue to use two separate applications of Excel or is there a way to get around this problem? Thanks a ton!!

Recommended Answers

All 4 Replies

If you can post the document, (strip it down if necessary) so I can see what is happening (I understand the problem, but can't seem to recreate it), I'll see what I can do.

Thanks for responding. I attached a copy of some code in a word document. I'm wondering if my problem is in the code I used to schedule how often the macro runs... Anyway, try this. Put the code in an Excel spreadsheet, get it to run. Then, open a new spreadsheet. Pretend you're working in that new spreadsheet until the macro runs again (in the other spreadsheet). The macro will start running in the spreadsheet you're working on, not the one it's supposed to run in. Ideally, I'd like to work on my new spreadsheet, without interruption, while the macro runs its schedule updating every 20 secs.
I really appreciate your help and time looking into this. Regards.

The problem lies not in the code you use to set the timer, but in the code you use to pull data from the web. Where you use With ActiveSheet.QueryTables.Add... , you're telling Excel to put the data in the currently active spreadsheet - whichever one you happen to be working on at the time the macro runs.

The fix is to refer to the appropriate spreadsheet by name, rather than using the ActiveSheet object. In fact, if you put your code directly into the spreadsheet that's supposed to be updated in the first place, you can use the following code snippet in place of the above-mentioned snippet:

With [B]Me[/B].QueryTables.Add(Connection:="URL;http://moneycentral.msn.com/investor/market/rates.asp", Destination:=Range("A1"))

Hopefully that will fix your problem!

- Sendoshin

Beat me too it.... Good job on that solution.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.