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.