Web Query

[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