VBA - How to enter username, password and click the submit button in webpage

Question

Hi I am new to VBA and I am trying to automatically login into a webpage using VBA and I was successful for few websites but for this particular webpage I am unable to do it

Below is my VBA code :

Sub login ()
    Set IE = CreateObject("InternetExplorer.application")
    IE.AddressBar = 0
    IE.StatusBar = 0
    IE.Toolbar = 0
    IE.Visible = True
    IE.navigate "www.sample.com"
    Do Until Not IE.busy: DoEvents: Loop
    Set doc = IE.document
    Do While doc.ReadyState <> "complete": DoEvents: Loop
    IE.document.all("ocsid").Value = "xx"
    IE.document.all("password").Value = "yy"
    IE.document.all("Submit").Click
End Sub

Here is the HTML code of the login area which i copied from the web page

    </TR>
    <TR>
    <TD width="165"><FONT FACE="Arial, Helvetica" SIZE=2><b>OCS Id:</b></FONT></TD>
    <TD align="center" width="95"><INPUT size="15" type="text" maxlength="10" name="ocsid"></TD>
     </TR>
     <TR>
     <TD width="165"><FONT FACE="Arial, Helvetica" SIZE=2><b>OCS Password</b></FONT>:</TD>
     <TD align="center" width="95"><INPUT size="15" type="password" maxlength="8" name="password"></TD>
     </TR>
     <TR>
     <TD width="165"><FONT FACE="Arial, Helvetica" SIZE=2><b>X500 Id:</b></FONT></TD>
     <TD align="center" width="95"><INPUT size="15" type="text" maxlength="10" name="x500id"></TD>
     </TR>

    <TR>
    <TD height="15" width="165"></TD>
    <TD width="95"></TD>
    </TR>
    <TR>
    <TD align="center" width="165" colspan="2"><INPUT type="checkbox" name="changePasswordBox"  onclick="changePassword2();" />
    <FONT FACE="Arial, Helvetica" SIZE=2><b>Change Password</b></FONT></TD>
    </TR>                    
    </TABLE>
    <div id="HideArea" >
    <TABLE width="287">
    <TR>
    <TD width="165"><FONT FACE="Arial, Helvetica" SIZE=2><b>New Password:</b></FONT></TD>
    <TD align="center" width="95"><INPUT size="15" type="password" maxlength="8" name="newpassword"></TD>
    </TR>

<TR>
<TD width="165"><FONT FACE="Arial, Helvetica" SIZE=2><b>Verify New Password:</b></FONT></TD>
<TD align="center" width="95"><INPUT size="15" type="password" maxlength="8" name="VERIFY_NEW_PWD" ></TD>
</TR>
<TR>
</TABLE>
</div>    
<TABLE width="287">
<TR>
<TD height="15" width="165"></TD>
<TD width="95"></TD>
</TR>
<TR>
<TD colspan="2" align="center"><input type="submit" name="Submit" value="Submit" onclick="if (top.frames[0] && top.frames[0].animate) top.frames[0].animate.start();" style="width: 60px">&nbsp;&nbsp;&nbsp;
<INPUT style="width: 60px" type="button" name="Reset" value="Reset" onClick="resetPage();"></TD>
</TR>

I hope this will help you to answer my question !!

Comments :- I am so sorry !! I do not understand what you are asking for ? But i just found out something, when i do a right click on the page using chrome it gives an extra option as "View Frame Source", normally all other website which i successfully automated the above process will have only one option as "View page source" using which i will find the "ID" and "Tags" and so on .... And when i click the "View page source" option I do not see any "ID" and "Tags" instead I have only links which end with .jsp format and when i click the link it takes me to the part of the webpage (like login area) (Legal Notice and Privacy Policy).... I am not sure whether this will help you or not !! But i am truly sorry and I Thank you a lot, for your work !!!!

Does the code produce an error? If so, where? What is the actual website (I'm assuming www.sample.com is not it)? Given that it works on some websites but not on one specific other one, means that website might be set up differently... — rohrl77, Nov 16 '16 at 07:36
Yes it does gives me an error "Run Time Error - 424 Object required" and this happens in this step (IE.document.all("ocsid").Value = "xx") !!! I am sorry the website is confidential I will not be in a position to share it !!! Yes this website is designed differently, the other websites will have "ID" or "Class" in the Tags but this doe not !!! so I am stuck !!! — David, Nov 16 '16 at 08:04
Without being able to test against the actual site, any advice would just be clutching at straws. — Skip Intro, Nov 16 '16 at 13:56
Thanks for your reply !! As i said i cannot share the address see if this is helpful, i have pasted the page source code of the login area in the original post !! — David, Nov 17 '16 at 05:55

Lukas · Answer 1 · 2016-11-17T13:23:41.950

1

Not sure if it is OK to post this as an answer but what about selecting the input fields by name using getElementsByName?

Sub login()
    Dim IE As Object
    Dim doc As Object
    Set IE = CreateObject("InternetExplorer.application")
    IE.AddressBar = 0
    IE.StatusBar = 0
    IE.Toolbar = 0
    IE.Visible = True
    IE.navigate "www.sample.com"
    Do Until Not IE.busy: DoEvents: Loop
    Set doc = IE.document
    Do While doc.ReadyState <> "complete": DoEvents: Loop
    doc.GetElementsByName("ocsid")(0).Value = "xx"
    doc.GetElementsByName("password")(0).Value = "xx"
    doc.GetElementsByName("Submit")(0).Click
End Sub

Tested with this html file:

<input size="15" type="text" maxlength="10" name="ocsid">
<input size="15" type="password" maxlength="8" name="password">
<input type="submit" name="Submit" value="Submit" onclick="alert('it works')">

edited Nov 17 '16 at 13:23

answered Nov 16 '16 at 20:57

Lukas

148
2
12

Thanks for your reply !! however i am getting an error at this code "IE.document.getElementsByName("OCS Id")(0).Value = "xx"" the error is "Run time error - 91" Object variable or with block variable not set" Also i understand that with out the actually URL it is hard to give an answer !!! – David Nov 17 '16 at 05:45
Have you added the "Microsoft HTML Object Library"-reference to your VBA Project? – Lukas Nov 17 '16 at 06:08
Actually I have not !! but even after adding it and other 2 options which are "Microsoft Internet Controls" and "Microsoft Forms 2.0 Object Library" and then saved and reopened and tried to use the code but still i am getting the error at this code (IE.document.getElementsByName("ocid")(0).Value = "xx") as "Run time error - 91" Object variable or with block variable not set" !!! – David Nov 17 '16 at 06:30
Also an another question do i have to add "Microsoft HTML Object Library", "Microsoft Internet Controls" and "Microsoft Forms 2.0 Object Library" in every excel i work ??? I am totally new to VBA, sorry to ask you so many questions !!! – David Nov 17 '16 at 06:30
I tested your code and got an error because IE and doc haven't been initialized. With the initialized values the code is working fine on my PC with Excel 2010 and IE11. It even works without the Microsoft HTML Library. In General, afaik, if you need to add a library you have to do that once for the Project. If you open the same file on another PC, the library is still there. – Lukas Nov 17 '16 at 13:21
Clarification: I haven't tested your code but the code that I entered in my answer. – Lukas Nov 17 '16 at 13:25
Hi Lukas, !!! Thanks for your valuable reply and i respect your efforts !!! However it does not seem to work for me when i copied your code and executed it, i am also using WIN 8, Excel 2007 and IE 10. I know i must be doing something wrong kindly assist me!! i am getting error on this line (doc.GetElementsByName("ocsid")(0).Value = "xx") which says "Object variable or with block variable not set" – David Nov 18 '16 at 09:00
Can you maybe share at least the structure of the website (only the tags with classes, ids and other attributes)? Without that I am afraid I cannot help further. – Lukas Nov 18 '16 at 17:10
(Please see my actually post (at the end)) – David Nov 21 '16 at 06:15
I'm afraid I cannot help you with this. Regarding .jsp maybe this post helps you (even though it is refered to another error-message):http://stackoverflow.com/questions/36747051/submitting-a-java-script-form-for-jsp-site-through-vba – Lukas Nov 21 '16 at 15:26

VBA - How to enter username, password and click the submit button in webpage

1 Answers1