fill a web form from excel vba

L

luigimut

I have a quite complex issue that I am trying to solve (the Italian excel
newsgroup hasn’t give me any answer at the moment).
The goal is to create an excel macro written in VBA that should effect the
following: a) open a IExplorer window containing a form (username and
password) b) fill the form automatically with the given username and password
and submit the form.
The form is part of an SQL database running on the company server.

By looking for solutions on internet I found several solutions that work,
for instance on the Google search window.
One of them adapted and working is the following:

QUOTE


Sub FILLWEBFORMGOOGLE() ' IT WORKS ON GOOGLE!!!!!!!!!!!!!!

'This project includes references to "Microsoft Internet Controls" and
'"Microsoft HTML Object Library"

'Variable declarations
Dim myIE As New InternetExplorer 'New '
Dim myURL As String
Dim myDoc As HTMLDocument
Dim strSearch As String

'Set starting URL and search string
myURL = "http://google.com"
strSearch = "pippo"

'Make IE navigate to the URL and make browser visible
myIE.navigate myURL
myIE.Visible = True

'Wait for the page to load
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop

'Set IE document into object

Set myDoc = myIE.document

'Enter search string on form

myDoc.forms(0).q.Value = strSearch

'Submit form
myDoc.f.submit

'Wait for the page to load
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop

MsgBox myIE.LocationName

End Sub

UNQUOTE

I tried then to apply the same scheme to my case but the macro doesn’t work.
The code used was the following

QUOTE

Sub FILLWEBFORM()

'This project includes references to "Microsoft Internet Controls" and
'"Microsoft HTML Object Library"

'Variable declarations
Dim myIE As New InternetExplorer 'New '
Dim myURL As String
Dim myDoc As HTMLDocument
Dim strSearch As String

'Set starting URL and search string
myURL = "http://ro-srv9/nostrogestionale/"
strSearch = "pippo"

'Make IE navigate to the URL and make browser visible
myIE.navigate myURL
myIE.Visible = True

'Wait for the page to load
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop

'Set IE document into object

Set myDoc = myIE.document

'Enter search string on form

myDoc.forms(0).UserName.Value = strSearch

'Submit form
myDoc.contest.submit

'Wait for the page to load
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop

MsgBox myIE.LocationName

End Sub

UNQUOTE

The problem is probably due to the syntax to identify the form and the
element UserName in the HTML. The last one should be filled with the text
“pippoâ€. Probably the code


myDoc.forms(0).UserName.Value = strSearch

is wrong (it’s there where the macro is stuck)


I don’t know how to write this code in order to obtain the result. Can
anybody help me on this issue? Can You also suggest articles and literature
on this topic?

Thanks for Your cooperation (I add also the HTML code of the IExplorer
window containing the form that I have to fill, in case is useful)


<HTML>
<HEAD>
<TITLE>Login</TITLE>
<LINK REL="stylesheet" HREF="Library/Ccs/style.css">
<SCRIPT LANGUAGE="JavaScript" SRC="Help/HelpLib.js"></script>
<STYLE TYPE="text/css">
.titolo, .messaggio
{
font-family: Trebuchet Ms;
font-size: 10pt;
font-weight: bold;
text-align: right;
}
.messaggio
{
text-align: center;
color: #CC0000;
}
</STYLE>
<SCRIPT LANGUAGE="JavaScript">
function START()
{
var f = document.contest;
parent.alto.location.href = "Testata.asp";
}
</SCRIPT>
</HEAD>

<BODY onLoad="START()">
<DIV VALIGN="center" ALIGN="RIGHT">

<TABLE width=100% border=0 cellpadding=0 cellspacing=0>
<TR align=left>
<TD align=left valign=bottom width=100%>
<FONT class=SID_PageTitle>Sistema Gestionale societa’</FONT><BR>
<FONT class=SID_SubTitle>Procedura di Logon</FONT>
<HR>
</TD>
</TR>
<TR>
<TD>
<FORM NAME="contest" ACTION="Login.asp" METHOD="post">


<TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="80%">
<TR>
<TD CLASS="titolo">UserName </TD>
<TD> <INPUT NAME="UserName" MAXLENGTH="50"></TD>
</TR>
<TR>
<TD CLASS="titolo">Password </TD>
<TD> <INPUT TYPE="password" NAME="password" MAXLENGTH="20"></TD>
</TR>
</TABLE>

<TABLE CLASS="CMD_Table" WIDTH=100%><TR>
<TD CLASS="CMD_TableCell" align="right">
<INPUT type=submit name=btnLogin CLASS="BTN_Comandi" value="ENTRA">
</TD>
</TR></TABLE>

</FORM>
</TD>
</TR>
</TABLE>

</DIV>
</BODY>
</HTML>
 
K

Kenneth Hobson

Maybe something like this will help.

With IE.GetElementsByName("Password")(0)
.Value = strPassword
End With

Obviously, strPassword is set earlier as is the IE object.
 
L

luigimut

thank You for Your prompt response.

Unfortunately the problem remains and I don't understand why.

To this regard I would like to ask the following:

1) can the problem be solved in principle? I summarize the terms of the
issue: a) I have a file excel where I want to create a macro b) the macro
should open a IE window containing a form (at least I guess it is a form
looking at the HTML attached to my previous post) and fill the UserName and
password elements of the form and finally submit it; c) I have applied the
code attached in the previous post and I always received a runtime errore 91;
d) I have included in VBA the reference to Microsoft Internet Object and
Microsoft HTML objects; e) I have applied the code to a similar problem (fill
a form of the Google search window) and it worked. So I am wondering if the
problem is that I want to fill and submit the form of my company database,
based on the company server, and not of an internet site. The question then
is can I, in principle apply the the same code applied to the google site to
my company database? (I guess Yes but maybe I am missing something important)

2) In the code posted in the previous post what should I change in order to
make it work

Thank You for Your assistance

luigimut
 
J

jaf

Wild guess. (yes, I'm guessing)
What data type is the form field set to receive?

myDoc.forms(0).UserName.Value = strSearch
should be
myDoc.forms(0).UserName.Value = char(34) & strSearch & char(34)
or maybe
myDoc.forms(0).UserName.Value = """ & strSearch & """
or maybe
myDoc.forms(0).UserName.Text= strSearch

John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

submit web form 1
Excel values to Website, HTML extraction back to Excel. PLEASEHelp!!! 0
Please Help, Excel to Website and web data back to Excel 15
Looping through HTML table to populate Excel 6
VBA and Internet Explorer 9
link click not working 5
Difficulty analysing Journey Planner result 6
Pick data from web page (no query) 14

Top