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

superfurry

New Member
Joined
Feb 15, 2010
Messages
4
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
If you change this:

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

to:

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

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

Ferdi24

Board Regular
Joined
Feb 26, 2012
Messages
133
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.

Code:
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

New Member
Joined
Feb 15, 2010
Messages
4
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:
<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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,061
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top