Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel with internet exp freezing up
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel freezing | Excel Programming | |||
Help! Excel keeps freezing | Excel Discussion (Misc queries) | |||
Excel program keeps freezing | Excel Discussion (Misc queries) | |||
Excel Freezing | Excel Discussion (Misc queries) | |||
excel freezing | Excel Programming |