VBA to copy/paste data from already open chrome web page

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
645
Hi, I have been searching with no luck to find a solution to copying/pasting data from an open chrome web page.

I have chrome web page open (the web page has a title, so hoping to use the title). On the page there are columns with headers and columns of data. I'd like to select certain columns and paste them to excel.

Would anyone be able to help get me started with this?

Thanks in advance for any help.

SD
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
645
Hi, I have been searching with no luck to find a solution to copying/pasting data from an open chrome web page.

I have chrome web page open (the web page has a title, so hoping to use the title). On the page there are columns with headers and columns of data. I'd like to select certain columns and paste them to excel.

Would anyone be able to help get me started with this?




Thanks in advance for any help.

SD
I pulled together this script. It works (sort of), it will pull the data into 1 cell, instead of value by value into individual cells. Also I realized it may be helpful to drop the source page info to help:

Sub GetTableData()


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 "web page address goes here"




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


y = 1


Set HTML = objIE.Document


Set elements = HTML.getElementsByClassName("timeLine")


For Each element In elements
If element.ClassName = "timeCell" Then
Sheets("test").Cells(1, y).Value = element.innerText
y = y + 1
End If
Next element


End Sub

Here is some source page detail: Here I would like each time pasted in a unique cell in Excel

div class="timeLine" id="dijit__TemplatedMixin_17" widgetid="dijit__TemplatedMixin_17" style="width: 80px;" table data-dojo-attach-point="timeTable" tbody data-dojo-attach-point="timeTableBody" tr td class="timeCell"00:00

00:00
00:30
01:00
01:30
02:00
02:30
03:00
03:30
04:00
04:30
05:00
05:30
06:00
06:30
07:00
07:30
08:00
08:30
09:00
09:30
10:00
10:30
11:00
11:30
12:00
12:30
13:00
13:30
14:00
14:30
15:00
15:30
16:00
16:30
17:00
17:30
18:00
18:30
19:00
19:30
20:00
20:30
21:00
21:30
22:00
22:30
23:00
23:30
Summary

<tbody>
</tbody>
 
Last edited:

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
645
Hello MVPs, I'm still looking for help if possible on my scrape. I have the below code working but I am only returning the table time column (above). However I have something wrong that is not pulling the table container values.

Any ideas where I am going wrong?


objIE.Navigate "Insert web address here"


Do Until objIE.ReadyState = 4 And Not objIE.Busy
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:03"))
HTMLdoc.body.innerHTML = objIE.Document.body.innerHTML
With HTMLdoc.body
Set objTable = .getElementsByTagName("table")
For lngTable = 0 To objTable.Length - 1
For lngRow = 0 To objTable(lngTable).Rows.Length - 1
For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
ThisWorkbook.Sheets("Sheet1").Cells(ActRw + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
Next lngCol
Next lngRow
ActRw = ActRw + objTable(lngTable).Rows.Length + 1
Next lngTable
End With
objIE.Quit
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
645
Hello MVPs, I'm still looking for help if possible on my scrape. I have the below code working but I am only returning the table time column (above). However I have something wrong that is not pulling the table container values.

Any ideas where I am going wrong?


objIE.Navigate "Insert web address here"


Do Until objIE.ReadyState = 4 And Not objIE.Busy
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:03"))
HTMLdoc.body.innerHTML = objIE.Document.body.innerHTML
With HTMLdoc.body
Set objTable = .getElementsByTagName("table")
For lngTable = 0 To objTable.Length - 1
For lngRow = 0 To objTable(lngTable).Rows.Length - 1
For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
ThisWorkbook.Sheets("Sheet1").Cells(ActRw + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
Next lngCol
Next lngRow
ActRw = ActRw + objTable(lngTable).Rows.Length + 1
Next lngTable
End With
objIE.Quit


I'm think the issue is in this section. Would anyone have any ideas?

HTMLdoc.body.innerHTML = objIE.Document.body.innerHTML
With HTMLdoc.body
Set objTable = .getElementsByTagName("table")


Would it help if I past some of that page source?

SD
 

Forum statistics

Threads
1,078,312
Messages
5,339,422
Members
399,305
Latest member
msklut

Some videos you may like

This Week's Hot Topics

Top