A problem implementing a delay within a for-each loop

**twills** · 06-18-2009, 05:56 PM

Hello everyone,

I have a small problem that I hope someone may be able to shine some light on for me

I'm trying to automate a process to download some financial data from a well known provider and I've managed to knock up a workbook that does this ok. (named Fully Working Worksheet.xls in the code below)

There are a number of data series (tickers) that I want to download and I have stored these in a range in my 'main' spreadsheet that the code below refers to.

When I pass the name of my data series into the workbook that downloads the data from the provider, this process can take some time, so I have tried to implement a short delay using the application.wait method so that the 'slave' spreadsheet has time to finish downloading the data. This doesn't seem to work however as it seems that the downloading process doesn't work when the procedure below is 'tied up' in the application.wait method.

Please Login or Register  to view this content.

I'm not entirely sure if the approach I've selected is doable, therefore would I'd be most grateful if anyone might be able to suggest a solution to the above problem.

Many Thanks in advance,
Twills

**shg** · 06-18-2009, 06:04 PM

See Help for the OnTime method. Basically, you want to schedule a 'resume' macro to run after a few seconds, and the just exit the current sub. Then you're not suspending Excel.