Scraping Data from Webpage document into Excel

[Corried]

Hello Excel Professionals and everyone who volunteer their support.

I am having a problem. I am not sure if "Mr. Worf" or anyone out there can help me…

I am asking for your time and patience and your knowledge, as this may sound complicated to understand…

I have 18 weblinks which are connected into one website, which I would like to scrape all data “text summary” into "sheet1", which is named “January”.

Now each link data scraping is copied from website and pasted using one macro button. If possible…

The data is pasted in order beginning in Cell B3. Here are the links below:

• Cell B3: Australia Manufacturing PMI | 1992-2020 Data | 2021-2022 Forecast | Calendar
• Cell B4: Australia Performance Services Index | 2003-2020 Data | 2021-2022 Forecast | Calendar
• Cell B5: Australia Business Confidence | 1997-2020 Data | 2021-2022 Forecast | Calendar
• Cell B6: Australia Consumer Confidence | 1974-2020 Data | 2021-2022 Forecast | Calendar
• Cell B7: Australia Dwelling Approvals | 1983-2020 Data | 2021-2022 Forecast | Calendar
• Cell B8: Australia Retail Sales YoY | 1983-2020 Data | 2021-2022 Forecast | Historical | Chart
• Cell B9: Austria Money Supply M2 | 1997-2020 Data | 2021-2022 Forecast | Historical | Chart
• Cell B10: Australia Interest Rate | 1990-2020 Data | 2021-2022 Forecast | Calendar | Historical
• Cell B11: Australia Producer Prices | 1998-2020 Data | 2021-2022 Forecast | Calendar
• Cell B12: Australia Consumer Price Index (CPI) | 1950-2020 Data | 2021-2022 Forecast
• Cell B13: Australia Unemployment Rate | 1978-2020 Data | 2021-2022 Forecast | Calendar
• Cell B14: Australia Employment Rate | 1978-2020 Data | 2021-2022 Forecast | Historical | Chart
• Cell B15: Australia Government Net Debt | 2007-2020 Data | 2021-2022 Forecast | Historical
• Cell B16: Australia Government Revenues | 1973-2020 Data | 2021-2022 Forecast | Historical
• Cell B17: Australia Government Spending | 1959-2020 Data | 2021-2022 Forecast | Historical
• Cell B18: Australia GDP Constant Prices | 1959-2020 Data | 2021-2022 Forecast | Historical
• Cell B19: Australia Central Bank Balance Sheet | 1994-2020 Data | 2021-2022 Forecast
• Cell B20: United States Initial Jobless Claims | 1967-2020 Data | 2021-2022 Forecast | Calendar

Now on each link or webpage, there is a text summary at the top of each page. Please view the 1st link example. Australia Manufacturing PMI | 1992-2020 Data | 2021-2022 Forecast | Calendar Here is a snapshot highlighted in blue:

The problem is, how can someone point me to the right direction? And how can I do this?
Is there a easily or effective way I can use to get the end results. And If so, what do you suggest I do?

Please note. as new data should be updated every time...

Thanks in advance


Cheers.

 

[Saurabhj]

Hi Corried,

Kindly use below macro to extract the required description in specified cells.
(see images)
1. Name of Sheet having URL of all sites - "Sites"
2. Name of Sheet where data extracted - "January"


Sub scrap_Website_className()
On Error Resume Next

Dim HTMLDoc As New HTMLDocument
Dim ieBrowser As New InternetExplorer
Dim lastRow As Byte, counter As Byte

Dim trow As Object

lastRow = WorksheetFunction.CountA(Worksheets("Sites").Range("A:A"))

For counter = 1 To lastRow

'To Open website in Internet Explorer
ieBrowser.navigate Sheets("Sites").Range("A" & counter)

Do
' Wait till the Browser is loaded
Loop Until ieBrowser.readyState = READYSTATE_COMPLETE
Application.Wait (Now() + TimeValue("00:00:03"))

Set HTMLDoc = ieBrowser.document

Sheets("January").Cells(counter + 2, 2) = HTMLDoc.getElementsByTagName("h2")(0).innerText
Next
MsgBox "Content Copied"
End Sub

 

[Corried]

Hi Corried,

Kindly use below macro to extract the required description in specified cells.
(see images)
1. Name of Sheet having URL of all sites - "Sites"
2. Name of Sheet where data extracted - "January"


Sub scrap_Website_className()
On Error Resume Next

Dim HTMLDoc As New HTMLDocument
Dim ieBrowser As New InternetExplorer
Dim lastRow As Byte, counter As Byte

Dim trow As Object

lastRow = WorksheetFunction.CountA(Worksheets("Sites").Range("A:A"))

For counter = 1 To lastRow

'To Open website in Internet Explorer
ieBrowser.navigate Sheets("Sites").Range("A" & counter)

Do
' Wait till the Browser is loaded
Loop Until ieBrowser.readyState = READYSTATE_COMPLETE
Application.Wait (Now() + TimeValue("00:00:03"))

Set HTMLDoc = ieBrowser.document

Sheets("January").Cells(counter + 2, 2) = HTMLDoc.getElementsByTagName("h2")(0).innerText
Next
MsgBox "Content Copied"
End Sub

Hi Saurabhj

The code you have provide for me have worked wonderfully. I appreciate the extra step that you have given to me by creating the steps so simple.

At first. I had a couple of errors. As I did not have Microsoft HTML Object Library & Microsoft Internet Control check box tick. Once this were rectified, a matter of seconds. I got the info.

I just want to say.

Thank you very much.