Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate form from row data | Excel Programming | |||
Populate form from row data | Excel Programming | |||
populate form from where the cursor is | Excel Worksheet Functions | |||
Populate a form | Excel Worksheet Functions | |||
VB Form to populate spreadsheet. | Excel Programming |