VBA Scraping a Website

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Hi I have this code below to scrape from the word "Wednesday" across to "Service Level Time Forecast" and then the time column below the word Wednesday to Excel. This works good.
I'm looking of help to pull the numbers, starting with (in this case) the highlighted number 4 to excel. Here is the element for the data in the first column:

HTML:
<div class="cell highlighted" style="width: 88px; background: none 0% 0% / auto repeat scroll padding-box border-box rgba(0, 0, 0, 0); color: rgb(0, 0, 0);" data-metric-ref="fcContacts" data-metric-position="0" data-metric-datatype="integer" data-rowid="0" data-original-background-color="rgba(0, 0, 0, 0) none repeat scroll 0% 0% / auto padding-box border-box">4</div>

The part I need help inserting into my code below is how to scrape the "fcContacts" piece above into excel.

Here is my VBA so far:

VBA Code:
Sub ScapeThis()

Dim HTML As HTMLDocument
Dim objIE As Object
Dim y As Integer
Dim result As String

Set objIE = New InternetExplorer

objIE.Visible = True


objIE.navigate "insert website here"

Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

x = 1
Set HTML = objIE.document
Set elements = HTML.getElementsByClassName("dayNameNode")
For Each element In elements
    If element.className = "dayNameNode" Then
        Sheets("Sheet1").Cells(1, x).Value = element.innerText ' change 1 to the row you want the data to go to
        x = x + 1
    End If
Next element


Set elements = HTML.getElementsByClassName("headerText")
For Each element In elements
    If element.className = "headerText" Then
        Sheets("Sheet1").Cells(1, x).Value = element.innerText ' change 1 to the row you want the data to go to
        x = x + 1
    End If
Next element


y = 2
Set elements = HTML.getElementsByClassName("timeCell")
For Each element In elements
    If element.className = "timeCell" Then
        Sheets("Sheet1").Cells(y, 1).Value = element.innerText ' change 1 to the column you want the data to go to
        y = y + 1
    End If
Next element


'Insert the part here to pull the column data

End Sub


1575478373327.png




Thanks in advance for any help.

SD
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Bumping this back up to see, if we have any MVPs that can help.

Let me know if I need to provide more detail. Really trying hard to figure this one out.

SD
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Bumping this up one more time for the day.

Thanks in advance for any help or direction.

SD
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Just in case more source data is helpful (sorry slightly blurry):

1575504565647.png
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647

ADVERTISEMENT

Good morning, is anyone willing and able to help?
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Seems I'm talking to myself, but hoping someone bites.

I might be getting further. I does not return any value(s), but I know the ClassName is correct, because if I use the "editable-row" in both sections below, it returns a full row of data in one cell (in a stacked column way). What I need it to return, is only the "fcContacts" value from each "editable-row". I believe my error is within the "for Each element in elements section, specifically the "if elements.className = "fcContacts" Then" sections.

Any thoughts? I would really appreciate the help.

This code does not work but seems like it is getting closer.
VBA Code:
Z = 2
Set elements = html.getElementsByClassName("editable-row")
For Each element In elements
    If element.className = "fcContacts" Then
        Sheets("Sheet1").Cells(Z, 2).Value = element.innerText
        Z = Z + 1
    End If
Next element
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647

ADVERTISEMENT

I will add, that if I choose "editableTable" class name, from the above picture, it returns all the values into 1 cell. So, I suppose another angle at this could be to figure out how to get those values into unique cells.

Anyone have ideas??

SD
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,769
There isn't a class named "fcContacts". "fcContacts" is the first child of each "editable-row" div, so try:

VBA Code:
Set elements = html.getElementsByClassName("editable-row")
For Each element In elements
    Debug.Print element.Children(0).innerText
Next element
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
There isn't a class named "fcContacts". "fcContacts" is the first child of each "editable-row" div, so try:

VBA Code:
Set elements = html.getElementsByClassName("editable-row")
For Each element In elements
    Debug.Print element.Children(0).innerText
Next element

John - I so appreciate the help. Thank you, thank you. This is starting to work. Is there a way I tweak this to focus only on the child "fcContacts"?

Again, can't tell how much I appreciate the direction.

SD
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
John - I so appreciate the help. Thank you, thank you. This is starting to work. Is there a way I tweak this to focus only on the child "fcContacts"?

Again, can't tell how much I appreciate the direction.

SD
John - I think I answered my own question. That seems to be what the 0 is for....
 

Watch MrExcel Video

Forum statistics

Threads
1,130,439
Messages
5,642,129
Members
417,257
Latest member
Sarahbw

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