Copy and Paste web page using Vba
 

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

Copy and Paste web page using Vba
 

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" wrote:

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

Copy and Paste web page using Vba
 

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

"joel" wrote:

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" wrote:

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

AddressToPostOffice Function Needed (USPS)
 

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

On Tuesday, April 07, 2009 5:17 AM Gwyndal wrote:

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


On Tuesday, April 07, 2009 7:05 AM joe wrote:

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" wrote:


On Tuesday, April 07, 2009 8:11 AM Gwyndal wrote:

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

"joel" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Lucene.Net Indexing Searching Entry Level Tutorial
http://www.eggheadcafe.com/tutorials...-tutorial.aspx

help- AddressToPostOffice Function.
 

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..

On Tuesday, April 07, 2009 5:17 AM Gwyndal wrote:

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


On Tuesday, April 07, 2009 7:05 AM joe wrote:

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" wrote:


On Tuesday, April 07, 2009 8:11 AM Gwyndal wrote:

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

"joel" wrote:


On Friday, October 15, 2010 1:28 AM naveen kumar wrote:

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


Submitted via EggHeadCafe - Software Developer Portal of Choice
Autocorrelation method in C# for signal analysis
http://www.eggheadcafe.com/tutorials...-analysis.aspx