Web Query

[art]

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.

 

[ron]

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

 

[art]

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

 

[ron]

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















- 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

 

[art]

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.

 

[dustinbrearton via OfficeKB.com]

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.

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

 

[art]

now it tells me the problem is "Worksheet". I tried putting a ' before
worksheet as well, but then other problems come up. Can you please help me
clear out this problem, or tell me any easy way to be able to make a web
query to search fot the zip code that will be in cell A1?

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.

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

 

[dustinbrearton via OfficeKB.com]

Art,
I use the below as a track trace prog at work. This should do what you
want with a few changes. First you will want to create a command button and
place it on your worksheet. Once the command button is in the sheet just
double click the button. That will open the Visual Basic Editor with a
private sub called CommandButton1_Click(). Between Private Sub Line and End
Sub put in openURL. Now go to insert on the tool bar and select Module.
This will open a new window in the VBE probably called module 1. The name of
this module doesn't really matter. Copy the code below and paste it into
this new module. Your web browser is going to mess up the Declare Function.
Just make sure that everything between Public Declare down to As Long is on
the first line of the module.

Post back if this doesn't do what you are wanting.

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String,
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As
Long) As Long
Public Sub openURL()

Dim value As String
Dim pos As Integer

value = Range("A1").value

value = Application.Substitute(value, " ", "+")

Debug.Print value

ShellExecute 0, vbNullString, "http://www.google.com/search?hl=en&q=" & value,
vbNullString, vbNullString, vbNormalFocus
End Sub

now it tells me the problem is "Worksheet". I tried putting a ' before
worksheet as well, but then other problems come up. Can you please help me
clear out this problem, or tell me any easy way to be able to make a web
query to search fot the zip code that will be in cell A1?

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

[quoted text clipped - 9 lines]

 

[art]

First of all I had to change the first part like this:

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String,
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As
Long)

It's a drop different then what you wrote.

But the main problem is it tells me "Syntax Error", and the following is
highlighted as wrong:

ShellExecute 0, vbNullString, "http://www.google.com/search?hl=en&q=" & value,

Please let me know how to correct this problem. (I have office 2007, if that
makes a difference.) Thanks for your help. Let me know please.

Art,
I use the below as a track trace prog at work. This should do what you
want with a few changes. First you will want to create a command button and
place it on your worksheet. Once the command button is in the sheet just
double click the button. That will open the Visual Basic Editor with a
private sub called CommandButton1_Click(). Between Private Sub Line and End
Sub put in openURL. Now go to insert on the tool bar and select Module.
This will open a new window in the VBE probably called module 1. The name of
this module doesn't really matter. Copy the code below and paste it into
this new module. Your web browser is going to mess up the Declare Function.
Just make sure that everything between Public Declare down to As Long is on
the first line of the module.

Post back if this doesn't do what you are wanting.

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String,
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As
Long) As Long
Public Sub openURL()

Dim value As String
Dim pos As Integer

value = Range("A1").value

value = Application.Substitute(value, " ", "+")

Debug.Print value

ShellExecute 0, vbNullString, "http://www.google.com/search?hl=en&q=" & value,
vbNullString, vbNullString, vbNormalFocus
End Sub

now it tells me the problem is "Worksheet". I tried putting a ' before
worksheet as well, but then other problems come up. Can you please help me
clear out this problem, or tell me any easy way to be able to make a web
query to search fot the zip code that will be in cell A1?

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

[quoted text clipped - 9 lines]

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

 

[dustinbrearton via OfficeKB.com]

That is another problem with the browser. Take the line below the one
highlighted and put it on the same line. All of the below should be on one
line.

ShellExecute 0, vbNullString, "http://www.google.com/search?hl=en&q=" & value,
vbNullString, vbNullString, vbNormalFocus

First of all I had to change the first part like this:

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String,
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As
Long)

It's a drop different then what you wrote.

But the main problem is it tells me "Syntax Error", and the following is
highlighted as wrong:

ShellExecute 0, vbNullString, "http://www.google.com/search?hl=en&q=" & value,

Please let me know how to correct this problem. (I have office 2007, if that
makes a difference.) Thanks for your help. Let me know please.

Art,
I use the below as a track trace prog at work. This should do what you

[quoted text clipped - 40 lines]

 

[art]

It tells me now:

Run-Time error '49:
Bad DLL calling convention

What does this mean?

Thanks for your help.

That is another problem with the browser. Take the line below the one
highlighted and put it on the same line. All of the below should be on one
line.

ShellExecute 0, vbNullString, "http://www.google.com/search?hl=en&q=" & value,
vbNullString, vbNullString, vbNormalFocus

First of all I had to change the first part like this:

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String,
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As
Long)

It's a drop different then what you wrote.

But the main problem is it tells me "Syntax Error", and the following is
highlighted as wrong:

ShellExecute 0, vbNullString, "http://www.google.com/search?hl=en&q=" & value,

Please let me know how to correct this problem. (I have office 2007, if that
makes a difference.) Thanks for your help. Let me know please.

Art,
I use the below as a track trace prog at work. This should do what you

[quoted text clipped - 40 lines]

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

 

[ron]

now it tells me the problem is "Worksheet". I tried putting a ' before
worksheet as well, but then other problems come up. Can you please help me
clear out this problem, or tell me any easy way to be able to make a web
query to search fot the zip code that will be in cell A1?

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 wordssubmit
button and re-run.

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

- Show quoted text -

Art...When I pasted my code into this newsgroup, the Google format
editor unfotuneately broke the text in a manner that, as you noted,
will create errors. Dustin's response is correct in terms of how to
remedy the problem. More completely the following lines need to be
adjusted

' Make the desired selections on the Login web page and click the
submit button

should appear all on one line in your code as

' Make the ......... the submit button
----------------------------------------------------------------------------------------

' Copy the entire web page and then paste it as text into the
worksheet

also place this all on one line as

' Copy the entire .......... into the worksheet
--------------------------------------------------------------------------------------------

ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False

also place this all on one line as
note: no apostrophe at the beginning of this line

ActiveSheet.PasteSpecial ....... =False, DisplayAsIcon:=False


Now try running the code, it should work. Let me know how it
goes...Ron

 

[dustinbrearton via OfficeKB.com]

Copy the Function code below and paste it over the function code you have in
the module. You should not change the way it looks or what it has in it.
Past it exactly how it is below. Make sure you remove the function that is
currently in there. If you do this correctly the Public function section
should be followed by a line and then go into the Public Sub OpenURL() sub.

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"
_
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal _
nShowCmd As Long) As Long

It tells me now:

Run-Time error '49:
Bad DLL calling convention

What does this mean?

Thanks for your help.

That is another problem with the browser. Take the line below the one
highlighted and put it on the same line. All of the below should be on one

[quoted text clipped - 25 lines]

 

[art]

I did what you wrote, but there are two problems:

1. It only open a google page, but does not search anything, nor import the
info to excel.
2. An error message comes up saying: Runtime error 2147467259 (80004005)'
Method 'Busy' of
object 'Iwebbrowser2' failed
and this line is highlighted when I press debug:

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy

Thanks for your help, but please try to help me further, until we get this
thing done.

Thanks again.

now it tells me the problem is "Worksheet". I tried putting a ' before
worksheet as well, but then other problems come up. Can you please help me
clear out this problem, or tell me any easy way to be able to make a web
query to search fot the zip code that will be in cell A1?

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

- Show quoted text -

Art...When I pasted my code into this newsgroup, the Google format
editor unfotuneately broke the text in a manner that, as you noted,
will create errors. Dustin's response is correct in terms of how to
remedy the problem. More completely the following lines need to be
adjusted

' Make the desired selections on the Login web page and click the
submit button

should appear all on one line in your code as

' Make the ......... the submit button
----------------------------------------------------------------------------------------

' Copy the entire web page and then paste it as text into the
worksheet

also place this all on one line as

' Copy the entire .......... into the worksheet
--------------------------------------------------------------------------------------------

ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False

also place this all on one line as
note: no apostrophe at the beginning of this line

ActiveSheet.PasteSpecial ....... =False, DisplayAsIcon:=False


Now try running the code, it should work. Let me know how it
goes...Ron

 

[dustinbrearton via OfficeKB.com]

Art,
Are you trying to get the results of the Google search to appear back in
Excel or just display the search results in Internet Explorer? Ron's
approach is a bit more complicated because he is trying to get the results of
your search out of the IE window and display them in Excel. If you are
trying to display the results in Excel you should use Ron's example. If you
are just wanting the search to happen in IE and be displayed in IE then use
the approach I posted. You were very close to having the code I wrote
function before if that is the road you want to go down. Thanks.

I did what you wrote, but there are two problems:

1. It only open a google page, but does not search anything, nor import the
info to excel.
2. An error message comes up saying: Runtime error 2147467259 (80004005)'
Method 'Busy' of
object 'Iwebbrowser2' failed
and this line is highlighted when I press debug:

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy

Thanks for your help, but please try to help me further, until we get this
thing done.

Thanks again.
[quoted text clipped - 52 lines]

Now try running the code, it should work. Let me know how it
goes...Ron

 

[art]

I need to have the results in excel. But why didn't his example search for
anything, and why didn't it bring in excel?
Please help?

Art,
Are you trying to get the results of the Google search to appear back in
Excel or just display the search results in Internet Explorer? Ron's
approach is a bit more complicated because he is trying to get the results of
your search out of the IE window and display them in Excel. If you are
trying to display the results in Excel you should use Ron's example. If you
are just wanting the search to happen in IE and be displayed in IE then use
the approach I posted. You were very close to having the code I wrote
function before if that is the road you want to go down. Thanks.

I did what you wrote, but there are two problems:

1. It only open a google page, but does not search anything, nor import the
info to excel.
2. An error message comes up saying: Runtime error 2147467259 (80004005)'
Method 'Busy' of
object 'Iwebbrowser2' failed
and this line is highlighted when I press debug:

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy

Thanks for your help, but please try to help me further, until we get this
thing done.

Thanks again.

now it tells me the problem is "Worksheet". I tried putting a ' before
worksheet as well, but then other problems come up. Can you please help me

[quoted text clipped - 52 lines]

Now try running the code, it should work. Let me know how it
goes...Ron

 

[ron]

I did what you wrote, but there are two problems:

1. It only open a google page, but does not search anything, nor import the
info to excel.
2. An error message comes up saying: Runtime error 2147467259 (80004005)'
                                                        Method 'Busy' of
object 'Iwebbrowser2' failed
and this line is highlighted when I press debug:

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy

Thanks for your help, but please try to help me further, until we get this
thing done.

Thanks again.

now it tells me the problem is "Worksheet". I tried putting a ' before
worksheet as well, but then other problems come up. Can you please help me
clear out this problem, or tell me any easy way to be able to make a web
query to search fot the zip code that will be in cell A1?
:
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

Art...When I pasted my code into this newsgroup, the Google format
editor unfotuneately broke the text in a manner that, as you noted,
will create errors.  Dustin's response is correct in terms of how to
remedy the problem.  More completely the following lines need to be
adjusted

' Make the desired selections on the Login web page and click the
submit button

 should appear all on one line in your code as

' Make the ......... the submit button
---------------------------------------------------------------------------­-------------

' Copy the entire web page and then paste it as text into the
worksheet

also place this all on one line as

' Copy the entire .......... into the worksheet
---------------------------------------------------------------------------­-----------------

ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False

also place this all on one line as
note: no apostrophe at the beginning of this line

ActiveSheet.PasteSpecial ....... =False, DisplayAsIcon:=False

Now try running the code, it should work.  Let me know how it
goes...Ron- Hide quoted text -

- Show quoted text -

1. It only open a google page, but does not search anything, nor import the
info to excel.

When you run the macro is the sheet containing the zip code in cell A1
the activesheet? For the macro to run correctly, it must be the
activesheet (e.g. the sheet you see when you view the Excel workbook)

2. An error message comes up saying: Runtime error 2147467259 (80004005)'
Method 'Busy' of
object 'Iwebbrowser2' failed
and this line is highlighted when I press debug:

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy

try
Do Until ie.ReadyState = READYSTATE_COMPLETE And Not ie.Busy

Also, while viewing the code in the Visual Basic Editor, click on
Tools, then References. Make sure the following references are
checked:
Visual Basic for Applications
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Office 10.0 Object Library

 

[art]

I did all these stuff, but the error message still comes, and besides, why
doesn't it look up the zip code from cell A1, and then import it in Excel?

I did what you wrote, but there are two problems:

1. It only open a google page, but does not search anything, nor import the
info to excel.
2. An error message comes up saying: Runtime error 2147467259 (80004005)'
Method 'Busy' of
object 'Iwebbrowser2' failed
and this line is highlighted when I press debug:

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy

Thanks for your help, but please try to help me further, until we get this
thing done.

Thanks again.

now it tells me the problem is "Worksheet". I tried putting a ' before
worksheet as well, but then other problems come up. Can you please help me
clear out this problem, or tell me any easy way to be able to make a web
query to search fot the zip code that will be in cell A1?

:
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

- Show quoted text -

Art...When I pasted my code into this newsgroup, the Google format
editor unfotuneately broke the text in a manner that, as you noted,
will create errors. Dustin's response is correct in terms of how to
remedy the problem. More completely the following lines need to be
adjusted

' Make the desired selections on the Login web page and click the
submit button

should appear all on one line in your code as

' Make the ......... the submit button
---------------------------------------------------------------------------­-------------

' Copy the entire web page and then paste it as text into the
worksheet

also place this all on one line as

' Copy the entire .......... into the worksheet
---------------------------------------------------------------------------­-----------------

ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False

also place this all on one line as
note: no apostrophe at the beginning of this line

ActiveSheet.PasteSpecial ....... =False, DisplayAsIcon:=False

Now try running the code, it should work. Let me know how it
goes...Ron- Hide quoted text -

- Show quoted text -

1. It only open a google page, but does not search anything, nor import the
info to excel.

When you run the macro is the sheet containing the zip code in cell A1
the activesheet? For the macro to run correctly, it must be the
activesheet (e.g. the sheet you see when you view the Excel workbook)

2. An error message comes up saying: Runtime error 2147467259 (80004005)'
Method 'Busy' of
object 'Iwebbrowser2' failed
and this line is highlighted when I press debug:

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy

try
Do Until ie.ReadyState = READYSTATE_COMPLETE And Not ie.Busy

Also, while viewing the code in the Visual Basic Editor, click on
Tools, then References. Make sure the following references are
checked:
Visual Basic for Applications
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Office 10.0 Object Library

 

[ron]

P.S. If

Do Until ie.ReadyState = READYSTATE_COMPLETE And Not ie.Busy

still doesn't work, try

Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop

....Ron

 

[art]

Now it tells me that this line is not correct:

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER