Excel Macro DO/LOOP not working after cells update from another application

**rodrigt** · 06-11-2010, 03:36 AM

Help please! I have a macro (below) that works OK when invoked manually for grabbing data from a few cells as they get automatically updated from another application. My objective is to detect when new data arrives and continuously update a historical data table for the last N data points. My problem is that I can’t get Do/Loop to do this automatically (as if the macro gets interrupted by the updates.)

In the code below I am downloading two variables-&-time/date (each updated at different time intervals).
-- The first two routines check if data is new and is so will update the historical data table.
-- “Sub DownloadUpdate()” works OK when I invoke it manually.
-- “Sub UpdateTable()” also works OK manually BUT ONLY ONCE, i.e. I can’t get Do/Loop to do this automatically

I sure would appreciate some guidance. (I’m pretty good with Excel but only a novice with macros.) Thanks guys,

Tom

PS – Here is my code:

Please Login or Register  to view this content.

[/CODE]

**snb** · 06-11-2010, 03:55 AM

You shouldn't delete (cut) the link to where tjhe data come form.
This suffices
Avoid activate, Select and copy in VBA

Please Login or Register  to view this content.

**rodrigt** · 06-11-2010, 08:23 PM

Pike wrote:
> Your first post does not comply with Rule 3 of our Forum RULES.... can you please go back and edit your first post.
-- My apologies, hope I followed the CODE procedures correctly.

SNB,
Thank you for your rapid reply! Being a VBA novice I'll need to digest your suggestions. During the weekend the external data I am attempting to capture into a table does not operate, thus I'll be back early next week.

**pike** · 06-11-2010, 08:39 PM

Thanks for taking the time to read and comply with the rule

.

**rodrigt** · 06-13-2010, 12:43 AM

Originally Posted by rodrigt

Thank you for your rapid reply! Being a VBA novice I'll need to digest your suggestions. During the weekend the external data I am attempting to capture into a table does not operate, thus I'll be back early next week.

Meanwhile I may have found guidance elsewhere (see below) to solve the problem, but as a macro novice I'm stumbling in the dark. The found guidance entails placing Macros in the Workbook (to run in the background) and a corresponding macro in the worksheet.

While the database I seek to continuously download from is down during the weekend my "manual workaround" seems to be working, BUT....

Help Please: How to control (e.g. Stop) a Macro that continuously runs in the background? I've attempted checking a "control cell" where I could enter "Stop" but that didn't really work - the background macro is still in effect. (What I've been doing to interrupt the macro is to invoke it in Editor mode and make a bogus entry in order to cause an error.) Nonetheless as my new Macro seems to be working manually, I would appreciate guidance on how to domesticate a macro that continuously runs in the background.

Thanks guys,

Tom

PS - Here is the guidance I found elsewhere:
Excel VBA Macro: How to Run a Macro at a Set Time (or continuously)...
http://www DOT ozgrid DOT com/Excel/run-macro-on-time.htm

The pertinent section for continuously running in the background (to execute code if the selected parameter changes) is:

Let's now suppose you want to run this macro (MyMacro) at 15 minute intervals after opening your Workbook. Again we will kick it off as soon as the Workbook Opens so right click on the Excel icon next to "File" and select "View Code". In here put;

Please Login or Register  to view this content.

Now in any Standard Module (Insert>Module) place this;

Please Login or Register  to view this content.

**pike** · 06-13-2010, 03:30 AM

Hi rodrigt,
Thanks for sharing and posting the solution.

**rodrigt** · 06-13-2010, 05:29 PM

Not quite solved yet

- How can I stop the continuously running macro? Last night I was able to stop it by entering bogus code into the macro, but somehow that is not working today... And if I shut down and re-start the worksheet the macro auto starts. Note - Per prior dialog (above), this macro "...entails placing Macros in the Workbook (to run in the background) and a corresponding macro in the worksheet."

I've tried Ctrl-Break and F8 but the macro keeps running and thus I can't debug my code. This problem gets worse when the Macro goes into "compile" and the error windows pop-up continuously. Any suggestions? Thanks.

PS - I'm also having trouble using If-Then-Else with End in one of the conditional statements, but that would be off-topic for this thread. Once I search this forum I may need to start a new thread.

**rodrigt** · 06-15-2010, 05:26 AM

The important thing for other users is that the above mentioned link for running a macro continuously really works! (As a novice I'm still figuring how to deal with it for stopping for editing... but it really works!)

Next I'll set this thread to "closed" - Thanks Pike!

Tom