asked on

Using VBA to automate secure website commands

I have access to a password protected payroll website. My goal is to be write a VBA script that will automatically access the website, enter my username and password, navigate to the "Reports" page, select a topic from a list, run a report from another list, download the report via the Internet Explorer download tool to a specified folder and then exit the Webpage.

I have attempted to use the View Source for the Webpage to determine the "id's" for the specific input/selection items but I am getting lost.

Attached are png's for the login screen, the report selection screen, the report format screen and the download screen.

I would appreciate a kick start to begin programming the VBA script using Excel 2010.

Thanks

GPSPOW
Paychex.PNG
PC-CustomReports.PNG
PC-RptFormat.PNG
download.PNG

This will get you started logging in
http://vbadud.blogspot.ie/2009/08/how-to-login-to-website-using-vba.html

ASKER

I am getting a error at the Debug.Assert Err = 0 line.

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_2_Website()

Dim oHTML_Element As IHTMLElement
Dim sURL As String

On Error GoTo Err_Clear
sURL = "https://landing.paychex.com/ssologin/login.aspx?TYPE=33554433&REALMOID=06-e74be48c-b042-1010-b9a3-841d880e0cb3&GUID=&SMAUTHREASON=0&METHOD=GET&SMAGENTNAME=ThSwob9IQTBgYoEstJrFG07DeK1IYxusNT3c3IGgBJ7xUR7bt2eyWI849VLDEHpe&TARGET=-SM-https%3a%2f%2flanding%2epaychex%2ecom%2f"
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 = "gpowers99"
HTMLDoc.all.passwd.Value = "103112"

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next

' oBrowser.Refresh ' Refresh If Needed
Err_Clear:
If Err <> 0 Then
Debug.Assert Err = 0
Err.Clear
Resume Next
End If
End Sub

Open in new window

Have you added the controls

ASKER

I checked the Microsoft HTML Object Library and Microsoft Internet Controls boxes in the Reference Screen.

Is there something else I need to do?

Thanks

Just remove that line

And asap change your password.

ASKER

Password is not real.

I have attached a picture of a new error I am getting for the Set oBrowser line.

Thanks

GPSPOW
Debug-error.PNG

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_2_Website()

Dim oHTML_Element As IHTMLElement
Dim sURL As String

On Error GoTo Err_Clear
sURL = "https://landing.paychex.com/ssologin/login.aspx?TYPE=33554433&REALMOID=06-e74be48c-b042-1010-b9a3-841d880e0cb3&GUID=&SMAUTHREASON=0&METHOD=GET&SMAGENTNAME=ThSwob9IQTBgYoEstJrFG07DeK1IYxusNT3c3IGgBJ7xUR7bt2eyWI849VLDEHpe&TARGET=-SM-https%3a%2f%2flanding%2epaychex%2ecom%2f"
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.UserName.Value = "gpowers99"
'HTMLDoc.all.passwd.Value = "103112"

For Each oHTML_Element In HTMLDoc.getElementsByName("ContinueButton")
    oHTML_Element.Click
    Exit For
Next

Application.Wait (Now + TimeValue("0:00:05"))


HTMLDoc.all.Password.Value = "103112"

For Each oHTML_Element In HTMLDoc.getElementsByName("LoginButton")
    oHTML_Element.Click
    Exit For
Next

Err_Clear:
If Err <> 0 Then
    Err.Clear
    Resume Next
End If

End Sub

Open in new window

ASKER

I pasted the solution into my Excel and the website does not open.

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_2_Website()

Dim oHTML_Element As IHTMLElement
Dim sURL As String

On Error GoTo Err_Clear
sURL = "http://www.paychex.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.UserName.Value = "gpowers99"
'HTMLDoc.all.passwd.Value = "103112"

For Each oHTML_Element In HTMLDoc.getElementsByName("ContinueButton")
    oHTML_Element.Click
    Exit For
Next

Application.Wait (Now + TimeValue("0:00:05"))


HTMLDoc.all.Password.Value = "999999"

For Each oHTML_Element In HTMLDoc.getElementsByName("LoginButton")
    oHTML_Element.Click
    Exit For
Next

Err_Clear:
If Err <> 0 Then
    Err.Clear
    Resume Next
End If

End Sub

Open in new window

I changed the sUrl name to main website which would open the aspx.

Working fine for me, make sure your firewall is not blocking access.
VBA is treated as dangerous code.

Also check the settings here
http://office.microsoft.com/en-ie/excel-help/change-macro-security-settings-in-excel-HP010096919.aspx

ASKER

I do not have a firewall running. I have enabled all macros to run and allow all webpages.

What would be the code to just open the web page before I try to enter the username and password?

Thanks

GPSPOW

Does the browser open at all?

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_2_Website()

Dim oHTML_Element As IHTMLElement
Dim sURL As String

On Error GoTo Err_Clear
sURL = "http://www.paychex.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

Err_Clear:
If Err <> 0 Then
    Err.Clear
    Resume Next
End If

End Sub

Open in new window

ASKER

No. I get the little blue circle that turns and then nothing.

Remove
oBrowser.timeout = 60

and step thru the code to see if you can find where its bugging out.

ASKER

I ran it in step mode and there were no bugs. It just didn't do anything.

Any suggestions?

Thanks

GPSPOW

And it stepped thru all the way to the end?
What version of Excel are you running?

ASKER

I closed my computer down and restarted it.

The website came up when I ran it again.

The username does not fill in.

GPSPOW

Post your code as you have it.

ASKER

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_2_Website()

Dim oHTML_Element As IHTMLElement
Dim sURL As String

On Error GoTo Err_Clear
sURL = "http://www.mypaychex.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.UserName.Value = "gpowers99"
'HTMLDoc.all.passwd.Value = "103112"

For Each oHTML_Element In HTMLDoc.getElementsByName("ContinueButton")
    oHTML_Element.Click
    Exit For
Next

Application.Wait (Now + TimeValue("0:00:05"))


HTMLDoc.all.Password.Value = "999999"

For Each oHTML_Element In HTMLDoc.getElementsByName("LoginButton")
    oHTML_Element.Click
    Exit For
Next

Err_Clear:
If Err <> 0 Then
    Err.Clear
    Resume Next
End If

End Sub

Open in new window

HERe is the code

Working fine for me.
Try changing the username line to
HTMLDoc.getElementById("Username").innerText = "gpowers99"

ASKER

When you run it, the username fills in and goes to the Password screen?

My screen stops at the Username and is waiting for entry.

Yep
Did you step thru it

ASKER

Stepping through it has no effect. I am running Excel 2010, Windows 8 and IE 10.

Does that make a difference?

GPSPOW

Then I can only hazard a guess that it is either W8 or more probably IE10
For me W7 and IE9 it works fine.

Try setting compatibility mode under Tools to IE9, only thing I can think of

ASKER

Can I use Google Chrome instead of IE

Cannot find anyway of doing it with Chrome or FF

ASKER

I changed the version of IE and it still does not work.

I am going to try it on my old windows 7 laptop tomorrow and see if I have any luck.

thanks

GPSPOW

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

ASKER

The website vendor does not have an API. That is the reason for the script. Thanks for all your help