Copy and Paste web page using Vba

[Gwyndalf]

The code below opens IE and allows me to navigate to my chosen page. What I
cannot make happen is for IE to select the whole page , copy it and then
allow me to paste it into my worksheet ("Hands"). Anyone know how I can do
this?

Sub ListLinks()

Dim IeApp As InternetExplorer
Dim sURL As String
Dim IeDoc As Object
Dim MyURL As String
' I need this to be variable/ user defined
MyURL = Application.GetOpenFilename()
Set IeApp = New InternetExplorer

'Make it visible
IeApp.Visible = True

'define the page to open
sURL = MyURL

'navigate to the page
IeApp.navigate sURL

'Pause the macro using a loop until the
'page is fully loaded
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE

Code needed here to copy and paste entire web page

Worksheets("Hands").Activate
Range("A1").Select
ActiveSheet.Paste

'Clean up
Set IeApp = Nothing

End Sub

 

[joel]

Do you want to copy the page as a PICTURE or TEXT. If you are using text it
depends on how the webpage is arranged. Each webpage is different the best
test is to use a webquery.

Go to worksheet menu

Data - Import External Data - New Webquery

Paste your URL into the address box. Then when you go to the webpagge
you'll see different check boxes. Depending on which check boxes you select
will depend on which data will get imported. You can record a macro while
doing the webquery so you can add this code into your macro instead of using
the IE application.

If the query doesn't give you the results you want then a program can be
written to get any data you want from the webpage, but it is not esy code to
write. Here is one example of a program I wrote.

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

 

[Gwyndalf]

Thanks for your time Joel

This Q was re-sent when site said service was temp unavail. With webquery
it returned only the title of the htm document and said there was no other
data - hence I'd put in the other post - (unfortunately i omitted it from
this one) that WebQuery was of no use to me. Ron provided a solution using
ExecWB 17,0 and ExecWB 12,2 which works great. Small recompense for you but
I have rated your post - again thanks for a quick reply

 

[naveen kumar]

HI,
thanks for posting zipcode verify FUNCTION with USPS..

like that.. i need to verify ADDRESS using FUNCTION.. can u just send me the FUNCTION which works for usps ADDRESS verification.. (http://zip4.usps.com/zip4/welcome.jsp)

ive tried to change zip function to address function by adding neccessary fields.. but not working out...


input: Address1, City, State..

get output in excel cell (excel function) just like ZIPfunction..


THANKS IN ADVANCE..

NAVEEN KUMAR KN

 

[naveen kumar]

send me AddressToPostOffice FUNCTION works with excel... with USPS... site..


just like ziptopostoffice.. function..


here input is 3.. address1, city state


output will be 1 (address1+city+state) and will be directed to single cell..