browser integration

[Maxi]

Tim helped me with a code to open browser through vba and navigate a
website. Code given below. However, I have two questions.

Sub foo()

Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate Range("A1").Value
Do While IE.ReadyState <> 4
DoEvents
Loop

With IE.document.all
.Uname.Value = "myUsername"
.Pass.Value = "myPassword"
.Submit.Click
End With

Do While IE.ReadyState <> 4
DoEvents
Loop

End Sub

Question1: If the webpage has a table and I want to look up data from
those tables then how do I find that out?

For instance: In the webpage I have lots of tables and in one of the
table which is of size 4X2 (4 rows and 2 columns). On the left side
there will be headings and on the right side a dynamic value.

Example
Report Owner: Maxi
Sent by: Tom
Sent to: Dick
Verified by: Harry

Somewhere down the code, I need a code that will check the Report
Owner, Sent by, Sent to, and Verified by and update it in cells B1 C1
D1 and E1. I have all my links in column A1:A25. I don't want to use a
web query because I have 25 links to loop through (mywebsite.com/
page1.htm, mywebsite.com/page2.htm .... page25.htm)

Here is the html code of the table

<table class="myPanel" border="0" cellpadding="2" cellspacing="0"
width="100%">
<tr class="row1">
<td class="rowLabel">Report Owner:</td>
<td valign="top">Maxi</td>
</tr>
<tr class="row0">
<td class="rowLabel">Sent by:</td>
<td><b>Tom</b></td>
</tr>
<tr class="row1">
<td class="rowLabel">Sent to:</td>
<td>Dick</td>
</tr>
<tr class="row0">
<td class="rowLabel">Verfied by:</td>
<td>Harry </td>
</tr>
</table>

I have read articles which says you can get it by getElementByID but
for that I need <table id="some name" but in my html code, there is no
"id", it says <table class="myPanel". Here I am getting confused.

Question1:
There will be an hyperlink on all pages that says "take me here" how
can I click on that website programatically?

Need help

Thank you
Maxi

 

[Randy Harmelink]

I have an add-in that might be able to do most of this for you
automatically. I don't have any login procedures in the routine, but
I have found if I log in manually once, via a Web Query, most (not
all) sites have kept me "logged in" for future uses. I even have a
workbook that grabs data from my NetFlix queue

For example, to get the "report owner" from your sample code, you'd
just need to use this formula from the add-in:

=RCHGetTableCell("http://whatever.web.page.com",1,"Report Owner:")

Basically, the function retrieves the source code of web page "http://
whatever.web.page.com", then looks for the string of "Report Owner:"
on the page, then returns the text content of the table cell following
it. The function has a number of other parameters -- mostly for page
positioning for extraction of data. I wrote the add-in to pull
financial data off of the web, because I grew weary of Web Queries
that would fail, not work quite right, or would return an entire table
of data when all I wanted was one item.

The add-in is free and open source. It, documentation on its
functions, and various templates (most for extraction of financial
data) can be found in the files area of this Yahoo group:

http://finance.groups.yahoo.com/group/smf_addin/

There is another example that would let you do all of your own
parsing, say if the data were not in a table:

=RCHGetWebData("http://whatever.web.page.com",1)

....would return the first 32767 bytes of the source code for the web
page. Or:

=RCHGetWebData("http://whatever.web.page.com","Report Owner:")

....would return the first 32767 bytes of the source code for the web
page once it found a string of "Report Owner:". Again, there are
other parameters, such as length and offset.

 

[Maxi]

Well I am very curious to try this out. I had been to the website but
kinda confused as to which file to download. There are lots of files
over there. I downloaded smfUpdateDownloadTable-Sample.xls file but it
is a 17kb file without any macros and nothing happens when i click Ctrl
+Shift+J

Can you send me the link to download that file?

 

[Randy Harmelink]

Best bet would be to read over at least the "Overview" document in the
"Documentation" folder. Current version of the add-in can be found in
the "Add-In Files" folder. None of the functions or macros will work
until after you've downloaded and activated the add-in.

 

[Maxi]

I installed the add-in and the RCHGetTableCell function is working
fine.... beyond my imagination that even this can be done. However, it
does not satisfy my criteria.

When I type =RCHGetTableCell("http://mywebsite.com/page1.htm",
1,"Report Owner:"), it gives me "Error" because it will not go that
page without proper authentication. In order to go to page1.htm, you
have to get authenticated by the http://mywebsite.com/ using a valid
username and password.

-------------

 

[Randy Harmelink]

On sites requiring a login, I've just used the > Data > New Web Query
process to login to the site manually. Once I've done that, the
functions work fine. You may need to run the smfForceRecalculation
macro once after loggin in, to empty out the saved web pages area.

So far, I've only run into one site that required frequent logins.
Most, I've done the login process once and been able to simply open my
spreadsheets that access the site without needed to do it again.

I decided not to add login processing to the add-in. Just as a
general rule, I know I'm not fond of entering username and password
into open spreadsheets or formulas that might be shared in one way or
another. The manual login process still leaves YOU in control of the
login process, not the add-in. And, like I said, since most require
it only once, I haven't found it to be burdensome.