Link to home
Start Free TrialLog in
Avatar of Nirvana
NirvanaFlag for India

asked on

Expand HTML tables without having to click on it

We have a website where we need to click on multiple rows which are collapsed ("+") we need to click on each of the Plus (+) sign to expand and copy paste to excel. I am unable to get focus on the same with keyboard when  I use tab key to do ctrl+right arrow

Can this be done using VBA or javascript

the methods I have tried is use "table capture" chrome extension

Below code works perfect for collapsed rows. Thanks a lot for your help in advance

Private Sub Test()

   Dim ie As Object, i As Long, strText As String

   Dim doc As Object, hTable As Object, hBody As Object, hTR As Object, hTD As Object
   Dim tb As Object, bb As Object, tr As Object, td As Object

   Dim y As Long, z As Long, wb As Excel.Workbook, ws As Excel.Worksheet

     Set wb = Excel.ActiveWorkbook
     Set ws = wb.ActiveSheet

     Set ie = CreateObject("InternetExplorer.Application")
     ie.Visible = True

      y = 1   'Column A in Excel
      z = 1   'Row 1 in Excel

     ie.navigate "url", , , , "Content-Type: application/x-www-form-urlencoded" & vbCrLf

     Do While ie.busy: DoEvents: Loop
     Do While ie.ReadyState <> 4: DoEvents: Loop

     Set doc = ie.document
     Set hTable = doc.GetElementsByTagName("table")


     For Each tb In hTable

        Set hBody = tb.GetElementsByTagName("tbody")
        For Each bb In hBody

            Set hTR = bb.GetElementsByTagName("tr")
            For Each tr In hTR


                 Set hTD = tr.GetElementsByTagName("td")
                 y = 1 ' Resets back to column A
                 For Each td In hTD
                   ws.Cells(z, y).Value = td.innertext
                   y = y + 1
                 Next td
                 DoEvents
                 z = z + 1
            Next tr
            Exit For
        Next bb
    Exit For
  Next tb

End Sub

Open in new window

Avatar of Qlemo
Qlemo
Flag of Germany image

Without seeing the HTML code (which you probably cannot provide), advice can only be generic.

You need to analyze the HTML code for the Click method and the HTML element it applies to. Then you can get e.g. from the row element up/down/sidewards in the hierarchy, or search by tagname / id / class to get the corresponding element, and run .Click() for it.
Note: The asker has sent me a HTML fragment for that table per private message, as it contains confidential data.
I've tried to fool around with the HTML fragment you sent me, but it does not contain active code (and that would be difficult to provide).
So let's try if you can add this code starting at line 25, followed by what you already have (ideally you would expand and read out content in one go, but let's start with separate tasks). I've tested it as far as possible, but that is no proof it works for you.
' --- new code at line 25
    For Each tb In doc.GetElementsByClassName("displayTable")
      For Each td In tb.getElementsByTagName("td")
        If InStr(td.InnerHTML, "showSchemes") > 0 Then td.Click
      Next
    Next

' ---- old code starting in line 25

Open in new window

Avatar of Nirvana

ASKER

Dear Qlemo,

no luck mate. No it is still the same the table gets expanded.
You could try working with the added code, and set up a breakpoint in my line 4 (pressing F9 in that line), then single-step with F8 to see if the Click is executed at all.
If you can't provide enough of code for me to try myself, all is left is to try to guide you to how you can debug yourself (in the question).

Open the page in Chrome or IE, look at the source code (use the context menu), and search for "showSchemes" - do you see that? According to the fragment you sent me, It is the Javascript(?) function called for unfolding a row's details, and what my code searches for (but seems not to find).
If you cannot detect that, search for the visible row content, and then go up in the source to find the plus button and the action performed.
Avatar of Nirvana

ASKER

when I search for "showshemes" I find three in the <tr> tag because currently i have three rows, however these are (rows) are dynamic i can have 30 one day and 5 one day

<tr class="odd">
                    <td width="15px" class="center">
                        <img src='/dffts/images/Plus8.gif' alt="Se skjema" title="Se skjema" onclick="showSchemes(1236589693, this);" style="cursor:pointer;"  />
                    </td>
                    <td>
                        New Customer - nanstalt
                    </td>
                    <td>SUM Ningrog TRS</td>
                    <td></td>

<tr class="even">
                    <td width="15px" class="center">
                        <img src='/dffts/images/Plus8.gif' alt="Se skjema" title="Se skjema" onclick="showSchemes(1236599053, this);" style="cursor:pointer;"  />
                    </td>
                    <td>
                        New Customer - nanstalt
                    </td>
                    <td>lvr TND fine</td>
                    <td></td>


Thank you once again
This non-expanded, I guess?
Maybe we have to apply Click for the IMG tag instead of TD:
' --- new code at line 25
    For Each tb In doc.GetElementsByClassName("displayTable")
      For Each img In tb.getElementsByTagName("img")
        If InStr(img.InnerHTML, "showSchemes") > 0 Then img.Click
      Next
    Next

' ---- old code starting in line 25

Open in new window

You might have to Dim img to define the (new) variable I used for clarity.
Avatar of Nirvana

ASKER

Hi I have update with the latest code but still not working, also added variable for image. sent you an email with entire code. thanks a lot
Avatar of Nirvana

ASKER

wherever i have  the "+" sign i have the below code


<img src="/Dssts/images/Plus8.gif" alt="Se skjema" title="Se skjema" onclick="showSchemes(1236599053, this);" style="cursor:pointer;">

<img src="/Dssts/images/Plus8.gif" alt="Se skjema" title="Se skjema" onclick="showSchemes(1236599059, this);" style="cursor:pointer;">

<img src="/Dssts/images/Plus8.gif" alt="Se skjema" title="Se skjema" onclick="showSchemes(1236598895, this);" style="cursor:pointer;">

<img src="/Dssts/images/Plus8.gif" alt="Se skjema" title="Se skjema" onclick="showSchemes(1236607887, this);" style="cursor:pointer;">

<img src="/Dssts/images/Plus8.gif" alt="Se skjema" title="Se skjema" onclick="showSchemes(1236630887, this);" style="cursor:pointer;">
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nirvana

ASKER

I do not know how to thank you enough. Its simply brilliant. the possibilities of VBA are great.. and i do not how you acquired the great knowledge.. A BIG Thank you. Even though  those are not coming in the format that I am looking for (sheer my mistake I might not have provided you enough details) I will Do a Ctrl+A and Paste. Not sure if it is possible after I run the code you have provided

Thank you so much
Avatar of Nirvana

ASKER

One last question


Option Explicit


Private Sub ExtractHtmlTable()

   Dim IE As Object, i As Long, strText As String

   Dim doc As Object, hTable As Object, hBody As Object, hTR As Object, hTD As Object
   Dim tb As Object, bb As Object, Tr As Object, Td As Object, img As Object

   Dim y As Long, z As Long, wb As Excel.Workbook, ws As Excel.Worksheet

     Set wb = Excel.ActiveWorkbook
     Set ws = wb.ActiveSheet

     Set IE = CreateObject("InternetExplorer.Application")
     IE.Visible = True

      y = 1   'Column A in Excel
      z = 1   'Row 1 in Excel

     IE.navigate "url", , , , "Content-Type: application/x-www-form-urlencoded" & vbCrLf

     Do While IE.busy: DoEvents: Loop
     Do While IE.readyState <> 4: DoEvents: Loop

     Set doc = IE.document
     Set hTable = doc.getElementsByTagName("table")
   
    'For Each tb In doc.GetElementsByClassName("displayTable")
     ' For Each td In tb.getElementsByTagName("td")
      '  If InStr(td.InnerHTML, "showSchemes") > 0 Then td.Click
      'Next
    'Next
   
    For Each tb In doc.getElementsByClassName("displayTable")
      For Each img In tb.getElementsByTagName("img")
        If InStr(img.OuterHTML, "showSchemes") > 0 Then img.Click
      Next
    Next

     For Each tb In hTable

        Set hBody = tb.getElementsByTagName("tbody")
        For Each bb In hBody

            Set hTR = bb.getElementsByTagName("tr")
            For Each Tr In hTR


                 Set hTD = Tr.getElementsByTagName("td")
                 y = 1 ' Resets back to column A
                 For Each Td In hTD
                   ws.Cells(z, y).Value = Td.innerText
                   y = y + 1
                 Next Td
                 DoEvents
                 z = z + 1
            Next Tr
            Exit For
        Next bb
    Exit For
  Next tb
 
MsgBox "Process Completed"

End Sub


though this is brilliantly working how can i get in the same table format that is displayed in the page. rather than scattered text, or I have <td> elements only that can be copied
I'm not getting you, sorry. Can you at least show a screen shot of the resulting Excel sheet as of now, and a mockup how it should be?
Avatar of Nirvana

ASKER

Hi attached is the file with two tabs current structure and required structure. in the current structure i think it is getting copied in row 19
ExtractHTMLtables.xlsm
I'll need the HTML code for that part you want to have extracted. There is no way to derive that from the plain text "stream" you get in that Excel cell.
Avatar of Nirvana

ASKER

Hi Qlemo, I was away from network for few days. thanks a lot for your help