Using InternetExplorer.Application in VBA

Question

I am trying to build a simple macro, which enable user to select required rows from an excel sheet and export it to a web service. The macro should be able to authenticate the user with username and password to make sure that he has permission to upload the data. When the user selects the required row, IE opens authentication page of web service. I am currently trying to capture the url change when the user log in successfully to the web service to verify the credentials.

Is there a better way to authenticate the user? I settled in for authenticating using websites main log in page , as I don't want to send the username and password through the script.

Here is how my code look like:

Call NavigateToURL(--url of login page--) 

Public Sub NavigateToURL(ByVal argURL As String)

Dim objIE As Object

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.Visible = True
.Silent = True
.Navigate argURL

Do Until objIE.LocationURL() = (--url after successfull log in--)
 DoEvents

Loop

If n = (---url after successful log in--) Then
    objIE.Navigate (--redirect to another url)

Else
    MsgBox ("Sorry. Authentication Failed.")
End If

But the part after the Do-Until Loop is not working properly. Can any body point out where I have gone wrong?

score 0 · Answer 1 · answered Jan 27 '15 at 19:25

I think that an easier way of doing what you're trying to do is to get the Username and the Password through an InputBox (or a custom User Form, if you want to make it fancier). This is the idea:

Public Sub Main()
'get username and password through input boxes
username = InputBox("Type your username")
password = InputBox("Type your password")
NavigateToUrl(argURL, username, password)
End Sub   

Public Sub NavigateToURL(ByVal argURL As String, ByVal username As String, ByVal password As String)
    Dim objIE As Object
    Set objIE = CreateObject("InternetExplorer.Application")
    With objIE
        .Visible = True
        .Silent = True
        .Navigate argURL

    Do While .Busy
        DoEvents 'wait for the page to load
    Loop

    'you will have to revise the below lines according to the HTML of the document
    .document.getElementById("username").Value = username 
    .document.getElementById("password").Value = password
    .document.getElementById("login-button").Click
    End With
End Sub

Like this (it is the idea clearly, you need to work on the HTML objects) you will be able not to hardcode username and password but avoiding checking the Internet Explorer external thread.

Using InternetExplorer.Application in VBA

1 Answers1