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.

 

[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

 

[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

 

[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

 

[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

 

[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

 

[ron]

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 getthis
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
--
Message posted viahttp://www.officekb.com-Hidequoted text -
- 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 howto
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)

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- Hide quoted text -

- Show quoted text -

Wich error message? The macro runs fine for me. Post the code from
your module, maybe something was inadvertently left out or added.
When you step through the macro (e.g. F8 while viewing the VBE editor)
does IE open? Move the cursor over the

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

construction without executing those lines. Now press F8 until you're
at

ipf.Value = Range("A1")

when you execute this line, does ipf.Value equal your zipcode?

What does IE do when you execute the next line
(ie.Document.all.Item("btnG").Click)? Did you get to the IE zipcode
results page?

Again, move the cursor over the next

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

construction without executing those lines

When you step through

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER

is all of the IE text selected?

When you step through

ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

is the selected text copied to the clipboard?..Ron

 

[art]

The error happens in the end:

When you step through

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER

is all of the IE text selected? NO they are not selected. The error message
recieved is: Run-time error 2147417848 (80010108)'
Automation error
the object invoked has disconnected from its clients

What does this mean?

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- Hide quoted text -

- Show quoted text -

Wich error message? The macro runs fine for me. Post the code from
your module, maybe something was inadvertently left out or added.
When you step through the macro (e.g. F8 while viewing the VBE editor)
does IE open? Move the cursor over the

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

construction without executing those lines. Now press F8 until you're
at

ipf.Value = Range("A1")

when you execute this line, does ipf.Value equal your zipcode?

What does IE do when you execute the next line
(ie.Document.all.Item("btnG").Click)? Did you get to the IE zipcode
results page?

Again, move the cursor over the next

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

construction without executing those lines

When you step through

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER

is all of the IE text selected?

When you step through

ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

is the selected text copied to the clipboard?..Ron

 

[ron]

The error happens in the end:

When you step through

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER

is all of the IE text selected? NO they are not selected. The error message
recieved is: Run-time error 2147417848 (80010108)'
Automation error
the object invoked has disconnected from its clients

What does this mean?

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 weget 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 tomake 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
--
Message posted viahttp://www.officekb.com-Hidequotedtext -
- 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 ofhow to
remedy the problem.  More completely the following lines needto 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 cellA1
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- Hide quoted text -
- Show quoted text -

Wich error message?  The macro runs fine for me.  Post the code from
your module, maybe something was inadvertently left out or added.
When you step through the macro (e.g. F8 while viewing the VBE editor)
does IE open?  Move the cursor over the

        Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
             DoEvents
        Loop

construction without executing those lines.  Now press F8 until you're
at

ipf.Value = Range("A1")

when you execute this line, does ipf.Value equal your zipcode?

What does IE do when you execute the next line
(ie.Document.all.Item("btnG").Click)?  Did you get to the IE zipcode
results page?

Again, move the cursor over the next

        Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
             DoEvents
        Loop

construction without executing those lines

When you step through

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER

is all of the IE text selected?

When you step through

ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

is the selected text copied to the clipboard?..Ron- Hide quoted text -

- Show quoted text -

Art...It would help diagnose the problem(s) if you answered the other
questions I posed. In the meantime, you might try replacing

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

with

ie.ExecWB 17, 2
ie.ExecWB 12, 0

and see if that selects and copies the IE page to the clipboard...Ron

 

[art]

I think the problem is OLECMDID_SELECTALL, there is no such function in excl
2007. That's what I think. Anyway else we can do select all?

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- Hide quoted text -

- Show quoted text -

Wich error message? The macro runs fine for me. Post the code from
your module, maybe something was inadvertently left out or added.
When you step through the macro (e.g. F8 while viewing the VBE editor)
does IE open? Move the cursor over the

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

construction without executing those lines. Now press F8 until you're
at

ipf.Value = Range("A1")

when you execute this line, does ipf.Value equal your zipcode?

What does IE do when you execute the next line
(ie.Document.all.Item("btnG").Click)? Did you get to the IE zipcode
results page?

Again, move the cursor over the next

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

construction without executing those lines

When you step through

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER

is all of the IE text selected?

When you step through

ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

is the selected text copied to the clipboard?..Ron

 

[art]

Thanks, now it works. One more thing, I really just need part of the page
let's say the first 3 lines, how do I do that?
I don't to select all.

Thanks.

The error happens in the end:

When you step through

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER

is all of the IE text selected? NO they are not selected. The error message
recieved is: Run-time error 2147417848 (80010108)'
Automation error
the object invoked has disconnected from its clients

What does this mean?

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- Hide quoted text -

- Show quoted text -

Wich error message? The macro runs fine for me. Post the code from
your module, maybe something was inadvertently left out or added.
When you step through the macro (e.g. F8 while viewing the VBE editor)
does IE open? Move the cursor over the

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

construction without executing those lines. Now press F8 until you're
at

ipf.Value = Range("A1")

when you execute this line, does ipf.Value equal your zipcode?

What does IE do when you execute the next line
(ie.Document.all.Item("btnG").Click)? Did you get to the IE zipcode
results page?

Again, move the cursor over the next

Do Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
DoEvents
Loop

construction without executing those lines

When you step through

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER

is all of the IE text selected?

When you step through

ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

is the selected text copied to the clipboard?..Ron- Hide quoted text -

- Show quoted text -

Art...It would help diagnose the problem(s) if you answered the other
questions I posed. In the meantime, you might try replacing

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

with

ie.ExecWB 17, 2
ie.ExecWB 12, 0

and see if that selects and copies the IE page to the clipboard...Ron