Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 193
Default Macro to populate web form

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.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Macro to populate web form

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?

--
Rick (MVP - Excel)


"confused" wrote in message
...
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.


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default Macro to populate web form

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


"confused" wrote in message
...
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.



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Macro to populate web form

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" /
<titleGoogle 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:

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.

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 193
Default Macro to populate web form

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.

"Bernie Deitrick" wrote:

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


"confused" wrote in message
...
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.






  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Macro to populate web form

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






"confused" wrote:

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.

"Bernie Deitrick" wrote:

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


"confused" wrote in message
...
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.




  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 193
Default Macro to populate web form

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?

"Joel" wrote:

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






"confused" wrote:

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.

"Bernie Deitrick" wrote:

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


"confused" wrote in message
...
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.



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Macro to populate web form

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.

"confused" wrote:

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?

"Joel" wrote:

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






"confused" wrote:

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.

"Bernie Deitrick" wrote:

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


"confused" wrote in message
...
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.



  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 193
Default Macro to populate web form

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.


"Joel" wrote:

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.

"confused" wrote:

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?

"Joel" wrote:

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






"confused" wrote:

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.

"Bernie Deitrick" wrote:

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


"confused" wrote in message
...
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.



  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Macro to populate web form

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

http://www.microsoft.com/office/comm...c-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




"confused" wrote:

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.


"Joel" wrote:

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.

"confused" wrote:

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?

"Joel" wrote:

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






"confused" wrote:

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.

"Bernie Deitrick" wrote:

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


"confused" wrote in message
...
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.





  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default You need to identify the objects on the web page that have thevalues entered.

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


On Monday, August 31, 2009 12:04 PM confused wrote:


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?

--
Rick (MVP - Excel)



On Monday, August 31, 2009 12:41 PM Bernie Deitrick wrote:


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



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" /
<titleGoogle 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:



On Monday, August 31, 2009 2:23 PM confused wrote:


Unfortunately it is a secure web page so I cannot post a working URL.
Even more unfortunately I cannot veiw the web page source so I cannot 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.

"Bernie Deitrick" wrote:



On Monday, August 31, 2009 2:35 PM Joel wrote:


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






"confused" wrote:



On Monday, August 31, 2009 3:08 PM confused wrote:


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 do not 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?

"Joel" wrote:



On Monday, August 31, 2009 3:23 PM Joel wrote:


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.

"confused" wrote:



On Monday, August 31, 2009 4:20 PM confused wrote:


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 do not 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 is not a way to basically send keystrokes to the web page I am 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.


"Joel" wrote:



On Monday, August 31, 2009 6:20 PM Joel wrote:


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

http://www.microsoft.com/office/comm...c-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




"confused" wrote:



Submitted via EggHeadCafe - Software Developer Portal of Choice
Freeze Row Group Header in WPF DataGrid
http://www.eggheadcafe.com/tutorials...-datagrid.aspx

  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default You have to put the URL of the webpage in the macro.

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 :

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

On Monday, August 31, 2009 12:04 PM confused wrote:


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?

--
Rick (MVP - Excel)



On Monday, August 31, 2009 12:41 PM Bernie Deitrick wrote:


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



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" /
<titleGoogle 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:



On Monday, August 31, 2009 2:23 PM confused wrote:


Unfortunately it is a secure web page so I cannot post a working URL.
Even more unfortunately I cannot veiw the web page source so I cannot 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.

"Bernie Deitrick" wrote:



On Monday, August 31, 2009 2:35 PM Joel wrote:


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






"confused" wrote:



On Monday, August 31, 2009 3:08 PM confused wrote:


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 do not 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?

"Joel" wrote:



On Monday, August 31, 2009 3:23 PM Joel wrote:


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.

"confused" wrote:



On Monday, August 31, 2009 4:20 PM confused wrote:


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 do not 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 is not a way to basically send keystrokes to the web page I am 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.


"Joel" wrote:



On Monday, August 31, 2009 6:20 PM Joel wrote:


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

http://www.microsoft.com/office/comm...c-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




"confused" wrote:



On Thursday, October 21, 2010 3:28 AM naveen kumar wrote:


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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate form from row data ryguy7272 Excel Programming 0 June 19th 09 10:52 PM
Populate form from row data Kent McPherson[_2_] Excel Programming 0 June 19th 09 10:33 PM
populate form from where the cursor is Diana Morrison Excel Worksheet Functions 1 December 7th 07 09:57 PM
Populate a form Doug Excel Worksheet Functions 3 December 15th 06 04:33 PM
VB Form to populate spreadsheet. Glenn Robertson Excel Programming 4 February 24th 04 01:10 PM


All times are GMT +1. The time now is 09:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"