In order to fix the following code, I tried to split it up into a smaller part. So, I have the following code that drives me crazy for hours in Sheet1:
Sub Scrapping_Data()
Dim IE As Object, EURUSD1 As String, EURUSD2 As String
Application.ScreenUpdating = False
Range("A:B").Clear
Set IE = CreateObject("internetexplorer.application")
With IE
.Navigate "http://uk.investing.com/currencies/streaming-forex-rates-majors"
.Visible = False
End With
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Set FOREX = IE.document.getElementById("pair_1")
EURUSD1 = FOREX.Cells(1).innerHTML
EURUSD2 = FOREX.Cells(2).innerHTML
IE.Quit
Set IE = Nothing
Range("A1").Value = EURUSD1
Range("B1").Value = EURUSD2
End Sub
I run it for the first time and it worked fine. But when I run it for the second time, the error the run-time error '91' occurred. So I clicked F8, but nothing happened the code worked fine and I checked Sheet1 there were values in Cells(1,1) and Cells(1,2). I then run it again and the error the run-time error '13' occurred this time. Again I clicked F8, but nothing happened the code worked fine. When I kept running the code, the errors still occurred and clicking F8 didn't help to find the problem. What is wrong with my code? How to fix it?
What I don't get it here too is my laptop is getting slow every time I run the code and I have to manually restart it many times.
The following requires that you go into the VBE's Tools ► References and place checkmarks beside Microsoft HTML Object library and Microsoft XML v6.0.
This is an xmlhttprewuest equivalent of an Internet Explorer object web scrape to the same URL.
Option Explicit
Sub tournamentFixtures()
'declare the objects with early binding
Dim htmlBDY As New HTMLDocument, xmlHTTP As New MSXML2.XMLHTTP60
'declare the regular variables
Dim sURL As String, ws As Worksheet
'set a var object to the destination worksheet
Set ws = Worksheets("Sheet1")
'assign the URL to a string var
sURL = "http://uk.investing.com/currencies/streaming-forex-rates-majors"
'isolate all commands to the MSXML2.XMLHTTP60 object
With xmlHTTP
'initiate the URL
.Open "GET", sURL, False
'set hidden header information
.setRequestHeader "User-Agent", "XMLHTTP/1.0"
'get the page data
.send
'safety check to make sure we got the web page's data
If .Status <> 200 Then GoTo bm_safe_Exit
'if here you got the page data - copy it to the local var
htmlBDY.body.innerHTML = .responseText
End With
'localize all commands to the page data
With htmlBDY
'check if the element ID exists
If Not .getElementById("pair_1") Is Nothing Then
'it exists - get the data directly to the worksheet
With .getElementById("pair_1")
ws.Range("A1") = .Cells(1).innerText
ws.Range("B1") = .Cells(2).innerText
End With
Else
'it doesn't exist - bad page data
MsgBox "there is no 'pair_1' on this page"
End If
End With
bm_safe_Exit:
'clean up all of the objects that were instantiated
Set htmlBDY = Nothing: Set xmlHTTP = Nothing: Set ws = Nothing
End Sub
I have commented virtually every line so you can follow what is happening. This may need some tweaking. I ran it ~40 times and it failed once but that could have been my own Internet connection. Consider this a starting point where you can do your own research to accomplish your goals. If you continue to have problems with this new code, please do not paste this into another question and ask why it doesn't work without doing some research and attempting a solution yourself. StackOverflow is a site for professional and enthusiast programmers.
I gave up trying to offer solutions to web scraping problems because page technology changes too fast to keep up on a peripheral level. You have to be involved in the immediate changes to be able to respond to them quickly and my own interests lie elsewhere. I responded to this request because you actually supplied the URL to test against (something few people asking questions actually think is important - go figure) and I thought the static dimming of the var would help.
The construction and destruction of an InternetExplorer object takes time; up to a few seconds even on the fastest sytems. You can wait an appropriate amount of time for it to relinquish all of the .DLLs et al it has loaded or you can declare your IE as a static object that will be reused on subsequent reruns of the sub procedure.
Option Explicit
Sub Scrapping_Data()
Static IE As Object
Dim EURUSD1 As String, EURUSD2 As String
Application.ScreenUpdating = False
With Worksheets("Sheet1") 'KNOW what worksheet you are on!!!!!
.Range("A:B").Clear
End With
If IE Is Nothing Then
Set IE = CreateObject("internetexplorer.application")
With IE
.Visible = True
'.Visible = False
.Silent = True
End With
End If
With IE
.Navigate "http://uk.investing.com/currencies/streaming-forex-rates-majors"
Do While .ReadyState <> 4: DoEvents: Loop
With .document.getElementById("pair_1")
EURUSD1 = .Cells(1).innerHTML
EURUSD2 = .Cells(2).innerHTML
End With
End With
With Worksheets("Sheet1") 'KNOW what worksheet you are on!!!!!
.Range("A1") = EURUSD1
.Range("B1") = EURUSD2
End With
IE.Navigate "about:blank"
End Sub
The caveat here is that you will have to destruct the InternetExplorer object yourself at some point in the future. Closing the workbook will close the VBA project but leave the IE object 'orphaned'.
Given all of the HTML5 debris that comes along with that web page, have you considered moving to xmlhttprequest? And if you are wondering then yes, that would be a new question under a different set of [tags].
