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

0 votes

I am trying to import some data in MS excel from a website (login ID & password have been provided to me) using the following VBA code (downloaded from another website)

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.forms(0)
    .userType.Value = "1"
     .userId.Value = "rlbdgs"
     .userPassword.Value = "123"
     .submit
 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/LoginAction.do?hmode=login"
 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("sampletable")

 'copy the tables html to the clipboard and paste to teh 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

While i am trying to run the Code, I am getting 'Runtime error 438 Object doesn't support this property or method' at this area

 With ieDoc.forms(0)
    .userType.Value = "1"
     .userId.Value = "rlbdgs"
     .userPassword.Value = "123"
     .submit
 End With

Source code of login page is as under:

    <html>
    <head>
    <title>Report User Login</title>
    <link href="../styles/loginStyle.css" rel="stylesheet" type="text/css" /> 
    <script src="../scripts/jquery-1.11.0.js"></script>
    <script type="text/javascript">
    javascript:window.history.forward(1);
    function trim(str)
    {
    return str.replace(/^\s*|\s*$/g,"");
    }


    function getUserType()
    {   
    if(typeof(Storage)!=="undefined")
    {       
        if (localStorage.userType)
        {               
            document.forms[0].userType[localStorage.userType].checked = true ;
            if (localStorage.userType == '0')
            {
                document.forms[0].userId.value="";
                document.forms[0].userPassword.value="";
            }
            else
            {
                document.forms[0].userId.value="";
                document.forms[0].userPassword.value="";
            }
        }
        else
        {
            document.forms[0].userType[0].checked = true ;
            document.forms[0].userId.value="";
            document.forms[0].userPassword.value="";
        }       
    }
    else
    {
        localStorage.userType = "0";
        //document.getElementById("result").innerHTML="Sorry, your browser does not support web storage...";
        //alert("Sorry, your browser does not support web storage...");
    }
     }

     function setUserType(thisObj)
     {      
    localStorage.userType = thisObj.value;
    if (localStorage.userType == '0')
    {
        document.forms[0].userId.value="";
        document.forms[0].userPassword.value="";
    }
    else
    {
        document.forms[0].userId.value="";
        document.forms[0].userPassword.value="";
    }
     }

     function callAction()
     {      
    document.forms[0].action ="../../JSP/rpt/LoginAction.do?hmode=login";
    document.forms[0].submit();     

     }
     </script>

      <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
      <link href="../styles/links.css" rel="stylesheet" type="text/css">
      <link href="../styles/text.css" rel="stylesheet" type="text/css">
        </head>

      <body onLoad="getUserType();" bgcolor="#e3e3e3" >
      <script type="text/javascript">
      <!--
      //alert('screen.width:'+screen.width)
      if (screen.width < 499 || screen.height < 499) {
    alert('Your Resolution: ' + screen.width + ' X ' + screen.height + '. This Application can only be run on Resolution: >= 499 X 499.')
    document.location = "/M";
      }
      //-->
     </script>
     <form name="reportLoginForm" method="post" action="/CMSREPORT/JSP/rpt/LoginAction.do">
      <table width="800" height="600" border="0" align="center" cellpadding="0" cellspacing="0">
     <tr>
    <td width="800" height="2" align="center" valign="top" bgcolor="#FFFFFF"></td>
      </tr>
     <tr>
    <td height="" align="left" valign="top" bgcolor="#FFFFFF"><table width="800" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td width="1" align="left" valign="top" bgcolor="#FFFFFF">&nbsp;</td>
        <td width="497" align="left" valign="top" bgcolor="#FFFFFF"><img width="497" height="200" src="images/backimage.jpg"/> </td>


<td width="1" align="left" valign="top" bgcolor="#FFFFFF">&nbsp;</td>
        <td align="left" valign="top" bgcolor="#FFFFFF"><table width="300" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td height="30" align="center" valign="middle" bgcolor="#333333">&nbsp;<font color="white" size="4">CMS REPORT LOGIN</font>
                                        </td>
          </tr>
          <tr>
            <td height="170" align="left" valign="top"><form>
              <table width="300" border="0" cellspacing="0" cellpadding="0">
                <tr>
                  <td height="35" colspan="3" align="left" valign="middle">
                  <input type="radio" name="userType" value="0" onclick="setUserType(this);"><i>MOBILE/REPORT USER</i>
                    <br>
                    <input type="radio" name="userType" value="1" onclick="setUserType(this);"><i>LOBBY/DIVISION/ZONE USER</i>
                    <br>
                    <input type="radio" name="userType" value="2" onclick="setUserType(this);"><i>KIOSK CREW LOGIN</i>
                    </td>
                </tr>

                <tr>
                  <td height="17" colspan="3" align="left" valign="top"></td>
                </tr>
                <tr>
                  <td width="80" align="center" valign="middle"><i><b>USER ID:</b></i></td>
                  <td width="2" align="left" valign="top">&nbsp;</td>
                  <td width="200" align="left" valign="top"><input type="text" name="userId" value=""></td>
                </tr>
                <tr>
                  <td height="15" colspan="3" align="left" valign="top">&nbsp;</td>
                  </tr>
                <tr>
                  <td align="center" valign="middle"><i><b>PASSWORD:</b></i></td>
                  <td align="left" valign="top">&nbsp;</td>
                  <td align="left" valign="top"><input type="password" name="userPassword" value=""></td>
                </tr>
                <tr>
                  <td colspan="3" align="left" valign="top"><b><i><font color="red"><br/>
                        </font>
                    </i>
                    </b><script type="text/javascript">document.write('Resolution: '+screen.width+' X '+screen.height)</script>
                    <a href="/CMSTEST"></a></td>
                  </tr>
                <tr>
                  <td align="left" valign="top" bgcolor="#333333">&nbsp;</td>
                  <td align="left" valign="top" bgcolor="#333333">&nbsp;</td>
                  <td height="36" align="center" valign="middle" bgcolor="#333333"><input type="submit" name="hmode" value="Login" onclick="return callAction();"></td>
                </tr>

                </table>
            </form></td>
          </tr>
        </table></td>
        <td width="1" align="left" valign="top" bgcolor="#FFFFFF"></td>
      </tr>
      <tr>
        <td align="left" valign="top" bgcolor="#FFFFFF"></td>
        <td height="2" colspan="3" align="left" valign="top" bgcolor="#FFFFFF"></td>
        <td align="left" valign="top" bgcolor="#FFFFFF"></td>
      </tr>
      <tr>
        <td align="left" valign="top" bgcolor="#FFFFFF"></td>
        <td colspan="3" align="left" valign="top" bgcolor="#FFFFFF"><table width="798" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td><table width="798" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td width="300"><table width="300" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td width="300" height="50" align="left" valign="top"><img src="images/news.jpg" /></td>
                  </tr>
                  <tr>
                    <td height="150" align="left" valign="top"><table width="300" border="0" cellspacing="0" cellpadding="0">
                      <tr>
                        <td height="160" align="left" valign="top" bgcolor="#CCCCCC"><div id="sld">
                  <marquee  behavior="scroll" width="300px;" height="150px;" scrollamount="3" direction="up">
                   Welcome to CMS Report Login Page. Now you have to enter user id and password to see CMS Reports.
                    </marquee>
                </div></td>
                      </tr>
                    </table></td>
                  </tr>
                </table></td>
                <td width="2">&nbsp;</td>
                <td align="center" valign="top"><h1 class="text" align="center"><b>Welcome To CMS</b></h1>                  
                  <p class="text">CMS project was started in 2005 with a dedicated team of 5 fresh software developers. With the passage of time the CMS system has bloomed out with the support of over 25 dedicated software engineers contributing in it's development. Leaders in the software industry like IBM, HCL, ITI, Redhat, MIND, Neoware, VXL, Anologics, Parrus IT, Palas, 3M have been contributing their resources in it's development.
                                    At present the software has Freight, Shunter, Coaching, SMS, Loco Inspector, QUICK and many more modules in use. New immerging technologies and challenges like Remote Management of the thin client from a centralised location and time syncing of all the clients to one time standard have been adopted in the CMS system.
                                    The project has been spread out to entire Indian Railways with nearly 300 lobby location and is used by over one lakh users regularly. The team has taken a leap forward by launching Electrical Loco management software in June 2009 on Jhansi Division. The Electrical Loco management software is being developed by the same team and is likely to be fully developed by June 2010.  
                  </p>
                  </td>
              </tr>
            </table></td>
          </tr>
        </table></td>
        <td align="left" valign="top" bgcolor="#FFFFFF">&nbsp;</td>
      </tr>
    </table></td>
  </tr>
</table>
</form>
</body>
</html>

What changes are required in the VBA code? Please help.

Sep 22, 2022 in Others by Kithuzzz
• 38,010 points
650 views

1 answer to this question.

0 votes

Replace

With ieDoc.forms(0)
   .userType.Value = "1"
    .UserId.Value = "rlbdgs"
    .userPassword.Value = "123"
    .submit
End With

with

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
answered Sep 23, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel: importing data from another Excel file using VBA

Refer to the file variables folderName and ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
428 views
0 votes
1 answer

Activating a Specific Cell in Excel Using VBA Results to Error 400

I think you trying to select cells(4, ...READ MORE

answered Dec 27, 2022 in Others by narikkadan
• 63,420 points
331 views
0 votes
1 answer

Using Visual Basic to pull data from within a range to use in an Excel function

Use AVERAGEIFS instead of the full range. ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,420 points
286 views
0 votes
1 answer

Getting data out of a cell with a #NAME? error in Excel VBA

If you need VBA, use .Formula: Dim f As ...READ MORE

answered Feb 3, 2023 in Others by narikkadan
• 63,420 points
336 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, 2022 in Database by gaurav
• 23,260 points
17,256 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, 2022 in Database by gaurav
• 23,260 points
645 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, 2022 in Others by narikkadan
• 63,420 points
635 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, 2022 in Others by narikkadan
• 63,420 points
397 views
0 votes
1 answer

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

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,420 points
472 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,386 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