Web scraper. Run time error 1004 - Method 'Range' of object'_Global failed.

[superfurry]

Hi all,

Struggling with a macro that I am using to scrape some data from a webpage. The code is as follows and is placed in a Module.

Sub getdataEnglish()


Application.Calculate


Dim result As String
Dim myURL As String
Dim winHttpReq As Object
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
myURL = "http://www.bbc.co.uk/news"


winHttpReq.Open "GET", myURL, False
winHttpReq.Send
result = winHttpReq.responseText
Application.ScreenUpdating = True


Range("Macros!A2").Value = result


Application.OnTime Now + TimeValue("00:02:00"), "getdataEnglish"
End Sub


All works perfectly until I open another spreadsheet. I then get the run time error 1004 when the macro is run. It is failing at the Range("Macros!A2") line. I presume it is because I don't have a sheet in the new workbook that I have opened called macros? Does anyone have any good ideas for a workaround which means that the macro will run even if there is another workbook open?

As an aside, my macro is downloading all of the html on the page. Would it be possible to use the same macro but for it to only extract data from a particular heading? ie. so it doesn't download the whole webpage every time. e.g. I only want to scrape a small part of the website - not everything!

Any help would be much appreciated.

Bert

 

[wigi]

If you change this:

[COLOR=#333333]Range("Macros!A2").Value = result[/COLOR]

to:

ActiveSheet.[COLOR=#333333]Range("A2").Value = result[/COLOR]

you use the currently active sheet, whatever its name may be.

 

[Ferdi24]

This should do what you're looking for.
Using ThisWorkbook makes sure you always use the workbook the code is placed in, regardless of which other wokbooks you have opened.
The getElementsByClassName("story")(0).innerText means you take the first (0) element of all the ones that have a class called "story" and take the text that is placed within this tag.

Sub getData2()
'Under Tools > References activate "Microsoft HTML Object Library"
Dim oHtml       As HTMLDocument
Dim oElement    As Object
Set oHtml = New HTMLDocument


With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", "http://www.bbc.co.uk/news", False
    .send
    oHtml.body.innerHTML = .responseText
End With
ThisWorkbook.Sheets("Macros").Range("A2").Value = oHtml.getElementsByClassName("story")(0).innerText
End Sub

 

[superfurry]

Wigi, Ferdi24 –thanks v much for the answers...
A few more queries...
In my original code I used the following to get the macro to run every 2 minutes. Now that I have placed this within ‘Thisworkbook’, Excel says that it can’t find the macro after it has been run once?

<code> Application.OnTime Now + TimeValue("00:02:00"), "getdata" </code>

Also, I am now trying to use your code to extract the ‘Valid from’ time which is on another website under the [h1]Three Day Flood Risk Forecast[/h1]

header on the following page: Environment Agency - Three Day Flood Risk Forecast . Ie. in cell A2, I want to display “Valid from 10:30 on 04 August 2014.”, and I want Excel to check for updates every 2 minutes.
However I have not successfully been able to modify your code to allow this (I am quite new to VBA!). Any pointers would be much appreciated.

 

[superfurry]

Posted by accident.