Hi,
I am trying to scrap data from a website. I got to the point where i enter some data and click submit, then a table opens below the submit button, i want to click on the value in the 2nd row 7th column to move further.
After running the above VBA, i want to be able to click on the 'download' in the above HTML code to download the XML file but i can't seem to figure it out. I tried the below code but it gives "Run Time error 91 Object variable or with block variable not set' error.
Can someone please help me with the code?
I am trying to scrap data from a website. I got to the point where i enter some data and click submit, then a table opens below the submit button, i want to click on the value in the 2nd row 7th column to move further.
VBA Code:
Option Explicit
Const sSiteName = "some_website"
Private Sub CommandButton1_Click()
Dim oIE As Object
Dim oHDoc As HTMLDocument
Set oIE = CreateObject("InternetExplorer.Application")
With oIE
.Visible = True
.navigate sSiteName
End With
While oIE.readyState <> 4
DoEvents
Wend
Set oHDoc = oIE.document
With oHDoc
.getElementById("formID").Value = "58"
.getElementById("bankName").Value = "1273"
.getElementById("period").Value = "31-MAR-2020"
.getElementById("toDate").Value = "31-MAR-2020"
oIE.document.forms(0).submit
Application.Wait (Now + TimeValue("0:00:05"))
End With
End Sub
HTML:
<div id="format">
<fieldset class="ff" style="width: 565px">
<div class="fm-optional">
<table cellspacing="0" cellpadding="4" rules="all" border="1"
id="ctl00_ContentPlaceHolder1_GridView1"
style="width: 100%; border-collapse: collapse;">
<tr
style="color: White; background-color: #403F36; fofont-weight: bold; font-size: 12px">
<th scope="col" width="28%">
Uploaded Return
</th>
<th scope="col" width="16%">
Reporting Date
</th>
<th scope="col" width="16%">
Date Uploaded
</th>
<th scope="col" width="16%">
Reporting Status
</th>
<th scope="col" width="6%">
IsRevised
</th>
<th scope="col" width="16%">
Processing Status
</th>
<th scope="col" width="16%">
Download Files
</th>
</tr>
<tr class="subhead" align="center"
style="background-color: #EFF3FB;">
<td class="subhead" align="center" style="width: 5%;">
<a target="_blank"
href="/ABCD/tiles/viewDetailTile.jsp?enc=">
DATA.xml </a>
</td>
<td class="subhead" align="center" style="width: 5%;">
31-MAR-2020
</td>
<td class="subhead" align="center" style="width: 5%;">
12-NOV-2020 15:59:20
</td>
<td class="subhead" align="center" style="width: 5%;">
Final
</td>
<td class="subhead" align="center" style="width: 4%;">
No
</td>
<td class="subhead" align="center" style="width: 5%;">
Completed
</td>
<td class="subhead" align="center" style="width: 5%;">
<a href="/ABCD/DownloadFileServlet?File=FileName.xml&id=2837008&formId=58&fileStatus=Completed">
<span class="glyphicon glyphicon-download" style="cursor: pointer;">download</span>
</a>
</td>
</tr>After running the above VBA, i want to be able to click on the 'download' in the above HTML code to download the XML file but i can't seem to figure it out. I tried the below code but it gives "Run Time error 91 Object variable or with block variable not set' error.
VBA Code:
oIE.document.getElementsById("ct100_ContentPlaceHolder1_GridView1")(1).getElementsByTagName("tr")(1).getElementsByTagName("td")(5).ClickCan someone please help me with the code?
