I am coding a macro in excel for web scraping from a particular web page. I am able to go to the web page.The problem is, in that page, there is a hyperlink, which is associated with a java script function to generate a html table. I want to get the data from this table. but, this dynamically created table is not showing up in "View Source" and I am not able to access it. I will share the Java script function here.
Table:
JS function generating table:
function create_table(ret,pshow){
var l_Count = ret.getElementsByTagName("td").length;
if(l_Count>0){
var cols = ret.getElementsByTagName("th").length;
var rows = l_Count/cols;
var tbl=document.createElement('table');
tbl.setAttribute('class','t20Report t20Standard');
tbl.style.borderCollapse="collapse";
var tbdy=document.createElement('tbody');
var tr=document.createElement('tr');
for(var k=0;k<cols;k++){
var th=document.createElement('th');
th.setAttribute('class','t20ReportHeader');
th.appendChild(document.createTextNode(ret.getElementsByTagName("th")[k].firstChild.nodeValue));
tr.appendChild(th);
}
tbdy.appendChild(tr);
var r=0;
for(var i=0;i<rows;i++){
var tr=document.createElement('tr');
for(var j=0;j<cols;j++){
var td=document.createElement('td');
td.setAttribute('class','t20data');
td.appendChild(document.createTextNode(ret.getElementsByTagName("td")[r].firstChild.nodeValue));
r++;
tr.appendChild(td);
}
tbdy.appendChild(tr);
}``
tbl.appendChild(tbdy);
pshow.appendChild(tbl);
}
else
{
var t=document.createTextNode("DATA NOT AVAILABLE");
pshow.appendChild(t);
}
This generates a HTML code as below.
HTML for the table:
<table class="t20Report t20Standard" style="border-collapse: collapse;">
<tbody>
<tr>
<th class="t20ReportHeader">Switch</th><th class="t20ReportHeader">NE</th>
<th class="t20ReportHeader">port_card_slot</th>
<th class="t20ReportHeader">Vertical</th>
<th class="t20ReportHeader">Pillar</th>
<th class="t20ReportHeader">OUT CT Box</th>
<th class="t20ReportHeader">OUT Tag</th>
<th class="t20ReportHeader">IN CT Box</th>
<th class="t20ReportHeader">IN Tag</th>
<th class="t20ReportHeader">DP</th>
<th class="t20ReportHeader">DP Tag</th>
<th class="t20ReportHeader">DP Type</th>
</tr>
<tr>
<td class="t20data">KKDPVLO01</td>
<td class="t20data">0003-02-117</td>
<td class="t20data">00</td>
<td class="t20data">1-001-01-013</td>
<td class="t20data">KKDPVL0062</td>
<td class="t20data">B2</td>
<td class="t20data">075</td>
<td class="t20data">A1</td>
<td class="t20data">013</td>
<td class="t20data">KKDPVL0062X0018</td>
<td class="t20data">004</td>
<td class="t20data">DP-DL</td>
</tr>
</tbody>
</table>
From VBA I am not able to access this.
By the way, I am using IE6 and I have to do this in IE6 only, and the above HTML code, I obtained from other system through Google chrome, using "Inspect".
But in IE6, I can only see "View Source" and here, it is not displaying the above generated HTML.
Code:
Sub scrape()
Dim ie As Object
Dim baseurl As String
Set ie = CreateObject("internetexplorer.application")
With ie
.navigate "http://itpc.sdc.bsnl.co.in:7777/pls/apex/f?p=204:1"
.Visible = True
End With
Do While ie.Busy
DoEvents
Loop
ie.document.getElementById("P101_USERNAME").Value = "b200800935"
ie.document.getElementById("P101_PASSWORD").Value = "xxxxxxxx"
Do While ie.Busy
DoEvents
Loop
For Each l In ie.document.getElementsByTagName("a")
If l.className = "t20Button" Then
l.Click
Exit For
End If
Next
Do While ie.Busy
DoEvents
Loop
'baseurl = Replace(ie.LocationUrl, "204", "124")
'MsgBox ie.LocationUrl
'MsgBox Replace(ie.LocationUrl, "204", "124") & "::NO::HOME_APP,HOME_PAGE:204,1"
ie.navigate Replace(ie.LocationURL, "204", "124") & "::NO::HOME_APP,HOME_PAGE:204,1"
Do While ie.Busy
DoEvents
Loop
ie.document.getElementById("PHONE_NO").Value = "04565-282200"
ie.document.getElementById("P1_GO").Click
Do While ie.Busy
DoEvents
Loop
For Each l In ie.document.getElementsByTagName("a")
If l.innerText = "04565-282200" Then
l.Click
Exit For
End If
Next
Do While ie.Busy
DoEvents
Loop
ie.document.parentWindow.execScript "ajaxcall('CIRCUITS','CKT_LINK','CIRCUITS')"
' THE CODE IS OK UPTO THIS
'THE ABOVE EXECUTION OF JAVASCRIPT FUNCTION CREATES A DYNAMIC TABLE IN WEB PAGE
'WHICH IS NOT ACCESSIBLE IN VBA BY MY CODE
''ie.Quit
''Set ie = Nothing
End Sub
Thank you all for your help. I actually got what i wanted by adding the following line of code. After the last line that executed the java script, I added the following line right after the execScript():
document.getElementbyId(‘div id name’).innerHTML.
This ‘div’ was generated through the script, which was visible in the “View Source”. Through this , I was able to access the table.
It returned a string containing the ‘Table…/Table’ portion within that ‘div’. and i extracted the data through string manipulation. Don’t know, how it worked, but i got what i want. Thank you all for your valuable suggestion.