Code That Works Once/ Twice Either By F5 Or F8 But Then Gets Multiple Errors

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()

Solution 1:

This is an xmlhttprewuest equivalent of an Internet Explorer object web scrape to the same URL.

OptionExplicitSub tournamentFixtures()
    'declare the objects with early bindingDim htmlBDY AsNew HTMLDocument, xmlHTTP AsNew MSXML2.XMLHTTP60
    'declare the regular variablesDim sURL AsString, ws As Worksheet

    'set a var object to the destination worksheetSet 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 objectWith 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 dataIf .Status <> 200ThenGoTo bm_safe_Exit

        'if here you got the page data - copy it to the local var
        htmlBDY.body.innerHTML = .responseText
    EndWith'localize all commands to the page dataWith htmlBDY
        'check if the element ID existsIfNot .getElementById("pair_1") IsNothingThen'it exists - get the data directly to the worksheetWith .getElementById("pair_1")
                ws.Range("A1") = .Cells(1).innerText
                ws.Range("B1") = .Cells(2).innerText
            EndWithElse'it doesn't exist - bad page data
            MsgBox "there is no 'pair_1' on this page"EndIfEndWithbm_safe_Exit:'clean up all of the objects that were instantiatedSet htmlBDY = Nothing: Set xmlHTTP = Nothing: Set ws = NothingEndSub

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.}