Password not working - VBA

[Shamas]

Hi,

I am using an excel sheet (2003) which runs a lot of code. When I open it up a form collects my username & password before it will continue.

Once we complete this step part of the code loads up a webpage and has to log in with username & password (taken from the form) before copying the data and pasting it back in to my excel sheet.

The webpage (intranet) was moved and some details were changed in the background.

I amended these details in the code but it is no longer able to login when it fires up the webpage.

I tested the sheet by adding my username and password in at code level (replacing teh form fetch bit) and it worked. When I switch it back to getting the username and password from the input form it doesn't work again.

Can you help please?

Thanks

ps. sorry for the messy layout

CODE--------------------

Sub myWebQuery()
'Dates for URL manipulation
Application.DisplayAlerts = False
Dim dFrom As String, dTo As String
dFrom = Sheets("Update").Range("D4").Value
dTo = Sheets("Update").Range("D4").Value + 6
‘Update sheet contains the date from the Monday just gone
Sheets("callsSQL").Select
'Credentials
Dim userNameVar, passW As String
userNameVar = loginForm.uNameBox.Value
passW = loginForm.passBox.Value
‘This is the bit that is causing problems… If I swap the loginForm bits with actual name & password then it works fine

'URL
Dim webPath, webPathFinal As String
webPath = "http://reporting.xxxxxx.com"
‘blanked out
webPathFinal = "http://reporting.xxxxxx.com?startdate=" & dFrom & "&enddate=" & dTo & "&drill=agent"

'IE
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")

With IE
.navigate webPath
.Visible = True

'wait until page loads up
Do Until Not .Busy
DoEvents
Loop

On Error Resume Next
'Object on the page
Dim userBox As Object
Set userBox = IE.document.all.Item("j_username")

userBox.Value = userNameVar

''pass
Dim passBox As Object
Set passBox = IE.document.all.Item("j_password")

passBox.Value = passW

'Button to login
Dim submitButton As Object
Set submitButton = IE.document.all.Item("submit")

submitButton.Click

'wait until page loads up
Do Until Not .Busy
DoEvents
Loop

'Refresh WebQuery clear First
Sheets("callsSQL").Select
Cells.Select
Selection.Value = ""

.navigate webPathFinal

'wait until page loads up
Do Until Not .Busy
DoEvents
Loop

Do Until Not .Busy
DoEvents
Loop

.ExecWB 17, 0 'Select everything on the page
.ExecWB 12, 2 'Copy everything on the page
Sheets("callsSQL").Select

ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False

'unmerge cells
Cells.Select
With Selection
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
'.Value = .Value
End With
Cells.EntireColumn.AutoFit

'To prevent exeption error with existing object log out the user

'wait until page loads up
Do Until Not .Busy
DoEvents
Loop

IE.Quit
End With

Set IE = Nothing
'clear objects
Call clearObjects

End Sub

 

[AlphaFrog]

When you say "it doesn't work again", does the web page give a cause e.g. invalid password or something like that?

Maybe wrap the UserName and password in a Trim function to strip any leading or trailing space that may have crept in.

userNameVar = Trim(loginForm.uNameBox.Value)
passW = Trim(loginForm.passBox.Value)

As a test, maybe you should...

[COLOR="RoyalBlue"]Debug Print[/COLOR] userNameVar & " " & passW

...to confirm both variables are being set with the values

 

[Shamas]

Thanks!

I set the webpage to visible so I could see what happens - when I revert back to form data it doesn't seem to enter the username and password at all.

I tried Trim() - no success

Not sure how and where I can do this bit:

Debug Print userNameVar & " " & passW

 

[AlphaFrog]

Correction:
It should have been Debug.Print

Instead of that, put a msgbox after you set userNameVar and passW just to confirm they are being set properly

userNameVar = Trim(loginForm.uNameBox.Value)
passW = Trim(loginForm.passBox.Value)
[COLOR="Red"]MsgBox "UserName: " & userNameVar & vbcr & "Password: " & passW[/COLOR]

 

[Shamas]

msgbox trick is genius.

Results came back blank... I can't work out why it doesn't pull the username & password through...

 

[AlphaFrog]

We'll your making some progress. So that's good.

Do you have more than one userform?
Are theses the exact names of the TextBoxes; uNameBox, passBox?
Is this the exact name of the UserForm with the two TextBoxes: loginForm?

 

[Shamas]

yes exact names copied from the form itself

 

[AlphaFrog]

Is the loginForm up and running while you call this Sub myWebQuery? And if yes, (I have to ask the obvious because I can't see what's happening) is there anything in the two textboxes?

How are you calling this Sub myWebQuery? Where is that code?

 

[Shamas]

I saved the Trim stuff. Closed excel down and logged on to another PC

This time I tried the sheet and it worked perectly!

I asked someone to use my sheet on their PC with their own username and password and it worked again!

Thanks for your help