Code stops prematurely unless VB Editor open...

[Gregg Roberts]

It stops on the second iteration of a loop at a point that it successfully
gets through when there is only a single iteration.

I'm grabbing the visible text of a webpage using SendKeys to manipulate the
menus in IE. Using CTRL+A, CTRL+C, and then coming back to Excel and doing
CTRL+V doesn't work at all -- using CTRL in a SendKeys statement has never
worked for me. But using the percent symbol for ALT does work:

(with IE as the active app)

SendKeys "{TAB 4}", True
SendKeys "%e", True
SendKeys "{DOWN 3}", True
SendKeys "{ENTER}", True
Wait (0.2)
SendKeys "%e", True
SendKeys "{DOWN 1}", True
SendKeys "{ENTER}", True
Wait (0.2)
AppActivate "Microsoft Excel"
Cells(CurURLReadRow, 10).Select
SendKeys "{F2}", True
ActiveSheet.Paste
SendKeys "{ENTER}", True

----------

Since I'm using several SendKeys statements, I can't freely run the code to
the point where it stops with the VB Editor open, because it "sends the keys"
to the VB Editor.

When there's a second URL to process, the code stops after executing:
SendKeys "{F2}", True

It's as if there is nothing in the clipboard to paste, AND as if there's no
other code after that either. But in the IE window, the page contents are
selected, just as in the case where there's only a single iteration.

I'm open to a better way to do a lot of this. Manipulating the IE document
object seems to be haphazard at best. It works for grabbing the Title and
Description tags, but then it tells me that the number of IMGs and H2s is
zero, when I'm looking right at them in the source. So I'm using it for the
tags that it seems to handle properly, but the more examples I test with, the
more I'm finding that the code isn't working as well as I thought it was.

The most frustrating part is that I have found a workaround -- using
SendKeys -- and it works, but only for one iteration.

Using Excel 2000 on an XPSP2 machine.

TIA...

Gregg Roberts

 

[George]

It stops on the second iteration of a loop at a point that it successfully
gets through when there is only a single iteration.

I'm grabbing the visible text of a webpage using SendKeys to manipulate the
menus in IE. Using CTRL+A, CTRL+C, and then coming back to Excel and doing
CTRL+V doesn't work at all -- using CTRL in a SendKeys statement has never
worked for me. But using the percent symbol for ALT does work:

(with IE as the active app)

SendKeys "{TAB 4}", True
SendKeys "%e", True
SendKeys "{DOWN 3}", True
SendKeys "{ENTER}", True
Wait (0.2)
SendKeys "%e", True
SendKeys "{DOWN 1}", True
SendKeys "{ENTER}", True
Wait (0.2)
AppActivate "Microsoft Excel"
Cells(CurURLReadRow, 10).Select
SendKeys "{F2}", True
ActiveSheet.Paste
SendKeys "{ENTER}", True

----------

Since I'm using several SendKeys statements, I can't freely run the code to
the point where it stops with the VB Editor open, because it "sends the keys"
to the VB Editor.

When there's a second URL to process, the code stops after executing:
SendKeys "{F2}", True

It's as if there is nothing in the clipboard to paste, AND as if there's no
other code after that either. But in the IE window, the page contents are
selected, just as in the case where there's only a single iteration.

I'm open to a better way to do a lot of this. Manipulating the IE document
object seems to be haphazard at best. It works for grabbing the Title and
Description tags, but then it tells me that the number of IMGs and H2s is
zero, when I'm looking right at them in the source. So I'm using it for the
tags that it seems to handle properly, but the more examples I test with, the
more I'm finding that the code isn't working as well as I thought it was.

The most frustrating part is that I have found a workaround -- using
SendKeys -- and it works, but only for one iteration.

Using Excel 2000 on an XPSP2 machine.

TIA...

Gregg Roberts

Sounds like an interesting problem.

Im just wondering after your first iteration, whether you made IE the
active app again since it looks like you made excel the active app
Anyway it's just a thought.

I'm sure there is a better way to do this - although I'm not the person
with any expertise in this area of web page manipulation. Sorry

George

 

[sebastienm]

Hi,
Not sure if that can be applied to your project...
What about importing the webpage into a hidden excel sheet using a web query?
- put the macro recorder 'On'
- in a new sheet, goto menu Data > (get) Import External Data > New Web
Query. set the url, which table of that page (or the whole page) and click
Import.
- Stop the recorder.
- Look afor the generated code, it will help to create a Sub with a dynamic
url.
- In the process, once the web page is loaded, parse the sheet (using
regular xl methods/properties) to get the info you need and paste it at the
right spots.
- Don't forget to delete (all) querytables and clearing the sheet before
loading a new one.

 

[Gregg Roberts]

What about importing the webpage into a hidden excel sheet using a web query?

Thanks for reminding me that you can retrieve an entire page that way, not
just tables -- and for the reminder to delete the querytables.

However, I have to do a lot of text processing on the page content. I just
tried yourmethod, and it spreads the page content all over the worksheet and
I don't know how to prevent that. Using my method gets all the text into a
single cell, after which I can put it into a string variable with only one
more line of code.

Right now I'm making my code more modular while Iwait for an answer from the
community, which of course is good practice anyway. And it might solve the
problem, even though it shouldn't. I've had that happen.

BTW, I know that Word is better for text processing. I might take advantage
of its capabilities at some point, but I know Excel VBA much better than Word
VBA. The inability to use the mouse in the document while recording a Word
macro has always been a sticking point. And I'm counting a lot of things in
the text, with reference to things stored in Excel, and I need to be able to
sort and filter the results, etc., so Excel seems the natural choice. Using
only Excel and IE also keeps the client machine requirements to a minimum.

parse the sheet (using regular xl methods/properties) to get the
info you need and paste it at the right spots.

Since I can't assume all the web pages I load will have fully valid HTML, I
decided I really want to use the DOM and let it handle the HTML parsing
issues, for the routines that process the content of specific tags. Too much
niggling code and error handling to write otherwise.

 

[Gregg Roberts]

Im just wondering after your first iteration, whether you made IE the
active app again since it looks like you made excel the active app
Anyway it's just a thought.

Well, I have this:

IeApp.Navigate LinkURL
Do While IeApp.ReadyState <> READYSTATE_COMPLETE
Loop
Set IEDoc = IeApp.Document

....and IE does load the second URL. Would this code not make IE the active
app again? Maybe not. But I tried:

AppActivate "Microsoft Internet Explorer"

and

IEApp.Activate

I think the former had no effect, and the latter gave a syntax error -- 1004
I think.

I'm sure there is a better way to do this

I think that myself about 5-10 times a day. :-(

 

[Gregg Roberts]

George,

You did it!!!

Adding _AppActivate "Microsoft Internet Explorer"_ above the SendKeys
section did the trick. I don't know what happened before, but it was easy
enough to try it again.

Just keep posting your "inexpert" ideas! Thanks!

Gregg