Macro to populate web form

C

confused

Is it possible to create macro that will take data from an Excel sheet and
use it to populate a form on a webpage in IE?
I currently manually key information from a table in Excel into a form on a
webpage in IE. Each row on the table represents a unique submission to the
webpage. Can anyone help me write a macro that will write the data from the
first row to the form, "click" submit, write the data from the second row,
"click" submit, etc.? Some of the entry is clicking on the appropriate radio
button.

If this isn't readily accomplished does anyone have any suggestions on how
to streamline/automate this process? About 90% of the time only 1 field on
the web form is different while ~15 fields are unchanged. Unfortunately the
page doesn't "remember" the last submission and won't recall the previous
entry so I end up keying all ~16 fields for every submission even when only 1
field changes.

I have limited VBA experience but if one of you helpful and talented experts
can provide at least the shell of some code i have always been able to make
it work in the past.

Thanks very much in advance.
 
R

Rick Rothstein

Do you have a web address you can post so we can try and see if what you
want is doable? If so, can you also tells what cells on your worksheet go to
what fields you are referring to?
 
B

Bernie Deitrick

You need to identify the objects on the web page that have the values entered. Below is some code
that takes three cell values and submits them to a form on the USPS web site to get the Zip + Four
value for the address submitted. Getting the result also depends on the document that is returned -
you may need to post the URL and an example of your submitted data to get more specific help. The
code requires, IIRC, a reference to the MS Forms 2.0 Object Library.

HTH,
Bernie
MS Excel MVP

Function ZipPlusFour(sAdd1 As String, _
sCity As String, _
sState As String _
) As String

Dim ie As Object
Dim sResult As String
Dim sCityState As String
Dim lStartCity As Long
Dim dtTimer As Date
Dim lAddTime As Long

Const sUPSURL As String = "http://zip4.usps.com/zip4/welcome.jsp"
Const lREADYSTATE_COMPLETE As Long = 4

Set ie = CreateObject("InternetExplorer.Application")
ie.silent = True
ie.navigate "http://zip4.usps.com/zip4/welcome.jsp"

dtTimer = Now
lAddTime = TimeValue("00:00:20")

Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
DoEvents
If dtTimer + lAddTime > Now Then Exit Do
Loop

ie.document.form1.address1.Value = sAdd1
ie.document.form1.City.Value = sCity
ie.document.form1.State.Value = sState
ie.document.form1.submit

dtTimer = Now
lAddTime = TimeValue("00:00:20")

Do Until ie.readystate = lREADYSTATE_COMPLETE And Not ie.busy
DoEvents
If dtTimer + lAddTime > Now Then Exit Do
Loop

sResult = ie.document.body.innertext
sCityState = sCity & " " & sState

lStartCity = InStr(1, sResult, sCityState, vbTextCompare)
lStartCity = InStr(lStartCity + 1, sResult, sCityState, vbTextCompare)

If lStartCity > 0 Then
ZipPlusFour = Mid(sResult, lStartCity + Len(sCityState) + 2, 10)
Else
ZipPlusFour = "Not Found"
End If

ie.Quit
Set ie = Nothing

End Function
 
J

Joel

This may help. I request was made to run the google API function to perform
language translation. We generated a simple HTML file that had two boxes and
a submit button. Using VBA I moved the foreign language sentance into one
box, then press the submit button, and finally removed the translated date
from the 2nd box. Take the html text and put it in a file called
translate.html. The put the VBA code into an excel file. Change the name of
the URL (foolder on PC) to match the html file so you can understand how it
works.

You would need a table in the HTML code.

HTML file - save in a text file with html extension using notepad or
equivalent

------------------------------------------------------------------------------------------

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Google Translate API</title>
</head>

<body>


<form name="f" id="f" action="#" onsubmit="translate(); return false;">

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"></textarea>
<br />
<br />

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" />
<br />
<br />

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"></textarea></form>
<br />

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script>

</body>
</html>

--------------------------------------------------------------------------------------

VBA code : change URL = "c:\temp\working\translation.html" as required

---------------------------------------------------------------------------------------
Sub translate()

Dim objIE As Object
Dim strServAcct As String

URL = "c:\temp\working\translation.html"
'Open Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL

Do While IE.Busy = True Or IE.readystate <> 4
DoEvents
Loop

Set ForeignCells = Range("A1:B1")
For Each cell In ForeignCells
Set ForeignText = IE.document.getElementById("foreign_text")
Set submit = IE.document.getElementById("submit_button")

ForeignText.innertext = cell.Value
submit.Select
submit.Click
Do While IE.Busy = True Or IE.readystate <> 4
DoEvents
Loop
Set Translation = IE.document.getElementById("Translation")
Translation.innertext = cell.Offset(1, 0).Value
Next cell
End Sub
 
C

confused

Unfortunately it is a secure web page so I can't post a working URL.
Even more unfortunately I can't veiw the web page source so I can't get the
names assigned to the various fields.

Is there a workaround (i.e. tab X times, send keys, tab X times, send keys,
etc.) or should I just give up and get a macro recorder for IE?

Bernie,
If I understand your code correctly (a big if), you:
define some variables and constants
open IE, wait 20 seconds (repeat as needed)
set variables defined on the webpage = string values from excel
submit the form in IE, wait 20 seconds (repeat as needed)
set sResults = results from web page
test sResults for city and state
set ZipPlusFour = the 10 characters that occur 2 characters after city and
state in sResults
close IE

If there is a workaround for not having the field names from the web page
source then this gets me most of the way there. Thanks much!

I still need help, however, with the code for multiple submissions.
Let's assume that in rows 1 thru 10 column A contans "Buy" or "Sell", column
B contains quantities, and column C contains prices. Once I have successfuly
set webform.field1 = A1, webform.field2 = B1, webform.field3 = C1 and
submited the form, how do I:
test the new web page to see that the submission was successful (without
knowing page defined field names)?
Submit the values for rows 2 thru 10?
Tell the macro to quit when it reaches row 11 (which is blank)?

Thanks again to everyone who answered.
Thanks in advance for any suggestions on how to move forward.
 
J

Joel

Use this code to get source information. Replace the URl

Sub DumpPage()


Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "My URL"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4 And _
IE.busy = True

DoEvents
Loop

RowCount = 1
For Each itm In IE.document.all
Range("A" & RowCount) = itm.tagname
Range("B" & RowCount) = itm.classname
Range("C" & RowCount) = itm.ID 'remove if error - not all pages have id's
Range("D" & RowCount) = Left(itm.innertext, 1024)

RowCount = RowCount + 1
Next itm


End Sub
 
C

confused

Thanks Joel!
I now have a file with the tagname, classname, ID, and innertext of each
item on the page. I must admit, however, that I don't know what much of that
stuff is or means. Can I run DumpPage on a web page I already have open so
that I can put something meaningful in the input fields and use that to
figure out which items are which? If so, how do I change the code to go to an
open page instead of opening a new instance of IE?
 
J

Joel

You have to put the URL of the webpage in the macro. If you have a form you
can fill the form and then same the webpage on your local drive as a file.
Then run the dump on the saved file. Your URL can look like this

URL = "c:\temp\working\translation.html"


1) The tags on a webpage are the names inside the angle brackets

<tag1 some text code /tag1>

or

<tag1 some text code />

the tags have opening and closing angle brackets. The closing may or may
not have the tag name




2) Class name are strings in the html source that look like this

class=:abc"

3) ID's are strings in the html source that look like this

id=:abc"

4) Innertext is all the text strings on the webpage. The data you are going
to put in your form will either be put in using VALUE or INNERTEXT property.
 
C

confused

I populated the fields with recognizable data, saved a local copy, changed
the DumpPage URL, and ran DumpPage.
It DID open the local copy but the recognizable data had been lost. I tried
populating the local file and running DumpPage with the local page still open
but got the same result so I still don't know which fields are which.

Thanks to you I do have a list of 519 items on the page including 52 with
unique IDs. The problem is that the IDs are stuff like "List0List",
"List0ListItem0", "List0ListItemDiv0", "List0ListItemC0", etc., and that
there are 12 radio buttons (at least, there are 12 items with a classname
"radio") that all have the same tagname and no ID or innertext.

If there isn't a way to basically send keystrokes to the web page I'm going
to have to reach out to the page developer and see if they can provide some
guidance on the page defined field names.

Thanks again.
 
J

Joel

I found the code to get an IE window that is already opened.

http://www.microsoft.com/office/com...&p=1&tid=c895d088-c2d2-47b8-aeac-e4a9bd9f6394

In the dump you should try looking for these items

1) Table - your data may be in rows and columns in a table

2) Form - the input may be in a form

3) Box - sometimes the input objects have the word INPUT. sometimes you can
identify the objects by the Caption that is on the box.


You can alway try to write to the different object and see which box is
asscociated with each box.

to get object use something like this

Set Form = IE.document.getElementsByTagname("Form")
Set zip5 = IE.document.getElementById("zip5")


try this in the code

Sub DumpPage()


Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "My URL"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4 And _
IE.busy = True

DoEvents
Loop


Set Radio = IE.document.getElementsByTagname("Radio")
RadioCount = 1
for each itm in Radio
itm.value = RadioCount
RadioCount = RadioCount + 1
next itm


End Sub
 
N

naveen kumar

hi can u please tell me how to get ADDRESS, CITY, STATE, ZIP in single function.....


now iam getting only zip.. along that i need ADDRESS, STATE CITY.

reply me

Thanks! In Advance

Naveen Kumar KN
(e-mail address removed)
Is it possible to create macro that will take data from an Excel sheet and
use it to populate a form on a webpage in IE?
I currently manually key information from a table in Excel into a form on a
webpage in IE. Each row on the table represents a unique submission to the
webpage. Can anyone help me write a macro that will write the data from the
first row to the form, "click" submit, write the data from the second row,
"click" submit, etc.? Some of the entry is clicking on the appropriate radio
button.

If this is not readily accomplished does anyone have any suggestions on how
to streamline/automate this process? About 90% of the time only 1 field on
the web form is different while ~15 fields are unchanged. Unfortunately the
page does not "remember" the last submission and will not recall the previous
entry so I end up keying all ~16 fields for every submission even when only 1
field changes.

I have limited VBA experience but if one of you helpful and talented experts
can provide at least the shell of some code i have always been able to make
it work in the past.

Thanks very much in advance.
On Monday, August 31, 2009 12:39 PM Rick Rothstein wrote:
Do you have a web address you can post so we can try and see if what you
want is doable? If so, can you also tells what cells on your worksheet go to
what fields you are referring to?
Click to expand...
On Monday, August 31, 2009 1:21 PM Joel wrote:
This may help. I request was made to run the google API function to perform
language translation. We generated a simple HTML file that had two boxes and
a submit button. Using VBA I moved the foreign language sentance into one
box, then press the submit button, and finally removed the translated date
from the 2nd box. Take the html text and put it in a file called
translate.html. The put the VBA code into an excel file. Change the name of
the URL (foolder on PC) to match the html file so you can understand how it
works.

You would need a table in the HTML code.

HTML file - save in a text file with html extension using notepad or
equivalent

------------------------------------------------------------------------------------------

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Google Translate API</title>
</head>

<body>


<form name="f" id="f" action="#" onsubmit="translate(); return false;">

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"></textarea>
<br />
<br />

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" />
<br />
<br />

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"></textarea></form>
<br />

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script>

</body>
</html>

--------------------------------------------------------------------------------------

VBA code : change URL = "c:\temp\working\translation.html" as required

---------------------------------------------------------------------------------------
Sub translate()

Dim objIE As Object
Dim strServAcct As String

URL = "c:\temp\working\translation.html"
'Open Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL

Do While IE.Busy = True Or IE.readystate <> 4
DoEvents
Loop

Set ForeignCells = Range("A1:B1")
For Each cell In ForeignCells
Set ForeignText = IE.document.getElementById("foreign_text")
Set submit = IE.document.getElementById("submit_button")

ForeignText.innertext = cell.Value
submit.Select
submit.Click
Do While IE.Busy = True Or IE.readystate <> 4
DoEvents
Loop
Set Translation = IE.document.getElementById("Translation")
Translation.innertext = cell.Offset(1, 0).Value
Next cell
End Sub



"confused" wrote:
Click to expand...
Click to expand...
Click to expand...
 
B

bobba venkat

Hi Berine,

This is Venkat, I need your help to update Excel Data in Web Page. Please help me to make my work easy. This helps me a lot and I will be very thank Full to you for this great help.

You can send me a mail to : (e-mail address removed)

For Ex: I have Created a Table in Excel as per below.


Address 1: 4th Floor, Chrusch Road.
Address 2: Cross Towers, Rock Gardern, India
City: Haryana
State: DL
ZIP:501250

The above Data is in Excel, i need to upload the same in the below link. And should say sumbit.

After this, I need to get the updated Status which is on web, that sentence need to be updated on Excel.

http://zip4.usps.com/zip4/welcome.jsp
Is it possible to create macro that will take data from an Excel sheet and
use it to populate a form on a webpage in IE?
I currently manually key information from a table in Excel into a form on a
webpage in IE. Each row on the table represents a unique submission to the
webpage. Can anyone help me write a macro that will write the data from the
first row to the form, "click" submit, write the data from the second row,
"click" submit, etc.? Some of the entry is clicking on the appropriate radio
button.

If this is not readily accomplished does anyone have any suggestions on how
to streamline/automate this process? About 90% of the time only 1 field on
the web form is different while ~15 fields are unchanged. Unfortunately the
page does not "remember" the last submission and will not recall the previous
entry so I end up keying all ~16 fields for every submission even when only 1
field changes.

I have limited VBA experience but if one of you helpful and talented experts
can provide at least the shell of some code i have always been able to make
it work in the past.

Thanks very much in advance.
On Monday, August 31, 2009 12:39 PM Rick Rothstein wrote:
Do you have a web address you can post so we can try and see if what you
want is doable? If so, can you also tells what cells on your worksheet go to
what fields you are referring to?
Click to expand...
On Monday, August 31, 2009 1:21 PM Joel wrote:
This may help. I request was made to run the google API function to perform
language translation. We generated a simple HTML file that had two boxes and
a submit button. Using VBA I moved the foreign language sentance into one
box, then press the submit button, and finally removed the translated date
from the 2nd box. Take the html text and put it in a file called
translate.html. The put the VBA code into an excel file. Change the name of
the URL (foolder on PC) to match the html file so you can understand how it
works.

You would need a table in the HTML code.

HTML file - save in a text file with html extension using notepad or
equivalent

------------------------------------------------------------------------------------------

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Google Translate API</title>
</head>

<body>


<form name="f" id="f" action="#" onsubmit="translate(); return false;">

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"></textarea>
<br />
<br />

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" />
<br />
<br />

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"></textarea></form>
<br />

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script>

</body>
</html>

--------------------------------------------------------------------------------------

VBA code : change URL = "c:\temp\working\translation.html" as required

---------------------------------------------------------------------------------------
Sub translate()

Dim objIE As Object
Dim strServAcct As String

URL = "c:\temp\working\translation.html"
'Open Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL

Do While IE.Busy = True Or IE.readystate <> 4
DoEvents
Loop

Set ForeignCells = Range("A1:B1")
For Each cell In ForeignCells
Set ForeignText = IE.document.getElementById("foreign_text")
Set submit = IE.document.getElementById("submit_button")

ForeignText.innertext = cell.Value
submit.Select
submit.Click
Do While IE.Busy = True Or IE.readystate <> 4
DoEvents
Loop
Set Translation = IE.document.getElementById("Translation")
Translation.innertext = cell.Offset(1, 0).Value
Next cell
End Sub



"confused" wrote:
Click to expand...
Click to expand...
Click to expand...
 

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

Request Form - Alert Team Member 0
Split form 0
Add additional information to excel form after original data entry 0
Macro to select and delete levels of a document 2
Auto populate value from one task to another in MS PROJECT 0
Copy previous record into form field 0
MS365 WORD: How to Create Macro Button to CLEAR ALL fields in fill-in form 1
How do I clear a form? 0

Top