getting VBA to pause while external data loads from web
 

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").Query Tables.Add(Connection:="URL;
& CompleteAddress
Destination:=ActiveWorkbook.Worksheets("WebSiteCon tents").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

--
Message posted from http://www.ExcelForum.com

getting VBA to pause while external data loads from web
 

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

--
Message posted from http://www.ExcelForum.com

getting VBA to pause while external data loads from web
 

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

:confused

--
Message posted from http://www.ExcelForum.com

getting VBA to pause while external data loads from web
 

.Refresh BackgroundQuery:= False

--
Regards,
Tom Ogilvy


"vladiator " wrote in message
...
Hi,

A quick question for you VBA gurus
Say I have a program that goes through a loop and downloads web sites
into an excel spread sheet, once the contents (news releases) are
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 script
job to download the sites and save them locally in separate files - I
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 problem
comes up when the page is still loading and the code runs further - it
already tries to read but there's still nothing there.
Is there any way I can get it to wait till the external data is
loaded?

Here's a snippet of the code:


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

With

ActiveWorkbook.Worksheets("WebSiteContents").Query Tables.Add(Connection:="UR
L;"
& CompleteAddress,
Destination:=ActiveWorkbook.Worksheets("WebSiteCon tents").Range("A1"))
WebFormatting = xlNone
RefreshPeriod = 0
RefreshStyle = xlOverwriteCells
Refresh
End With

'now I run through the website, take time stamps and headlines and
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 j


---
Message posted from http://www.ExcelForum.com/

getting VBA to pause while external data loads from web
 

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

--
Message posted from http://www.ExcelForum.com