Extracting specific items from large IE table

[expect_ed]

Trying to pull out specific items from a large IE table (500 rows, 1-4 cols).
Very limited knowledge of HTML is making it harder. Several questions:

Examples I have seen here always call the URL. The page I want to read
seems to come from a 'Post' Method and the URL is the same regardless of
which of thousands of documents you want. I can get to the front page of the
doc ok, but then must press a link which ends up with a URL like:
http://server.comp.com:8080/appl/requests.do
Is it possible to direct XL to the IE window if I am already on the page I
want?

Next problem is I am not sure I understand tag names well enough to know
what to put in the xxx's in the instruction:
Set Table = IE.document.getElementsByTagname("xxx")

I would like to go to row 483 of the table and extract the last column. The
HTML of the page looks something like this for each row/column:

<tr style='mso-yfti-irow:483'>
<td {formatting info}>
<p class=MsoNormal><span style='{more formatting
info}>CellContent<o></o></span></p>
</td>
....td - /td repeats for number of columns on that page

What is confusing me is that first style='. Is the 'mso-yfti-irow:483' a
tag name I can use to get to an exact row of the table or is the only way to
For-Next through all the rows until I get to the one I want to extract?

All help greatly appreciated.
ed

 

[joel]

I usually start with a few different methods

1) You can try to import the table using Data - Import Query - New Web
query. Record a macro while doing this a try to select the table you want.
The recorded macro will contain the table number that you can use in your vba
code. the table number is off by one because the query numbering starts at 1
and the VBA array starts at zero.

Set Tables = IE.document.Table("Table")
set mytable = table(12) 'really table 13

2) I dump the webpage to the worksheet using this code

RowCount = 1
for each itm in IE.document.all
Range("A" & RowCount) = itm.tagname
Range("B" & RowCount) = itm.classname
Range("A" & RowCount) = left(itm.innertext,1024)
RowCount = RowCount + 1
next itm

This line will get the tags
Set Form = IE.document.getElementsByTagname("Table")
This line will get the items that have id="abc" in the html
On some webpages the classname is the id.
Set zip5 = IE.document.getElementById("zip5")

3) Once you get the table to extract use rows then cells

RowCount = 1
For each myrow in Table.Rows
ColCount = 1
For each cell in myrow.cells
cells(RowCount,ColCount) = cell.innertext
ColCount = ColCount + 1
next cell
RowCount = Rowcount + 1
next MyRow

 

[expect_ed]

Thanks for the quick reply, joel,

Your first method is unworkable for me because I cannot get the correct page
into the Web Query box. When I click on the link a new browser window opens
with an error message. If I paste the URL for the resulting page - the one
that is the same for all pages - I get the login screen for the Web app. If
I try to right click the link to force it to open in the same window I do not
get that as an option.

I imagine there must be a VBA instruction that will direct XL to the open IE
window. Perhaps if I had that I could try your second method. Otherwise I
have the same problem that copying the URL of the page to direct XL to open
does not get me to the correct page so I cannot even get started with your
idea.

Thanks for the help.
ed

 

[joel]

You can do everything through the web rowser. I did a project like this last
week for somebody.

The login in window is probably a form. You can enter the login name and
password into the form from VBA. If you don't want to hard code the pasword
you can have a message box come up and you can enter the pasword into the
messge box and then have the VBA code put the pssword into the paasword on
the web page. If you look at the source code (internet explorer View -
Source) you can find the names of the boxes for the login and password using
the cod bloew. You will also need to find the SUBMIT Button object. This is
the code I worked on last week without the correct account or password.

Notice I checked for the TXUSERID object which is on the login screen. If I
didn't get the object I knew I was already login. To get to the table I was
donloading you had to login, then go to the main menu, and finally go to the
correct user page.

I can help you writed the code if you give me the webpage and account info.
Send me your email address so we don't publisize the account and password
where everybody can read the information. Or just send me the webpage and I
will get you through the login process.

Sub Net2Phone()
Dim objIE As Object
Dim strServAcct As String

strServAcct = "123456789"

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True

objIE.Navigate "https://partner.net2phone.com/apps/account/search.aspx"
WaitForLoad objIE

Set UserID = objIE.document.getElementById("txtUserID")
If Not UserID Is Nothing Then
'Input user name and password
UserID.Value = "famaperu45"
Set Password = objIE.document.getElementById("txtPassword")
Password.Value = "password"

'submit the form by clicking "Login"
Set Login = objIE.document.getElementById("btnlogin")
Login.Click

WaitForLoad objIE
End If

Set AccountBox = _
objIE.document.getElementById("ctl00$pageBody$txtServiceAccount")
AccountBox.Value = strServAcct

Set SearchButton = _
objIE.document.getElementById("ctl00$pageBody$btnSearch")
SearchButton.Click

WaitForLoad objIE

objIE.Navigate "https://partner.net2phone.com/apps/account/calls.aspx"
WaitForLoad1 objIE
RowCount = 1

 

[joel]

The web query method will work because you can enter the password into the
query IE explorer and then browse to any directory you want. The query won't
end until you select a table and press the import button on the query window.

 

[expect_ed]

Thanks again for your help and kind offer. Actually I would rather have the
fishing pole than the fish, i.e. understand how to do this myself.
If you look closely below I trid to explain why the Web Query does not/will
not work. I cannot browse within the query box to the page I want to
interpret. When clicking on the link on the web page within the Web Query
tool to take me to the page i want another window opens with an error
message. If I have your email I can send you a couple sample pages if you
care to look at them.

I think my real solution is to browse to the page manually and then run a
macro in excel that will choose the active IE window that is ready to
decipher. If I have a way to do that I think I can figure out the rest from
the other info you have so genrously provided.
Thanks
ed

 

[joel]

With the web query after you get an error you can enter any URL in the
address box to get to the page your want to dowload. If you don't see the
address box you can go to the View menu on the web page and change the
settings.

You can send me the web page you want me to look at at this email address

joel dot warburg at itt dot com

You can gedt the IEprocess id by using

Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd
As Long, lpdwProcessId As Long) As Long

lpdwProcessId is declared as BYREF (default when not specified). the
process id is returned for the indernet explorer.

You can then place the process id into
AppActivate title[, wait] where title can be replaced with Process ID

See VBA help AppActivate Statement

I haven't tried this myself. But I pretty sure you can get the equivalent
of IE from the process. I think it would be the object return from the
process .APPLICATION


see webpage for more details under KILL webpage
http://support.microsoft.com/kb/231844

I still think it will be easier to do everything from the IE object.