Nirvana
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
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
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.
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
ASKER
Dear Qlemo,
no luck mate. No it is still the same the table gets expanded.
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.
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.
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.g if' alt="Se skjema" title="Se skjema" onclick="showSchemes(12365 89693, 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.g if' alt="Se skjema" title="Se skjema" onclick="showSchemes(12365 99053, this);" style="cursor:pointer;" />
</td>
<td>
New Customer - nanstalt
</td>
<td>lvr TND fine</td>
<td></td>
Thank you once again
<tr class="odd">
<td width="15px" class="center">
<img src='/dffts/images/Plus8.g
</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.g
</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:
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
You might have to Dim img to define the (new) variable I used for clarity.
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
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(12365 99053, this);" style="cursor:pointer;">
<img src="/Dssts/images/Plus8.gif" alt="Se skjema" title="Se skjema" onclick="showSchemes(12365 99059, this);" style="cursor:pointer;">
<img src="/Dssts/images/Plus8.gif" alt="Se skjema" title="Se skjema" onclick="showSchemes(12365 98895, this);" style="cursor:pointer;">
<img src="/Dssts/images/Plus8.gif" alt="Se skjema" title="Se skjema" onclick="showSchemes(12366 07887, this);" style="cursor:pointer;">
<img src="/Dssts/images/Plus8.gif" alt="Se skjema" title="Se skjema" onclick="showSchemes(12366 30887, this);" style="cursor:pointer;">
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thank you so much
ASKER
One last question
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
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.Appli cation")
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("displayT able")
' For Each td In tb.getElementsByTagName("td")
' If InStr(td.InnerHTML, "showSchemes") > 0 Then td.Click
'Next
'Next
For Each tb In doc.getElementsByClassName("displayT able")
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?
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
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.
ASKER
Hi Qlemo, I was away from network for few days. thanks a lot for your help
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.