VBA to open web page, find text, and click on text's URL link?

[craig]

How can I use VBA (in Excel) to open a web page, find particular text
on that web page (e.g., "02-005"), and then "click" into the URL that
is associated with that text?

If I could do this, it would streamline a task I have to manually do
over & over & over again.

The end-result would be viewing a web page document associated with
that document identifier. Somehow the document web server is designed
to only serve documents via a click on the web page; it is not possible
to access the document by opening the URL of the document directly.

I do know how to open a web page to a URL using
ActiveWorkbook.FollowHyperlink Address:="http://...", and also can use
SendKeys, but don't know how to find the desired text on the page and
click on that text.

Is it possible? (If VBA can't do it alone, would ActiveX or a 3rd
party ActiveX control help?)

Craig in NJ

 

[Lance Wynn]

You can use the WebBrowser control to open the page, and then loop through
the document("all") collection object, and check each "A" tag's innerText
property for the search string, if it's the correct string then you can
navigate to the href of the anchor.


How can I use VBA (in Excel) to open a web page, find particular text
on that web page (e.g., "02-005"), and then "click" into the URL that
is associated with that text?

If I could do this, it would streamline a task I have to manually do
over & over & over again.

The end-result would be viewing a web page document associated with
that document identifier. Somehow the document web server is designed
to only serve documents via a click on the web page; it is not possible
to access the document by opening the URL of the document directly.

I do know how to open a web page to a URL using
ActiveWorkbook.FollowHyperlink Address:="http://...", and also can use
SendKeys, but don't know how to find the desired text on the page and
click on that text.

Is it possible? (If VBA can't do it alone, would ActiveX or a 3rd
party ActiveX control help?)

Craig in NJ

 

[craig]

Thank you very much, Lance. It took a few days before I could
experiment with this, and it took me (a novice to this task) days to
figure out exactly how do do what you said, but it looks like it'll
work great for me.

I've successfully opened the database's web site via an
InternetExplorer object (part of the same component group as
WebBrowser), can see the document.links.item(#).href elements and lots
of other things, and can then make this instance of the browser
..Navigate to any URL link I find on the page. I'm really glad to see
that .Navigate(...) works as well as a user "click." (Manually typing
in the identical URL in the browser's URL field is somehow refused by
the server, but .Navigate() seems to work fine.)

Better yet: Not only should I be able to hunt my way through to any
particular report I need to access, but it looks like I can even pull
any given data fields off the reports. I wasn't even thinking of that.
With some coding it'll save me 4 to 8 hours of manual effort each
month or more often, and that can be invaluable when I need to pull
updated data from over a hundred reports in a rush.

Thanks again!

Craig in NJ

 

[Lance Wynn]

Cool, I'm glad you got it working. This type of automation is what VBA was
made for

Lance

Thank you very much, Lance. It took a few days before I could
experiment with this, and it took me (a novice to this task) days to
figure out exactly how do do what you said, but it looks like it'll
work great for me.

I've successfully opened the database's web site via an
InternetExplorer object (part of the same component group as
WebBrowser), can see the document.links.item(#).href elements and lots
of other things, and can then make this instance of the browser
..Navigate to any URL link I find on the page. I'm really glad to see
that .Navigate(...) works as well as a user "click." (Manually typing
in the identical URL in the browser's URL field is somehow refused by
the server, but .Navigate() seems to work fine.)

Better yet: Not only should I be able to hunt my way through to any
particular report I need to access, but it looks like I can even pull
any given data fields off the reports. I wasn't even thinking of that.
With some coding it'll save me 4 to 8 hours of manual effort each
month or more often, and that can be invaluable when I need to pull
updated data from over a hundred reports in a rush.

Thanks again!

Craig in NJ