VBA code to open web page then enter number and postcode then subm

[nelly]

Hi I need to open an internal webpage after a number and postcode is entered
into a spreadsheet then submit these details on the webpage. Is there a way
of doing this?

Thanks in advance

 

[Joel]

Each webpage is different and i would need to know the URL of the webpage.
Below is the code for the US Zip code page. I assume you are not in the US
if you are using POSTCODE.


Sub GetZipCodes()

ZIPCODE = InputBox("Enter 5 digit zipcode : ")

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

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE

Form(0).Submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
If Table(0).Rows(0).innertext = "" Then
MsgBox ("Invalid Zip code")
Else
Location = Table(0).Rows(2).innertext
End If
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub

 

[nelly]

Hi Joel thanks for the quick response the site is
http://www.orange.co.uk/partner-savc/default.cfm

 

[Joel]

I don't have a valid phone number and postal code to complete the jog. Here
is what i have so far. I can enter the phone number and Postcode and submit
the form, but I don't get a valid response.

Sub PostCodes()


POSTCODE = InputBox("Enter PostCode (NW12 9BN): ")
If POSTCODE = "" Then
POSTCODE = "NW12 9BN"
End If

PHONENUMBER = InputBox("Enter Phone Number (02071234567): ")
If PHONENUMBER = "" Then
PHONENUMBER = "02071234567"
End If

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

URL = "http://www.orange.co.uk/partner-savc/default.cfm"


'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

Set Form = IE.document.getElementsByTagname("Form")
Set Inputform = Form.Item(0)

Set PhoneNumbox = Inputform.Item(0)
PhoneNumbox.Value = PHONENUMBER

Set PostCodebox = Inputform.Item(1)
PostCodebox.Value = POSTCODE


Set POSTCODEbutton = IE.document.getElementById("theForm")
POSTCODEbutton.onsubmit = POSTCODEbutton.action
POSTCODEbutton.submit

'POSTCODEbutton.Click
Do While IE.busy = True
DoEvents
Loop

'Set Table = IE.document.getElementsByTagname("Table")
'Location = Table(0).Rows(2).innertext
IE.Quit
'MsgBox ("Zip code = " & POSTCODE & " City/State = " & Location)


End Sub

 

[nelly]

Hi again Joel this works great and I dont have a combination that gives a
positive response although it seems the checker is off-line??? I have tried
to use your original code to enter the number as well but came unstuck but
from your updated version I can see why I was making mistakes so many many
thanks again. Now the info I get to put into the page is from 2 ranges
initially from text boxes in excel which is working fine. So can I now get
the response to copy back into excel?

 

[Joel]

To copy back I need a valid input. Once you have a webpage you can either
perform a query of the webpage or use VBA code to extract the data. Some
webpages the data are tables others you have to look for specific properties
using the Tag Name or the ID. I can't tell which is the best method until I
actually see the webpage.

 

[nelly]

Hi Joel

I don't think the web page uses tables there is none in the source code that
I can see! Can you view the webpage? I can send you a valid input but don't
really want to out it on here any suggestions?

Thanks for your help on this

 

[Joel]

email me at

joel dot warburg at itt dot com

Hi Joel

I don't think the web page uses tables there is none in the source code that
I can see! Can you view the webpage? I can send you a valid input but don't
really want to out it on here any suggestions?

Thanks for your help on this