SSG
SSG

Reputation: 73

Extract dynamically loaded tables from nested div elements to excel in Vba-Selenium

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

Answers (1)

SSG
SSG

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

Related Questions