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

0 votes

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? 

Sep 20 in Others by Kithuzzz
• 20,660 points
64 views

1 answer to this question.

0 votes

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.

answered Sep 21 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10 in Others by gaurav
• 22,040 points
282 views
0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
6,064 views
0 votes
1 answer

How to open .xlsx files in MS Excel from VS Code?

Hello, to open xlxs files, or files ...READ MORE

answered Feb 17 in Others by gaurav
• 22,040 points
215 views
0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
42 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

"Runtime Error 91: Object variable or with ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
3,787 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

When there was no unhidden workbook open ...READ MORE

answered Apr 6 in Database by gaurav
• 22,040 points
186 views
0 votes
1 answer

I have a problem with counta function in VBA

When the worksheet name has a space, ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
47 views
0 votes
1 answer

Excel VBA - Trouble importing WhatsApp chat history files into an Excel sheet

Since the OpenText method isn't working for ...READ MORE

answered Oct 14 in Others by narikkadan
• 37,660 points
70 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
98 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP