Hello all:

I would like to know how to get a web query search let's say google for info
that are entered in excel. For example I have a zip code in excel in cell A1,
then I want to have a macro that should get a web query and search in google
for that zip that is entered in cell A1 and give me then the results.

Please let me know.

Thanks in advance.

Art...There are several ways to accomplish this, here is one. A1 on
the activesheet is where you would place the zip code or search term,
A3 is where the copied webpage results will appear. You may want to
add a loop to bring in data from pages beyond page 1 of the Google
results. Take a look at the source code behing www.google.com to see
how the "q" and "btnG" terms are identified...Ron


Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "http://www.google.com/"
.Top = 50
.Left = 530
.Height = 400
.Width = 400

' Loop until the page is fully loaded
Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

' Make the desired selections on the Login web page and click the
submit button
Set ipf = ie.Document.all.Item("q")
ipf.Value = Range("A1")

ie.Document.all.Item("btnG").Click

' Loop until the page is fully loaded
Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

' Copy the entire web page and then paste it as text into the
worksheet
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

Range("A3").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False
Range("A3").Select

End With

ie.Quit

Where should I paste it in? In "Sheet1" or in "ThisWorkBook"? And what should
I do afterwards? Thanks for your help.


"ron" wrote:

Art...There are several ways to accomplish this, here is one. A1 on
the activesheet is where you would place the zip code or search term,
A3 is where the copied webpage results will appear. You may want to
add a loop to bring in data from pages beyond page 1 of the Google
results. Take a look at the source code behing www.google.com to see
how the "q" and "btnG" terms are identified...Ron


Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "http://www.google.com/"
.Top = 50
.Left = 530
.Height = 400
.Width = 400

' Loop until the page is fully loaded
Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

' Make the desired selections on the Login web page and click the
submit button
Set ipf = ie.Document.all.Item("q")
ipf.Value = Range("A1")

ie.Document.all.Item("btnG").Click

' Loop until the page is fully loaded
Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

' Copy the entire web page and then paste it as text into the
worksheet
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

Range("A3").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False
Range("A3").Select

End With

ie.Quit

On Sep 3, 7:56*am, art wrote:
Where should I paste it in? In "Sheet1" or in "ThisWorkBook"? And what should
I do afterwards? Thanks for your help.



"ron" wrote:
Art...There are several ways to accomplish this, here is one. *A1 on
the activesheet is where you would place the zip code or search term,
A3 is where the copied webpage results will appear. *You may want to
add a loop to bring in data from pages beyond page 1 of the Google
results. *Take a look at the source code behingwww.google.comto see
how the "q" and "btnG" terms are identified...Ron

* * Set ie = CreateObject("InternetExplorer.Application")

* * With ie
* * * * .Visible = True
* * * * .Navigate "http://www.google.com/"
* * * * .Top = 50
* * * * .Left = 530
* * * * .Height = 400
* * * * .Width = 400

' Loop until the page is fully loaded
* * * * Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
* * * * * * *DoEvents
* * * * Loop

' Make the desired selections on the Login web page and click the
submit button
* * * * Set ipf = ie.Document.all.Item("q")
* * * * ipf.Value = Range("A1")

* * * * ie.Document.all.Item("btnG").Click

' Loop until the page is fully loaded
* * * * Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
* * * * * * *DoEvents
* * * * Loop

' Copy the entire web page and then paste it as text into the
worksheet
* * * * ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
* * * * ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

* * * * Range("A3").Select
* * * * ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False
* * * * Range("A3").Select

* * End With

* * ie.Quit- Hide quoted text -

- Show quoted text -

Art...Insert a module into whichever workbook contains the zipcode in
A1. In the module, enter

Sub Pick_A_Name ()

End Sub

Next copy/paste the code from above into the module between Sub and
End Sub. Then, just run the macro...Ron

It tells me there is an error "sub or finction not defined". And it
highlights the words "submit Button". Do you know what's the problem?

Thanks.

"ron" wrote:

On Sep 3, 7:56 am, art wrote:
Where should I paste it in? In "Sheet1" or in "ThisWorkBook"? And what should
I do afterwards? Thanks for your help.



"ron" wrote:
Art...There are several ways to accomplish this, here is one. A1 on
the activesheet is where you would place the zip code or search term,
A3 is where the copied webpage results will appear. You may want to
add a loop to bring in data from pages beyond page 1 of the Google
results. Take a look at the source code behingwww.google.comto see
how the "q" and "btnG" terms are identified...Ron

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "http://www.google.com/"
.Top = 50
.Left = 530
.Height = 400
.Width = 400

' Loop until the page is fully loaded
Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

' Make the desired selections on the Login web page and click the
submit button
Set ipf = ie.Document.all.Item("q")
ipf.Value = Range("A1")

ie.Document.all.Item("btnG").Click

' Loop until the page is fully loaded
Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

' Copy the entire web page and then paste it as text into the
worksheet
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

Range("A3").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False
Range("A3").Select

End With

ie.Quit- Hide quoted text -

- Show quoted text -

Art...Insert a module into whichever workbook contains the zipcode in
A1. In the module, enter

Sub Pick_A_Name ()

End Sub

Next copy/paste the code from above into the module between Sub and
End Sub. Then, just run the macro...Ron

The words Submit button should be commented. Web browsers don't really allow
you to copy info the way it was typed. Just put a ' before the words submit
button and re-run.
art wrote:
It tells me there is an error "sub or finction not defined". And it
highlights the words "submit Button". Do you know what's the problem?

Thanks.

Where should I paste it in? In "Sheet1" or in "ThisWorkBook"? And what should
I do afterwards? Thanks for your help.
[quoted text clipped - 58 lines]
Next copy/paste the code from above into the module between Sub and
End Sub. Then, just run the macro...Ron

--
Message posted via http://www.officekb.com