Need some help with this code.
1) The current code leaves a bunch of IE windows open.
2) The current code leads to a Run-time error '-2147437259 (80004005)':
3) It takes forever to run, Hopefully someone can assist me in converting it to use MSXML2.XMLHTTP60 for example, I heard that works faster.
1) The current code leaves a bunch of IE windows open.
2) The current code leads to a Run-time error '-2147437259 (80004005)':
3) It takes forever to run, Hopefully someone can assist me in converting it to use MSXML2.XMLHTTP60 for example, I heard that works faster.
Code:
'
'-----------------------------------------------------
' Run-time error '-2147437259 (80004005)': ' This Error Occurs, eventually, in the 'Yahoo_One_Year_Estimates_Scrape_Error' section \/ \/ \/
' Also many internet explorer windows are left open that should have been closed
'
' Automation Error
' Unspecified Error
'-----------------------------------------------------
'
'
' Global Variables That will be used
'
Public Doc As HTMLDocument
'
Public StockMainPageURL As String ' This will be the main portion of the URL that we send to Internet Explorer
Public TotalURL As String ' This will be the complete URL that we send to Internet Explorer
'
Public CellCounter As Integer ' This will be used to adjust left to right on web site cells
Public RowCounter As Integer ' This adjusts the offset from the top of the spreadsheet to the start of the columns
Public StockCount As Integer ' This counts the actual stocks being analyzed currently
Public TotalStocksToLoad As Integer ' This counts the stocks that should be analyzed right now
'
Public PageLoadAttempt As Long ' This counts the number of times we have tried to load a page
'
'-------------------------------------------------------------------------------------------------------------------------------
'
Private Sub RefreshEntireDocument_Click()
'
' This will Clear certain cell values in the spreadsheet when the $B$1 'Refresh' cell is clicked
'
Range("$B$5:$K$254").Select ' Select the range of $B$5 thru $J$254
Selection.ClearContents ' Delete the contents of this range
'
'
' -------------------------------------------------------------------------------------------------------------------------
'
' Scrape stocks to consider looking into further from 1st URL page
'
RowCounter = 5 ' Start loading stock values recieved into the 5th row of Excel
MaxYahooDelay = 0 ' Initialize MaxYahooDelay = 0
'
CellCounter = 0 ' Left to right cell counter
PageLoadAttempt = 0 ' Initialize PageLoadAttempt = 0
TotalStocksToLoad = 100 ' we will Scrape this amount of stocks from the 1st loaded page of stocks
'
Call Scrape_BarChart_Stock_Page_1 ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
'
' Scrape stocks to consider looking into further from 2nd URL page
'
CellCounter = 0 ' Left to right cell counter
PageLoadAttempt = 0 ' Initialize PageLoadAttempt = 0
TotalStocksToLoad = 100 ' we will Scrape this amount of stocks from the 2nd loaded page of stocks
'
Call Scrape_BarChart_Stock_Page_2 ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
'
' Scrape stocks to consider looking into further from 3rd URL page
'
CellCounter = 0 ' Left to right cell counter
PageLoadAttempt = 0 ' Initialize PageLoadAttempt = 0
TotalStocksToLoad = 50 ' we will Scrape this amount of stocks from the 3rd loaded page of stocks
'
Call Scrape_BarChart_Stock_Page_3 ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
' -------------------------------------------------------------------------------------------------------------------------
'
' Scrape values from Yahoo to Update the one year estimates from previous pages of stocks scraped
'
RowCounter = 5 ' Start loading stock values recieved into the 5th row of Excel
PageLoadAttempt = 0 ' Initialize PageLoadAttempt = 0
TotalYahooDelay = 0 ' Initialize TotalYahooDelay = 0
TotalYahooPageAttempts = 0 ' Initialize TotalYahooPageAttempts = 0
TotalStocksToLoad = 250 ' we will Scrape this amount of stocks from the 3rd loaded page of stocks
Call Scrape_Yahoo_One_Year_Estimates ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
'
' Display some final results in the status bar
Application.StatusBar = "Spreadsheet Refreshing Complete :)" ' & " Avg Yahoo Delay = " & AvgYahooDelay & " Avg Yahoo Page Attempts = " & AvgYahooPageAttempts
'
End Sub
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'
Private Sub Scrape_Yahoo_One_Year_Estimates() ' *** Good up to here ***
'
'
For StockCount = 1 To TotalStocksToLoad ' Grab One Year stock price estimate
'
'
ReloadScrape_Yahoo_One_Year_Estimates:
'
' Load all of the Update one year estimates
DelaySeconds = 0 ' Initialize DelaySeconds to zero
PageLoadAttempt = PageLoadAttempt + 1 ' Add 1 to our PageLoadAttempt counter
'''' TotalYahooPageAttempts = TotalYahooPageAttempts + 1 ' This will be the total yahoo Page Attempts
'
StockMainPageURL = "finance.yahoo.com/quote/" ' This will be the main portion of the URL that we send to Internet Explorer
CurrentStockSymbol = Trim(Range("B" & RowCounter).Value) ' This is the stock symbol that we will be addressing
'
' Setup and Load the Internet Explorer Page ...
'''' Dim IE As New SHDocVw.InternetExplorer ' This works
Dim IE As New InternetExplorer
'' Dim IE As MSXML2.XMLHTTP60
'' Set IE = New MSXML2.XMLHTTP60
'
TotalURL = "https://" & StockMainPageURL & CurrentStockSymbol ' This will be the complete URL that we send to Internet Explorer
'
If CurrentStockSymbol = 0 Or CurrentStockSymbol = "" Or IsEmpty(CurrentStockSymbol) = True Then ' If no stock symbol found @ $B? then ...
PageLoadAttempt = 0 ' Reset PageLoadAttempt = 0
StockCount = TotalStocksToLoad ' Indicate no more stocks to load
'
IE.Quit ' Close Internet Explorer Window
Set IE = Nothing ' Clear Internet Explorer Memory
'
Exit Sub ' Exit this sub
Else
'
On Error GoTo Yahoo_One_Year_Estimates_Scrape_Error ' If Error occurs then goto Yahoo_One_Year_Estimates_Scrape_Error
'
Set IE = New InternetExplorer ' Open Internet Explorer Browser
'
' Browser address that we will be scraping values from
IE.navigate TotalURL ' Load the Internet Explorer URL
'
' Make the Browser window, that we will be scraping values from, visible
IE.Visible = True ' Make Internet Explorer Windows Visible
'
' Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
Do While IE.readyState <> 4 And DelaySeconds <= 19 ' Loop while IE is still loading and <= 19 seconds delayed
'' Application.Wait DateAdd("s", 1, Now)
Application.Wait (Now + TimeValue("00:00:01")) ' Delay for 1 second
DoEvents ' Enable Mouse Clicks
'
' Update status bar to inform the user of what is occurring
Application.StatusBar = "Loading website … " & TotalURL & " Stock # " & (RowCounter - 4) ''''& _
'''' " Delay Seconds = " & DelaySeconds & " Page Load Attempts = " & PageLoadAttempt & _
'''' " Avg Yahoo Delay = " & AvgYahooDelay & " AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
DelaySeconds = DelaySeconds + 1 ' Add 1 to our DelaySeconds Counter
'
'''' If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds ' Save the MaxYahooDelay
'' TotalYahooDelay = TotalYahooDelay + 1
'
Loop ' Loop back
'
' Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
Do While IE.Busy And DelaySeconds <= 19 ' Or IE.readyState <> 4 And DelaySeconds <= 19 ' Loop while IE is still loading and <= 19 seconds delayed
'' Application.Wait DateAdd("s", 1, Now)
Application.Wait (Now + TimeValue("00:00:01")) ' Delay for 1 second
DoEvents ' Enable Mouse Clicks
'
' Update status bar to inform the user of what is occurring
Application.StatusBar = "Loading website … " & TotalURL & " Stock # " & (RowCounter - 4) ''''& _
'''' " Delay Seconds = " & DelaySeconds & " Page Load Attempts = " & PageLoadAttempt & _
'''' " Avg Yahoo Delay = " & AvgYahooDelay & " AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
DelaySeconds = DelaySeconds + 1 ' Add 1 to our DelaySeconds Counter
'
'''' If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds ' Save the MaxYahooDelay
Loop ' Loop back
'
'
If DelaySeconds > 19 Then ' If we have delayed for > 19 seconds to allow the page to load then ...
IE.Quit ' Close Internet Explorer Window
'
If PageLoadAttempt <= 4 Then GoTo ReloadScrape_Yahoo_One_Year_Estimates ' If we have'nt tried 4 reloads of this page then reload page again
End If ' End If
'
If PageLoadAttempt > 4 Then ' If we have tried 4 reloads of the URL page then Display a message box & Exit program
MsgBox "We've reloaded the same web page " & PageLoadAttempt & " times without success so we're going to pause the program" & _
" so you can investigate.", , "Multiple errors detected"
'
PageLoadAttempt = 0 ' Reset PageLoadAttempt = 0
'
Stop ' Stop this Excel program!
End If
'
Set Doc = IE.document
'
End If
'
'
'''' TotalYahooDelay = TotalYahooDelay + DelaySeconds
'''' AvgYahooDelay = TotalYahooDelay / (RowCounter - 4)
'''' AvgYahooPageAttempts = TotalYahooPageAttempts / (RowCounter - 4)
'
' Update status bar to inform the user of what is occurring
Application.StatusBar = "Gathering Data from website … " & TotalURL & " Stock # " & (RowCounter - 4) ''''& _
'''' " Delay Seconds = " & DelaySeconds & " Page Load Attempts = " & PageLoadAttempt & _
'''' " Avg Yahoo Delay = " & AvgYahooDelay & " AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
Range("J" & RowCounter).Value = Doc.getElementsByTagName("td")(11).innerText ' Scrape the Yahoo 52 Week Price Range
Range("K" & RowCounter).Value = Doc.getElementsByTagName("td")(31).innerText ' Scrape the Yahoo One Year Price Estimate
'
On Error GoTo 0 ' Clear Errors & Set Excel Error handling to Default
'
RowCounter = RowCounter + 1 ' Advance to next row in Excel sheet
'
IE.Quit ' Close Internet Explorer Window
Set IE = Nothing ' Clear Internet Explorer Memory
'
PageLoadAttempt = 0 ' Reset PageLoadAttempt = 0
'
Next ' Load next stock until all are loaded
'
Exit Sub ' Exit this Sub
'
Yahoo_One_Year_Estimates_Scrape_Error:
'
' Tried this solution from google \/ \/ to solve errors, No luck :( ' Shut down all Internet Explorer windows
'' Dim wsh As Object
'' Dim windowStyle As Integer: windowStyle = 1
'' Dim waitOnReturn As Boolean: waitOnReturn = True
'
'' Set wsh = VBA.CreateObject("Wscript.Shell")
'' wsh.Run "taskkill /F /IM iexplore.exe", windowStyle, waitOnReturn
'
'
'
'' IE.Quit ' Close Internet Explorer Window
Set IE = Nothing ' Clear Internet Explorer Memory
'
' This works some what
Set IE = New InternetExplorer ' Open Internet Explorer Browser
'
'
Resume Next ' Go back to the next line after the previous error occurred
'
End Sub
'________________________________________________________________________________________________________________________________________________________
