"Auto Complete" Internet Form

[debauch]

Ok, can someone help point in the right direction with an example maybe ? What I am trying to do is, have information from an excel sheet (I will probably have a userform with a button to initiate this) and post the correct information in text boxes on a website. I have a site I visit, to create accounts for users. I.e - I input, first name (textbox) last name (next textbox) etc. I want to have all this automated.

I think the class 'internetexplorer' is what I want to use, but not sure how to use its functions. I need to be able to: tab (jump to next textbox), input text, and when all info is input, I would like it to hit the 'save' button.

I hope I was somewhat clear. I am sure I can clarify if someone asks questions a little more specific?

I did search b4 posting, but did not find quite what I was looking for ...

[brettdj]

see my kb article on logging into a website http://vbaexpress.com/kb/getarticle.php?kb_id=399

The trick is to identify the Form and its item that you want to populate

[debauch]

Thanks Brettdj,
It lays out groundwork, and I am uble to understand most of the code. By the looks of it, it will log my into my appication, but what is more important, is once logged in, have information copied from excel, and pasted into different textboxes. How do I identify what each textbox is called ?

What I am doing, is creating logins for users, but I enter 4 to 5 peices of info, for about 40 users a day. It is killer to tpye this junk ouut.

[brettdj]

Trial and error
Sometimes the nameing gives a good indication

Can you share your web link with us?

[stanl]

just an FYI - you might be able to do everything with the WinHttp.WinHttpRequest Object. You first determine if your form is a GET or [more likely] POST and then set your request against the action=
In terms of username/pw issues use setcredentials - something like

Dim oHTTP As New WinHttp.WinHttpRequest
oHTTP.Open "GET", http://MyWebSite.com/login_page.asp, False
oHTTP.SetCredentials "user", "password", 0
oHTTP.Send

Stan

[mvidas]

How do I identify what each textbox is called ?

First thing I do is go to View / Source. In there, I do a search for 'form', and look for the name of the form I'll be putting values into. From there I look for 'input', to find the input names to put the value to.

For example, look at the google advanced search page:
http://www.google.com/advanced_search
Though it isn't the greatest example, due to the non-formatted source of the page, it can still help show you. Go to view / source, and search for 'form'. The first one you see is in the line:
<form method=GET action="/search" name=f>

Now you know the form's name is 'f'. Now do a search for 'input'. The first one you see is in the line:
<input type=text value="" name=as_q size=25>

Though the name 'as_q' doesn't exactly explain what it does, if you look before that you'll see it goes with:
<font size=-1>with <b>all</b> of the words</font></td><td><input type=text value="" name=as_q size=25>
so that tells you the "with all the words" box has a textbox with name 'as_q'.

Do another search for 'input' and you'll see (at least in mine):
<input type=hidden name=hl value="en">

It is a type=hidden, and not type=text. It doesn't explain what that is; but it isn't the number-of-results dropdown (that has '<select name=num>' before it). The 'hl' is actually language, with 'en' being english, so it might not be that for you.

Looking for the next 'input type=text', you'll see:
<font size=-1>with the <b>exact phrase</b></font></td><td><input type=text size=25 value="" name=as_epq>

Just follow that general idea to find your form/input names.
Matt

[malik641]

EDIT: Sorry, wanted to quote about brettdj's article. So....

see my kb article on logging into a website http://vbaexpress.com/kb/getarticle.php?kb_id=399

The trick is to identify the Form and its item that you want to populate

Yeah that all sounds good...but what if you wanted to login to any site? How can you make this dynamic??


I made a userform that asks for the web address, username and password. Now what I'm trying to do is have it load the page and look for the following text:
For Username:
"ID"
"Username"
"Email"

For Password:
"Password"....der

And I'm trying to use brettdj's way of finding this text...then using his "ieForm(#)" method to enter the values in...now this works if I KNOW which number to enter into the ieForm(), but some websites are different I've noticed...so how can I search and find the correct form #???? I've tried a couple different methods but it seems that I can't make this dynamic...


BTW, I'm giving this thread a 5 star rating Awesome new info for me

[mvidas]

Making it dynamic could be fun, though it would never be 100% accurate due to so many variances

I think it is funny how vbax uses 'name="vb_login_username"' in their input field. So a search on 'username' would find that, but would first find
"/* ***** styling for 'big' usernames on postbit etc. ***** */"
".bigusername { font-size: 13pt; }"
before it found
<td><input type="text" class="button" name="vb_login_username" id="navbar_username" size="10" accesskey="u" tabindex="1" value="User Name" onfocus="if (this.value == 'User Name') this.value = '';" /></td>

somtimes you might even see the above look like ... name=vb_login_username ... as the quotes aren't even necessary

Doing it dynamically sounds like it could be fun.. even creating something that lists all the forms/inputs would be a help

When I get a spare few minutes, I know what I'm gonna try!
Matt

[malik641]

Making it dynamic could be fun, though it would never be 100% accurate due to so many variances

I think it is funny how vbax uses 'name="vb_login_username"' in their input field. So a search on 'username' would find that, but would first find
"/* ***** styling for 'big' usernames on postbit etc. ***** */"
".bigusername { font-size: 13pt; }"
before it found
<td><input type="text" class="button" name="vb_login_username" id="navbar_username" size="10" accesskey="u" tabindex="1" value="User Name" onfocus="if (this.value == 'User Name') this.value = '';" /></td>

somtimes you might even see the above look like ... name=vb_login_username ... as the quotes aren't even necessary

Doing it dynamically sounds like it could be fun.. even creating something that lists all the forms/inputs would be a help

When I get a spare few minutes, I know what I'm gonna try!
Matt

Hold on...I'm a little confused...
Here is the code I run to log into Gmail...
[VBA]
Sub GMAIL(MyLogin As String, MyPass As String)
Dim ie As New InternetExplorer
Dim ULogin As Boolean
Dim ieForm As Variant
Dim WebPage As String

WebPage = "https://www.google.com/accounts/Login"
ie.Visible = True
ie.Navigate WebPage
'Loop until IE pages is fully loaded
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
'Look for password Form by finding test "Password"
For Each ieForm In ie.Document.forms
If InStr(ieForm.innertext, "Password") <> 0 Then
ULogin = True
'enter details
ieForm(0).Value = MyLogin
ieForm(1).Value = MyPass
'login
ieForm.submit
'Exit For
Else
End If
Next
If ULogin = False Then MsgBox "User is aleady logged in"
Set ie = Nothing
End Sub
[/VBA]
Lemme get this straight....what this code does is look through the source for the word "Password" in each Form in the code and performs the login if found?

So what do the numbers in ieForm mean?

Edit:I couldn't get stan's code to work, BTW...???

[brettdj]

I may have to edit the KB article to clear up what I am doing with the "Password" bit

If you break the code at [vba]For Each ieForm In ie.Document.forms [/vba] and then look at ie.Document.Forms in the locals window, you will see that there are four forms (Form 1 is Forms(0), Form 2 is Forms(1) etc). The trick is to identify which form contains the login and password items, and then find the correct items

Now the first form gives a very good hint - it has "Password" in the form text - ie the descriptor next to the login. The first item in this form is the login, the third item is the password. Again item 1 is Item(0) etc

So I could have coded it directly as [vba] ie.Document.forms(0).Item(0).Value = MyLogin
ie.Document.forms(0).Item(2).Value = MyPass[/vba]

I step through this code line by line to test that the right textbox is being used

Cheers

Dave

[malik641]

I may have to edit the KB article to clear up what I am doing with the "Password" bit

If you break the code at [vba]For Each ieForm In ie.Document.forms [/vba] and then look at ie.Document.Forms in the locals window, you will see that there are four forms (Form 1 is Forms(0), Form 2 is Forms(1) etc). The trick is to identify which form contains the login and password items, and then find the correct items

Now the first form gives a very good hint - it has "Password" in the form text - ie the descriptor next to the login. The first item in this form is the login, the third item is the password. Again item 1 is Item(0) etc

So I could have coded it directly as [vba] ie.Document.forms(0).Item(0).Value = MyLogin
ie.Document.forms(0).Item(2).Value = MyPass[/vba]

I step through this code line by line to test that the right textbox is being used

Cheers

Dave

I see....
How to find the correct form...

[debauch]

Unfortunately, it is a secure website. I am unable to test this until I got back to work on Monday, but I have been testing the code on general websites to learn it. I will keep you posted, thanks for all the input everyone.

[debauch]

Ok, so I have taken bits from everyone who has helped out here and was able to test it using http://www.google.com/advanced_search

and by editing below, I figured out how to use it, only [vba]MyLogin = sheets(example)range("a22")[/vba] is the format I am using ... to take info from excel, to automate my forms.
[VBA]
ie.Document.forms(0).Item(0).Value = MyLogin
ie.Document.forms(0).Item(2).Value = MyPass
[/vba]
I have managed to test this on several sites by following Mvidas' tips. However, the source code for the site I need to use it with does not seem like the average source code, here it is (some is edited for privacy purposes) :

[vba]
<html>
<head><base href="http://www.xxxxxxxxxxxxxxxxxxxx.com">
<title>CxxxxxxxUxxxxxxxx</title>


</head>

<frameset rows="80,*" frameborder=no border=0>

<!-- Banner frame -->

<frame name="banner" src="/docent/lms/banner.html" marginwidth=0 marginheight=0 scrolling=no>

<frameset cols="215,*" frameborder=no border=0>

<!-- Menu frame -->
<frame name="menubar" src="?CMD=GET&file=menubar.jsm&http_switch=false">


<!-- Main frame -->
<frameset rows="*,35" frameborder=no border=0>
<frame name="docent_main" src="?CMD=GET&file=catalog/plan_summary.jsm">
<frame name="bottomlogo" src="/docent/lms/bottomlogo.html" marginheight=5 scrolling=no>
</frameset>

</frameset>
</frameset>
</html>
[/vba]
As I naigate through pages, the source code doesn't seem to change. Is some of the cod e hidden somehow maybe ??

[debauch]

I think I may be able to mange to figure it out if I learn how to 'tab' in the code. Is this possible ? I need to be able to automatically tab during and after the forms have been filled in, and to get to the forms

[mvidas]

The source you have there is just of the main frame for the page, not the source for the whole page

Looking at the line:
<frame name="docent_main" src="?CMD=GET&file=catalog/plan_summary.jsm">

What happens if you try getting that?
If you're looking at www.domain.com/index.php, try getting www.domain.com/index.php?CMD=GET&file=catalog/plan_summary.jsm or maybe just www.domain.com/catalog/plan_summary.jsm

If you're using the IE object you could always change frames as well, I believe it is a property of the document property, like:[vba]IE.Document.Frames(0).Body.Forms(0)...[/vba]Matt

[malik641]

I'm very interested in Stan's approach...but I can't get it to work. Which Reference do I need to install? I'm about to check EVERY single one

Besides that I found this from MSDN, pretty informative...and it may be the easiest approach...but I'm not sure as this is all very new to me. So let me know if I'm wrong please.

Anyway here's what I'm TRYING to use from MSDN's website:
[VBA] Option Explicit
'HttpRequest SetCredentials flags.
Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0
Const HTTPREQUEST_SETCREDENTIALS_FOR_PROXY = 1
Sub SetCredentials( _
ByVal bstrUserName As BSTR, _
ByVal bstrPassword As BSTR, _
ByVal Flags As HTTPREQUEST_SETCREDENTIALS_FLAGS _
)
Sub Internet()
Dim HttpReq As Object
' Create the WinHTTPRequest ActiveX Object.
Set HttpReq = New WinHttpRequest

' Switch the mouse pointer to an hourglass while busy.
MousePointer = vbHourglass

' Open an HTTP connection.
HttpReq.Open "GET", "http://microsoft.com", False

' Set credentials for server.
HttpReq.SetCredentials "User Name", "Password", _
HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
' Send the HTTP Request.
HttpReq.Send

' Get all response text.
Text1.Text = HttpReq.ResponseText
' Switch the mouse pointer back to default.
MousePointer = vbDefault

End Sub

[/VBA]
But I have no luck so far....Can't figure out which Reference to check

Any ideas?

EDIT: BTW, I'm using Excel 2000. And I'm using Windows 2000 Professional.

[mvidas]

Though I haven't played with http requests at all, after looking around it looks like you want to add a reference to 'Microsoft WinHTTP Services'
Matt

[malik641]

Thanks Matt...Still unsure how to use the WinHttpRequest....I got this code to work that I found on the web:

[VBA] Option Explicit
'HttpRequest SetCredentials flags.
Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0
Private Sub ListSubs()
Dim MyRequest As New WinHttpRequest
MyRequest.Open "GET", _
"https://www.google.com/accounts/ServiceLoginAuth"
'Set credentials
MyRequest.SetCredentials "yourname", "yourpassword", _
HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
' Send Request.
MyRequest.Send

'And we get this response
MsgBox MyRequest.ResponseText

End Sub
[/VBA]
But this just returns the XML data in a message box....not what I was going for....Opening the site and logging in.

I tried this because it seemed to be much cleaner/simple....oh well.
I'm going to leave this alone for a little, until I read-up on this stuff.

[mvidas]

Joseph,

Looking mot at winhttp it looks an awful lot like xmlhttp, which I am familiar with. The .responsetext property does only return the source code for the page, not the text itself. Usually to get only the text of the web page requires an IE object, and getting the .InnerText of that. I know its a little strange using winhttp then using IE, but to give you an example:[vba]Option Explicit
'HttpRequest SetCredentials flags.
Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0
Private Sub ListSubs()
Dim MyRequest As New WinHttp.WinHttpRequest
MyRequest.Open "GET", _
"https://www.google.com/accounts/ServiceLoginAuth"
'Set credentials
MyRequest.SetCredentials "yourname", "yourpassword", _
HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
' Send Request.
MyRequest.Send

'And we get this response
Dim IE As Object
Set IE = CreateObject("internetexplorer.application")
IE.navigate2 "about:blank"
Do While IE.readystate <> 4
DoEvents
Loop

IE.Document.Body.InnerHTML = MyRequest.ResponseText
' IE.Visible = True
MsgBox IE.Document.Body.InnerText
End Sub[/vba]Matt

[malik641]

First off, thanks Matt for taking my login and password out of my thread
I can't believe I forgot

Anyway The message box told me that I couldn't login....how come?