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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel with internet exp freezing up
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel with internet exp freezing up
Wait, correction: I said <Ctl-C, but that's old school. The current way to
do it, I think, is <Ctl-Break. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel with internet exp freezing up
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. "Bob Bridges" wrote: 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. |
Reply |
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 |