Get a value from a table in an HTML document

[Terry V]

Hello
I need to be able to access information that is in an HTML document (in a
table), from an application that will be opened. (Although the application
is a webpage, it is dynamic in content). I need to be able to access a
particular cell (the same one every time) in this table and place its value
in an access cell.

Using the xlUp, I can place it as the next item in the column, but I cannot
seem to figure out how to get the value to begin with.

I believe it was on Jwalk that I saw the function/sub to open a webpage, but
I don't need to open it, only get a value from an open page.

Any suggestions?

Thank you so much
Terry V

 

[Dick Kusleika]

Terry

You can try something like this. Tools - Reference - Microsoft Internet
Controls. Then loop through the ShellWindows until you find an
HTMLDocument. Then loop through all the elements and test every HTMLTable
until you find the one you need. There's probably a more efficient way to
do this, but it's the only way I know. Here's an example using
http://www.dicks-blog.com/excel/2004/10/weekend_picks.html

Sub GetTableCell()

Dim ieApp As Object
Dim ieTbl As Object
Dim sws As SHDocVw.ShellWindows
Dim ieDoc As Object

Set sws = New SHDocVw.ShellWindows

For Each ieApp In sws
Set ieDoc = ieApp.Document

If TypeName(ieDoc) = "HTMLDocument" Then
For Each ieTbl In ieDoc.all
If TypeName(ieTbl) = "HTMLTable" Then
If ieTbl.Cells(0).innertext = "BYU" Then
Debug.Print ieTbl.Cells(10).innertext
End If
End If
Next ieTbl
Exit For
End If
Next ieApp

Set ieApp = Nothing

End Sub

 

[Terry V]

Dick
Hey this is really cool.
When I use this app, I will always have approximately 18 HTML documents open
at the same time.

Is there a way to add a "Like" option? I know what the first part of the
url is going to be each time, and I know what cell it will be in (the info I
need, is an email address). I think I can figure out what table on the page
it will be in, because it will be in the same cell on the same table every
time. The only thing that actually changes, is the information that is
displayed in the tables -- everything else is the same.


Thank you so much
Terry V

 

[Dick Kusleika]

Terry

A Like option on the document? If you know the document title, you can put
an if statement to test

If ieDoc.Title = "My Web Page"

or you can use a Like there too. If the only way you can identify the
document is by the URL, you can use the URL property

If ieDoc.URL Like "http:\\www.myweb.com\*"

Either of those should go after you make sure ieDoc is an HTMLDocument
object.

If the information you need is a link, you can loop through all the a
objects in the document

For Each ieTbl in ieDoc.Links
If ieTbl.Href Like "mailto: (e-mail address removed)" Then

I don't know of any way to loop through the tables, or to go to a specific
table. If you know what number element the table is, you could do something
like

ieDoc.all(564).Cells(8).InnerText

but I don't know how you would determine the 564 other than trial and error.

I hope that answers your questions, but be sure to post back if not.

 

[Terry V]

Thank you Dick for your replies.
---- If ieDoc.URL Like "http:\\www.myweb.com\*"
This is more of what Im looking for.
If the URL of the webpage is like http://www.domain.com/docname*

within that html document, what does the 564 represent?
What Im attempting to do is : at work, I want to be able to keep track of
clients that call each day, then place them into a worksheet. So that when
I need to do a review of all clients that called back with a repeat
incident, I can track it. When they call in, thier info is placed into a
dynamic html document (maybe php; can't remember) where the fields are all
the same in the generated table and the values are placed in the same
column/cell as the previous or next client info.

So, Im trying to get the Date and email address to be placed into my excel
sheet to prevent me from copy/paste for several hrs at the end of each
period.... then having to manually / visually match the names.

Right now, I have to lookup every client for each day's work to see if they
called back. however, if I can keep track of each person that called, I can
automate a lookup for each name in the list. Saving myself approx 20 hr
work (unpaid) at the end of each month.

Thank you so much
Terry V


Thank you
Terry V

 

[Dick Kusleika]

Terry

Thank you Dick for your replies.
---- If ieDoc.URL Like "http:\\www.myweb.com\*"
This is more of what Im looking for.
If the URL of the webpage is like http://www.domain.com/docname*

So you can find the right HTMLDocument, now you just need to find the right
information on it. Does that sound right?

within that html document, what does the 564 represent?

An HTMLDocument has a number of elements on it. Elements are tables, links,
divs, etc. I would give you a more concrete definition, but I don't really
know it that well. ieDoc.all returns a collection of all the elements and
we loop through them. If you know which element you need, you can go
directly to it. Say there's 1000 elements on your document and the table
you need is the 564th. You can go right to that element because you know
it's the 564th element.

In the example I gave before, there are 319 elements. Here's a sample

306 HTMLParaElement
307 HTMLLabelElement
308 HTMLInputElement
309 HTMLBRElement
310 HTMLParaElement
311 HTMLLabelElement
312 HTMLBRElement
313 HTMLTextAreaElement
314 HTMLDivElement
315 HTMLInputElement
316 HTMLInputElement
317 HTMLDivElement
318 HTMLScriptElement
319 HTMLImg

I wanted to present that method to you, but honestly I would almost never
use it. Hardcoding a number like that is scary. If one little thing
changes and you are off by one number, it's broke, and you may as well be
off by 10,000.

What Im attempting to do is : at work, I want to be able to keep track of
clients that call each day, then place them into a worksheet. So that when
I need to do a review of all clients that called back with a repeat
incident, I can track it. When they call in, thier info is placed into a
dynamic html document (maybe php; can't remember) where the fields are all
the same in the generated table and the values are placed in the same
column/cell as the previous or next client info.

So, Im trying to get the Date and email address to be placed into my excel
sheet to prevent me from copy/paste for several hrs at the end of each
period.... then having to manually / visually match the names.

Right now, I have to lookup every client for each day's work to see if they
called back. however, if I can keep track of each person that called, I can
automate a lookup for each name in the list. Saving myself approx 20 hr
work (unpaid) at the end of each month.

When I'm trying to work with HTML from VBA, here's what I do. I go to
http://msdn.microsoft.com/library/default.asp and navigate like this: Web
Development > HTML and Dynamic HTML > SDK Documentation > Reference >
Objects. I usually start with the Document object and I search around for a
property or collection that looks like what I need. I just don't know this
object model well enough to go directly to what I need so it's a lot of
searching for something that looks right. You can go there and search
around for something better based on what you know about the HTMLDocument
you have. I'm telling you this because I don't want you to think that my
way is definitive. It's just the only way I've figured out so far.

With your Like operator, you can find the right document and set that to
ieDoc. Now you need to find the right table. You need to figure our what
is unique about that table. Is it the only table in the document? If so,
just loop through the elements in ieDoc.all until you get one whose TypeName
is HTMLTable, and you know you will be there. If there's more than one
table in the Document, but you know which number it is, say the 10th table,
you could loop through all the elements and keep count of the HTMLTable
objects you encounter. When you get to number 10, stop and use
Cells(x).InnerText to get the values you need.

You saw from my first example that I checked the InnerText of Cells(0) (the
first cell) and that was an example to use if the text in the first cell is
consistent and you can use that to identify that you're in the right table.

Let's say that the first cell will be a date, but that the date can change.
If it's also true that no other tables will have a date in their first cell,
you could use this information to identify the correct table. For example:

For Each ieTbl in ieDoc.all
If TypeName(ieTbl) = "HTMLTable" Then
If IsDate(ieTbl.Cells(0).Innertext) Then
Sheet1.Cells(1,1).Value = ieTbl.Cells(8).Innertext
End If
End If
Exit For
Next ieTbl

I know I'm not giving you the concrete answers you probably want, but it's
all I've got. Once you can determine what's unique about that table such
that you can identify it, the rest should be easy. If you can tell me
what's unique, but you still need help with the code, post back. If you
can't tell me what's unique, tell me why you can't or think you can't. I
assume an example document is not available for me to look at, but if I'm
wrong, tell me that too.

 

[Terry V]

LOL
Ive been staring at the MSDN site all night now, I can't get my head around
it all. But I'll keep trying.

Thanks Dick
Terry V