Unable to import data in excel from another website using VB code

Question

I am trying to import some data in excel from a website using VB code. During the course of testing first got an error 438 in the login section which was rectified by respected Mr. Siddharth Rout (Thanks again) through my earlier question Runtime error 438 while importing data in excel from the secured website using VBA. Now i am facing error 438 in the following section and am unable to import data/excel sheet remain blank.

'get the table based on the table’s id
     Set ieDoc = ieApp.Document
     Set ieTable = ieDoc.all.Item("report-table")

     'copy the tables html to the clipboard and paste to the sheet
     If Not ieTable Is Nothing Then
         Set clip = New DataObject
         clip.SetText "<html>" & ieTable.outerHTML & "</html>"
         clip.PutInClipboard
         Sheet1.Select
         Sheet1.Range("A1").Select
         Sheet1.PasteSpecial "Unicode Text"
     End If

Complete code is as under:

Sub GetTable()

     Dim ieApp As InternetExplorer
     Dim ieDoc As Object
     Dim ieTable As Object
     Dim clip As DataObject

     'create a new instance of ie
     Set ieApp = New InternetExplorer

     'you don’t need this, but it’s good for debugging
     ieApp.Visible = True

     'assume we’re not logged in and just go directly to the login page
     ieApp.Navigate "http://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/LoginAction.do?hmode=loginPage"
     Do While ieApp.Busy: DoEvents: Loop
     Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

     Set ieDoc = ieApp.Document

     'fill in the login form – View Source from your browser to get the control names
     With ieDoc
    .getElementById("userId").setAttribute "value", "rlbdgs"
    .getElementById("userPassword").setAttribute "value", "123"

    '~~> This will select the 2nd radio button as it is `0` based
    .getElementsByName("userType")(1).Checked = True

    .getElementById("hmode").Click
     End With
     Do While ieApp.Busy: DoEvents: Loop
     Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

     'now that we’re in, go to the page we want
     ieApp.Navigate "http://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/GeneralReportAction.do?hmode=drillDown25And26And30GeneralReport&kioskOrManual=K&val=26&wherePart=ZONE_CODE_C=-IR-&lobby=AJJ&type=B&startDate=&endDate=&traction=ELEC"
     Do While ieApp.Busy: DoEvents: Loop
     Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

     'get the table based on the table’s id
     Set ieDoc = ieApp.Document
     Set ieTable = ieDoc.all.Item("report-table")

     'copy the tables html to the clipboard and paste to the sheet
     If Not ieTable Is Nothing Then
         Set clip = New DataObject
         clip.SetText "<html>" & ieTable.outerHTML & "</html>"
         clip.PutInClipboard
         Sheet1.Select
         Sheet1.Range("A1").Select
         Sheet1.PasteSpecial "Unicode Text"
     End If

     'close 'er up
     ieApp.Quit
     Set ieApp = Nothing

 End Sub

Source code of webpage containing the table (from data to be downloaded) is as

<html>
<head>
<title>CREW BOOKED ON TA</title>

<link href="../styles/reportStyle.css" rel="stylesheet" type="text/css" />

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

 <script type="text/javascript">
  function DoNav(theUrl)
  {
    //alert(theUrl);
    document.location.href = theUrl;
  }
  </script>
</head>
<body>
<table id="report-table">
    <!-- Table header -->
        <thead>
            <tr>
                <th scope="col" class="date" style="border:0px;" colspan="10">Print Date Time: <span>14-08-2014 13:30</span></th>
            </tr>
            <tr>
                <th scope="col"  class="report-cris" style="text-align:center;">CRIS</th><th scope="col" class="report-heading" style="text-align:center;" colspan="8">VIGILENCE CONTROL DEVICE (VCD) IN LOCO NOT WORKING(SIGN OFF THROUGH KIOSK)(LOCO SHED WISE)(LAST 24 HOURS)<th scope="col"  class="report-cris" style="text-align:center;">CMS</th>
            </tr>
            <tr style="border:none;">
                <th colspan="9" style="border-right:none;">
                                <span class="report-button" onclick="javascript:history.back();">BACK</span>
                                <span class="report-button" onclick="javascript:window.print();">PRINT</span>
                </th>
                <th style="border-left:none;text-align:right;"></th>
            </tr>
        </table>
    <table id="report-table">
    <thead>
        <tr style="border:none;" align="center">
                <th>S.No.</th>
                <th>ID</th>
                <th>NAME</th>
                <th>SIGNOFF DATE</th>
                <th>FROM</th>
                <th>TO</th>             
                <th>LOCO NO.</th>
                <th>BASE SHED</th>
                <th>RAILWAY</th>
            </tr>
        </thead>
        <tbody>

                    <tr>
                        <td>1</td>                              
                        <td>BINA1482</td>
                        <td >RAKESH KUMAR BAJPAI</td>
                        <td>14-08-2014 11:07</td>
                        <td >BINA</td>
                        <td>ET  </td>                       
                        <td>23551   </td>
                        <td>BRC</td>
                        <td>WR  </td>                       
                    </tr>                   

            </tbody>
</table>
* If duration for this report is last 24 hours or from and to date is same, then only last VCD reporting of the loco will be shown.
</body>
</html>

Can someone please help me with this?

narikkadan · Answer 1 · Sep 21, 2022

Replace :

Set ieTable = ieDoc.all.Item("report-table")

With:

Set ieTable = ieDoc.getElementById("report-table")

It appears that the outer HTML attribute is not supported by the DispHtmlElementCollection that the Item function delivers. In its place, the getElementById returns an HTML table element.