How can I automate filling in a Web form with Excel data?

[VBwannaB]

Hi,
I have an Excel spreadsheet with reimbursement/expense data that I need
to submit to my main office. The only way to do this is by filling in
a web form for each reimbursement/expense entry. Can you help me with
VBA or VB code that can extract data from Excel and paste it into an IE
web form?

I just started dabbling with VB 2005, and I am quite proficient with
VBA so any help could go a long way!

Thanks

 

[Robin Hammond]

Depending on how the page is coded you may well be able to post your data to
the NEXT page that the web form submits to.

e.g. in the page with the web form in it you might have some html like this.

<form action="../PostPage.asp" method="post" name="InputForm"
class="PageTable">
<table border="0" >
<tr> <td> <div class="PageText">Your Name: </div></td>
<td> <div > <span class="PageText">
<input type="text" name="UserName" size="47">
'rest of html omitted here

The important elements in this are the page name: PostPage.asp, and the
variable name: UserName

You should then be able to use the xmlhttp object to send post data.

'first set a project reference to msxml4
Sub TestPost()
Dim objHttp As MSXML2.XMLHTTP40
Set objHttp = New MSXML2.XMLHTTP40
objHttp.Open "POST", "http://www.yourdomain.com/PostPage.asp", False
objHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objHttp.send "UserName=Robin"
'if you have multiple entries it would look like this
'objHttp.send "UserName=Robin&LastName=Hammond&[email protected]"
'check the objhttp.status for 200
'you can also look at the responsetext to see what is going on
Set objHttp = Nothing
End Sub

That said, I just tried this and got an access denied on a particular site,
so it might take some testing to get it to work.

HTH,

Robin Hammond
www.enhanceddatasystems.com