Solved

Help logging into a website via EXcel 2010

Posted on 2013-05-26
14
370 Views
Last Modified: 2014-11-12
I am trying to set up a way to log into website similar to logging into Gmail using Excel 2010. After enabling Microsoft HTML Object Library and Microsoft Internet Controls through  the Tools > Reference menu in VB and then using the code below I've gotten as far as navigating to the page and filling in the filed. Where I am stuck is on is click the submit button. After getting past the login screen I then need to be able to navigate to another page. Also, if there is a cookie dropped on login that prevents having to login again I would like a way to check to see if I am logged in and then move onto the next step of navigating to the next page. Any help would be greatly appreciated.  

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Website_Login_Test()
 
Dim oHTML_Element As IHTMLElement
Dim sURL As String
 
On Error GoTo Err_Clear
sURL = "https://gmail.com"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True
 
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
 
Set HTMLDoc = oBrowser.document

 
HTMLDoc.all.Email.Value = "username"
HTMLDoc.all.Passwd.Value = "password"
 
For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
Debug.Print oHTML_Element.Name
Next
 
' oBrowser.Refresh ' Refresh If Needed
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub

Open in new window

0
Comment
Question by:futr_vision
  • 9
  • 5
14 Comments
 

Author Comment

by:futr_vision
Comment Utility
I found this code too however the submit button doesn't use a name. It looks like this:

<input type="image" border="0" title=" Submit " alt="Submit" src="includes/languages/english/images/buttons/button_confirm.gif">


'Needs references to Microsoft HTML Object Library and Microsoft Internet Controls

Option Explicit

Sub Test()

    Const cURL = "https://gmail.com"
    Const cUsername = "XXXX"    'REPLACE XXXX WITH YOUR USER NAME
    Const cPassword = "YYYY"    'REPLACE YYYY WITH YOUR PASSWORD
    
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim LoginForm As HTMLFormElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    
    Set IE = New InternetExplorer
    
    IE.Visible = True
    IE.navigate cURL
    
    'Wait for initial page to load
    
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    Set doc = IE.document
    
    'Get the only form on the page
    
    Set LoginForm = doc.forms(0)
    
    'Get the User Name textbox and populate it
    '< input name="ctl00$ct$UserName" type="text" maxlength="30" id="ctl00_ct_UserName" style="width:160px;" />
 
    Set UserNameInputBox = LoginForm.elements("email_address")
    UserNameInputBox.Value = cUsername
    
    'Get the password textbox and populate it
    '< input name="ctl00$ct$Password" type="password" maxlength="30" id="ctl00_ct_Password" style="width:160px;" />

    Set PasswordInputBox = LoginForm.elements("Password")
    PasswordInputBox.Value = cPassword
    
    'Get the form input button and click it
    '< input type="submit" name="ctl00$ct$uxBtnLogin" value="Sign In" o n c l i c k="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$ct$uxBtnLogin", "", true, "Login", "", false, false))" id="ctl00_ct_uxBtnLogin" />
    
    Set SignInButton = LoginForm.elements("ctl00$ct$uxBtnLogin")
    SignInButton.Click
            
    'Wait for the new page to load
    
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    'Get the HTML document of the new page
    
    Set doc = IE.document
    
    'Determine whether login succeeded or not
    
    If InStr(doc.body.innerText, "Invalid Login Information") = 0 Then
        MsgBox "Login succeeded"
    Else
        MsgBox "Login failed"
    End If
        
    Debug.Print "Current URL: " & IE.LocationURL

End Sub
        

Open in new window

0
 
LVL 20

Expert Comment

by:ltlbearand3
Comment Utility
You stat you are coding to a website similar to GMail.  This code will need to be adapted to use the elements from that website.  Using you first piece of code, you can use the Debug.Print statements to help you find the element names that you need.  I ahve adapted the first piece of code to click the submit button for you.

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Website_Login_Test()
 
Dim oHTML_Element As IHTMLElement
Dim sURL As String
 
On Error GoTo Err_Clear
sURL = "https://gmail.com"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True
 
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
 
Set HTMLDoc = oBrowser.document

 
HTMLDoc.all.Email.Value = "username"
HTMLDoc.all.Passwd.Value = "password"
HTMLDoc.all.signIn.Click
 
For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
    Debug.Print oHTML_Element.Name
Next
 
' oBrowser.Refresh ' Refresh If Needed
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub
                                  

Open in new window


-Bear
0
 
LVL 20

Expert Comment

by:ltlbearand3
Comment Utility
I have adapted the code a little more with your second request.  For gmail if you recieved this page "https://accounts.google.com/ServiceLoginAuth", you login failed and I display a message box

Sub Website_Login_Test()
 
    On Error GoTo Err_Clear
    
    Dim HTMLDoc As HTMLDocument
    Dim oBrowser As InternetExplorer
    Dim sURL As String
 
    sURL = "https://gmail.com"
    Set oBrowser = New InternetExplorer
    oBrowser.Silent = True
    oBrowser.timeout = 60
    oBrowser.navigate sURL
    oBrowser.Visible = True
     
    Do
    ' Wait till the Browser is loaded
    Loop Until oBrowser.readyState = READYSTATE_COMPLETE
     
    Set HTMLDoc = oBrowser.document
    
     
    HTMLDoc.all.Email.Value = "username"
    HTMLDoc.all.Passwd.Value = "password"
    HTMLDoc.all.signIn.Click
    
    ' Check if we received the error page
    If HTMLDoc.URL = "https://accounts.google.com/ServiceLoginAuth" Then
        MsgBox "Login failed"
    Else
        MsgBox "Login succeeded"
    End If
        
Err_Clear:
    If Err <> 0 Then
    Err.Clear
    Resume Next
    End If
End Sub
                                  

Open in new window


-Bear
0
 

Author Comment

by:futr_vision
Comment Utility
Thanks! As I mentioned I'll be logging into a site similar to logging into a site like Gmail. i just used gmail in the code as an example. The problem i think i am running into is that there is no name on the button I which to click. Here is the code for the button.

<input type="image" border="0" title=" Submit " alt="Submit" src="includes/languages/english/images/buttons/button_confirm.gif">
0
 

Author Comment

by:futr_vision
Comment Utility
Ahh. Debug.Print gives me a value of "fsubmit" which appears to work in the code. Excellent!
0
 

Author Comment

by:futr_vision
Comment Utility
Ok. So your second bit of code doesn't quite work as expected. What I am looking to do is to not check to see if I received an error page. Most of the time the login is going to work because the user credentials are hard-coded and not prone to user error. Because a cookie is dropped and I do not have to log in every time I visit the site. What I think needs to happen is that my code first checks to see if the page I want to go to is accessible or not. If it is I go straight to that page and continue one with the rest of my code. If it is not accessible I will be pushed to the login screen where the code above fills out the fields and submits. Once I have successfully logged in I would then want to navigate to the page I first intended. Does that make sense? So in simple terms it looks like this.

ABC.com is a reporting system where a user can log on an run reports on system performance based on criteria selected through dropdowns and date fileds.

1. Go to page ABC.com/report1
2. If I can go to ABC.com/report1 directly then run a specified query like you see in this video(http://www.youtube.com/watch?v=qbOdUaf4yfI)
3. If I can not navigate directly to ABC.com/report1 the system will kick me out to the login screen(ABC.com/login) where I would want to auto-populate and submit like we do with the previous code.
4. Once I am successfully logged in the system pushes me to a menu page(ABC.com/main)
5. I would then want to automatically redirect to ABC.com/report1

Now, I am not sure if it even matters if I redirect to ABC.com/report1 or not. I think the method outline in the video i included may take care of that for me. Then again, if i want to enter in the filed data using controls in Excel I may need to. I'm a novice when it comes to macros and VBScript so I am relying on the experts here to point me in the right direction.
0
 
LVL 20

Expert Comment

by:ltlbearand3
Comment Utility
This will be a little tough to help as coding to scrape data from web sites gets to be pretty specific to each web site and we are running blind here.

here is kind of the pseudo code I would recommend for now

Attempt to navigate to ABC.com/report1

If not successfull then
Run Log In Script
Navigate to abc.com/report1
End If

Run Data Extraction

The idea with the if statement is to only run the log in code if needed and then end up at the same place you would even if you did not need to log in.  I hope this helps, but without exact details or a more full code sample, it is difficult.

-Bear
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:futr_vision
Comment Utility
I was thinking we'd simply expand on the code above. Add a check in there to see if I can get to ABC.com/report1 and if not run the log in code. After the log in code is complete then I would do the data extraction. I think I can handle that using tutorial butI am not sure how to move onto that step after logging in. Is that a separate sub?
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
Comment Utility
Well using the Google idea you can try two things.  First one is two navigate to the page you want and check to see if you got there  (sample below) or the second is to navigate to the page you want and to check if you got to the log in page.

Sub Website_Login_Test()
 
    On Error GoTo Err_Clear
    
    Dim HTMLDoc As HTMLDocument
    Dim oBrowser As InternetExplorer
    Dim sURL As String
 
 
    sURL = "https://mail.google.com/mail/?shva=1#inbox"
    'sURL = "https://gmail.com"
    Set oBrowser = New InternetExplorer
    oBrowser.Silent = True
    oBrowser.timeout = 60
    oBrowser.navigate sURL
    oBrowser.Visible = True
     
    Do
    ' Wait till the Browser is loaded
    Loop Until oBrowser.readyState = READYSTATE_COMPLETE
     
    Set HTMLDoc = oBrowser.document
    
    Debug.Print HTMLDoc.URL
    If HTMLDoc.URL <> "https://mail.google.com/mail/?shva=1#inbox" Then
        ' We reached the log in page.  Need to log in
        HTMLDoc.all.Email.Value = "username"
        HTMLDoc.all.Passwd.Value = "password"
        HTMLDoc.all.signIn.Click
        
        ' Check if we received the error page
        If HTMLDoc.URL = "https://accounts.google.com/ServiceLoginAuth" Then
            MsgBox "Login failed"
            ' We cannot continue
            Exit Sub
        Else
            MsgBox "Login succeeded"
        End If
    End If
    
    ' Now Proceed with rest of your report code to run your data extraction.
    
       
Err_Clear:
    If Err <> 0 Then
    Err.Clear
    Resume Next
    End If
End Sub

Open in new window


-Bear
0
 

Author Comment

by:futr_vision
Comment Utility
It might be easier to check to see if I get to the login page since the other page could have several variations unless we could check for the base URL somehow.

e.x.
ABC.com/reports1?a=123
ABC.com/reports1?a=345
ABC.com/reports1?a=789

Come to think of it the login URL might have variations too.

e.x.
ABC.com/login
ABC.com/login?s=failure
0
 

Author Comment

by:futr_vision
Comment Utility
By the way, I don't think the "Login succeeded" box is working correctly. It seems to pop up no matter what.
0
 

Author Comment

by:futr_vision
Comment Utility
Looks like i can get away with adding

 oBrowser.navigate sURL

right after:

HTMLDoc.all.signIn.Click

and go to the page I want too.
0
 
LVL 20

Assisted Solution

by:ltlbearand3
ltlbearand3 earned 500 total points
Comment Utility
Concerning determining when to run the log-in code, you will have to use your best judgement on what makes the code flow best.  If you can determine that part of the url will always be the same for a certain condition you could use that.  For example:

If Left(HTMLDoc.URL, 18) <> "ABC.com/reports1?a" Then

Open in new window

or
    If Left(HTMLDoc.URL, 13) = "ABC.com/login" Then

Open in new window


Dealing with the Login suceeded box, we probably need to check the ready state again.  Something like this:

    If Left(HTMLDoc.URL, 13) = "ABC.com/login" Then
        ' We reached the log in page.  Need to log in
        HTMLDoc.all.Email.Value = "username"
        HTMLDoc.all.Passwd.Value = "password"
        HTMLDoc.all.signIn.Click
        
        Do
        ' Wait till the Browser is loaded
        Loop Until oBrowser.readyState = READYSTATE_COMPLETE
         
        ' Check if we received the error page
        If HTMLDoc.URL = "https://accounts.google.com/ServiceLoginAuth" Then
            MsgBox "Login failed"
            ' We cannot continue
            Exit Sub
        Else
            MsgBox "Login succeeded"
        End If
    End If

Open in new window


And yes, you can try to navigate to the correct page with the succeeded message like this:
    If Left(HTMLDoc.URL, 13) = "ABC.com/login" Then
        ' We reached the log in page.  Need to log in
        HTMLDoc.all.Email.Value = "username"
        HTMLDoc.all.Passwd.Value = "password"
        HTMLDoc.all.signIn.Click
        
        Do
        ' Wait till the Browser is loaded
        Loop Until oBrowser.readyState = READYSTATE_COMPLETE
         
        ' Check if we received the error page
        If HTMLDoc.URL = "https://accounts.google.com/ServiceLoginAuth" Then
            MsgBox "Login failed"
            ' We cannot continue
            Exit Sub
        Else
            MsgBox "Login succeeded"
            oBrowser.navigate sURL
        End If
    End If

Open in new window


-Bear
0
 

Author Closing Comment

by:futr_vision
Comment Utility
Very thorough and infinitely patient. Thanks!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

With voice direction tools, various map viewing styles, the ability to get directions for different means of transportation and great service, Google Maps is simply one of the best navigations app in the market.    However, over the past year, an …
Learn new improvements released by Google for Google Calendar. Noted in this article are simple tips and tricks that can make your everyday use of Google Calendar better.
This Micro Tutorial will demonstrate importing calendar invites from events such as webinars into your Google Calendar.
This Micro Tutorial will demonstrate Google Calendar to monitor updates with top sites, such as Facebook, Google, Twitter, etc. with Marketing News. Each update of Google Calendar can be monitored, correlate dips and spikes in your website traffic, …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now