getting VBA to pause while external data loads from web

[vladiator]

Hi,

A quick question for you VBA gurus
Say I have a program that goes through a loop and downloads web site
into an excel spread sheet, once the contents (news releases) ar
there, I read off the time stamps and the headlines.
I used to have this done in two stages where I first ran a shell scrip
job to download the sites and save them locally in separate files -
then had a VBA routine run through them to get the info.
I'm now trying to do both steps within the same program and the proble
comes up when the page is still loading and the code runs further - i
already tries to read but there's still nothing there.
Is there any way I can get it to wait till the external data i
loaded?

Here's a snippet of the code:


'within the outside loop not seen here, I go through the tickers an
the dates and specify the URL to go to as a function of this inf
("CompleteAddress" variable below)

Wit
ActiveWorkbook.Worksheets("WebSiteContents").QueryTables.Add(Connection:="URL;
& CompleteAddress
Destination:=ActiveWorkbook.Worksheets("WebSiteContents").Range("A1"))
.WebFormatting = xlNone
.RefreshPeriod = 0
.RefreshStyle = xlOverwriteCells
.Refresh
End With

'now I run through the website, take time stamps and headlines an
output them along with the ticker/date

ActiveWorkbook.Worksheets("Output").Cells(i, 1).Value = Ticker
ActiveWorkbook.Worksheets("Output").Cells(i, 2).Value = Day & "/"
Month & "/" & Year

' The headline stamps start in row 23 column k and go
' down in steps of 2. The headlines are in column n
' and respective rows
For j = 23 To (MaxReleases * 2 - 1) + 23 Step 2
TimeStamp = ActiveWorkbook.Worksheets("WebSiteContents").Range("k"
j).Value
Headline = ActiveWorkbook.Worksheets("WebSiteContents").Range("n"
j).Value
ActiveWorkbook.Worksheets("Output").Cells(i, j - 20).Value = TimeStamp
ActiveWorkbook.Worksheets("Output").Cells(i, j - 19).Value = Headline
Next

 

[vladiator]

It seems I could do Application.Wait but then I'd inevitable either no
have enough time in cases when the downoad takes longer or waste tim
when the download was faster than expected...

any ideas

 

[vladiator]

Tried Application.Wait - not helping as everything appears to hal
including the external data download it is waiting for...

:confused

 

[Tom Ogilvy]

.Refresh BackgroundQuery:= False

 

[vladiator]

Thanks!
You the man!
That did the trick.
No way I would have figured that out on my own! :