Excel with internet exp freezing up

[Accessor]

Hi all. I've been working on this all day. Just when I thought I had it
tested on a few sample parameters, it freezes up when I put it to the test of
real data. In a nutshell, I'm using internet explorer (my first time doing
this method, have been using web queries) to parse data out of html data
tables. Seems pretty straightforward. I pass the sub a web address string. I
need to loop it several thousand times, though. After it runs a few times, my
machine freezes up. Sometimes I get an "out of memory" error, sometimes not.
In all cases I can't open any other files, and I get the "thud" sound when
clicking on just about anything, even ctl alt del. I'm disposing of the IE
instances at the end of every run, so, any help? Sigh. Here's the code:

Sub OptionsDataDownload(str As String)
'Uses instances of IE to parse web tables.
Dim objIE As Object
Dim varTables, varTable
Dim varRows, varRow
Dim varCells, varCell
Dim lngRow As Long, lngColumn As Long
Dim myTime As Date
Dim sPage As String
Dim WSUsedRow As Long
Set WS = Worksheets("Workspace")
WS.Cells.Clear

'Create a new hidden instance of IE.
Set objIE = New SHDocVw.InternetExplorer
objIE.Visible = True

'Open the web page.
objIE.Navigate str

'Set the timer for the page download.
Let myTime = Now
Do While (Now - myTime) < TimeSerial(0, 0, 7)
If objIE.ReadyState = READYSTATE_COMPLETE Then
Exit Do 'Don't hold if not necesary
End If
Loop

If objIE.ReadyState <> READYSTATE_COMPLETE Then
GoTo Cleanup 'Timed out, exit.
End If
'See if there are options for this date.
sPage = objIE.Document.body.innerText
If InStr(sPage, "No options are available for this date.") Then
Exit Sub
End If
Set varTables = objIE.Document.All.tags("TABLE")

WSUsedRow = 1
For Each varTable In varTables

'Use the innerText to see if this is the table we want.
If InStr(varTable.innerText, "Strike PriceSymbolLastChg%ChgTime
ValueBidAskVolOpen Interest") _
And InStr(varTable.innerText, "Options for ") = False Then

'If so, parse the web tables.
Set varRows = varTable.Rows
For Each varRow In varRows
Set varCells = varRow.Cells
lngColumn = 1 'This will be the output column
For Each varCell In varCells
WS.Cells(WSUsedRow, lngColumn) = varCell.innerText
lngColumn = lngColumn + 1
Next varCell
WSUsedRow = WSUsedRow + 1
Next varRow

End If
Next varTable

Cleanup:
Set varCell = Nothing: Set varCells = Nothing
Set varRow = Nothing: Set varRows = Nothing
Set varTable = Nothing: Set varTables = Nothing
objIE.Quit
Set objIE = Nothing
End Sub

 

[Bob Bridges]

Accessor, it happens this is something I've been playing with myself, the
last year or two. I didn't try to work through your code and understand it
all, but I can add a few things that you may or may not already have known:

1) I don't know what your real target URL is, but I've found there are a few
web sites that literally never stop loading, for some reason. Usually I need
only selected fields from a given page, so as soon as those fields have been
loaded I don't care about the rest; that doesn't cause a problem. But if I
need the whole page - if I'm trying to run it through my MapPage routine, for
example (which creates an Excel page with 1500 or 2500 rows, one for each
field in the page, showing its ChildNodes tree structure, field ID, inner
HTML etc) - then the only way I can map such pages is to intercept the run
after it's loaded as far as it's going to go and cause the logic to skip the
test for the full end.

2) Maybe you already know how to do this, but if your code "hangs" then it
probably is in a loop somewhere. You can find out where, and subsequently
follow and fix it, by hitting <Ctl-C> and then stepping through the logic one
step at a time, watching the program move from one line to the next and
checking values as you go, in order to figure out exactly what's going on.
If you already know how to do that, I suggest you try it. If you don't, let
me know and I'll give you some pointers.

 

[Bob Bridges]

Wait, correction: I said <Ctl-C>, but that's old school. The current way to
do it, I think, is <Ctl-Break>.

 

[Accessor]

Hi Bob, thanks for the response. I'm not stuck in a loop... it more seems
like there's an issue with the multiple instances of internet explorer that
my code generates, but that's just a guess. If you look at my code, I do
implement a time out process for each web page, and it seems (key word) to be
working. As for my thought on it being internet explorer instances, that's
just my guess.