Reputation: 73
I've below code to navigate to site, login and then save the displayed web page data. But I could not see the tables in html.body.innerHTML
which I've saved to .txt file. I see below div
content instead. When I do Inspect Element, it shows the tables are under nested div
elements which are under below div
which I want to print to excel.
I would appreciate any help to achieve this.
<div id="container_div" style="height: 100%;">
<table style="width: 100%; height: 100%;">
<tbody><tr>
<td align="center" valign="middle">
<img alt="progress" src="/ria/images/progress.gif">
<br>
<span>Loading...</span>
</td>
</tr>
</tbody></table>
</div>
Vba code:
Option Explicit
Private ch As Selenium.ChromeDriver
Sub TestSelenium()
Set ch = New Selenium.ChromeDriver
ch.Start
ch.Get "https://siteaddress"
ch.Timeouts.ImplicitWait = 20000 ' 5 seconds
With ch
.AddArgument "--headless" ''This is the fix
With .FindElementById("logInForm")
.FindElementById("j_username").SendKeys "username"
.FindElementById("j_password").SendKeys "password"
.FindElementById("submitButton", timeout:=1000000).Click
'ch.Timeouts.Server = 120000 ' 2 mins
'ch.Timeouts.ImplicitWait = 50000 ' 5 seconds
End With
'Stop '<== Delete me after inspection
' .SwitchToFrame .FindElementByTag("iframe", timeout:=10000) '' switch to iframe
Dim html As New HTMLDocument ' Requires Microsoft HTML Library
html.body.innerHTML = ch.ExecuteScript("return document.body.innerHTML;")
Debug.Print html.body.innerText
Dim filePath As String
filePath = "C:\temp\MyTestFile.txt"
' The advantage of correctly typing fso as FileSystemObject is to make autocompletion
' (Intellisense) work, which helps you avoid typos and lets you discover other useful
' methods of the FileSystemObject
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim fileStream As TextStream
' Here is another great method of the FileSystemObject that checks if a file exists
If fso.FileExists(filePath) Then
fso.DeleteFile filePath
End If
' Here the actual file is created and opened for write access
Set fileStream = fso.CreateTextFile(filePath)
' Write something to the file
fileStream.WriteLine html.body.innerHTML
' Close it, so it is not locked anymore
fileStream.Close
.Quit
End With
End Sub
Upvotes: 0
Views: 356
Reputation: 73
I could not find solution to all loop through all tables, but used below code to extract data from particular table using full XPath
:
Dim tr, td, th As WebElement
Dim c, r, l As Integer
Lastrow = 1
'' Print header
For Each tr In ch.FindElementByXPath("/html/body/div[1]/div/div[2]/div/div[4]/div/table/tbody/tr/td/div/table/tbody/tr[1]/td/div/div/div/div/div/div/div[1]/div[3]/div/div[4]/div/div/div/div/div[8]/div/div/div/div[1]/div/table").FindElementByTag("thead").FindElementsByTag("tr")
c = 1
For Each th In tr.FindElementsByTag("th")
Sheets("Sheet1").Cells(Lastrow + r, c).Value = th.Text
c = c + 1
Next th
r = r + 1
Next tr
'' Print table data
For Each tr In ch.FindElementByXPath("/html/body/div[1]/div/div[2]/div/div[4]/div/table/tbody/tr/td/div/table/tbody/tr[1]/td/div/div/div/div/div/div/div[1]/div[3]/div/div[4]/div/div/div/div/div[8]/div/div/div/div[1]/div/table").FindElementByTag("tbody").FindElementsByTag("tr")
c = 1
For Each td In tr.FindElementsByTag("td")
Sheets("Sheet1").Cells(Lastrow + r, c).Value = td.Text
c = c + 1
Next td
r = r + 1
Next tr
Upvotes: 1