Integrating browser with excel

[Maxi]

Hi! friends,

I have a question but not sure if this is possible with xl macros.

I have few website addresses in the range B2:B6. Here are they:
http://www.mywebsite/report1.htm
http://www.mywebsite/report2.htm
http://www.mywebsite/report3.htm
http://www.mywebsite/report4.htm
http://www.mywebsite/report5.htm

This is just a sample data, I have in all 50+ urls in my worksheet.
Everytime I visit the first link and check if that report is updated.
If it is updated, somewhere on the page I will get a comment "updated"
to check that, I use CTRL+F function to find the word "Updated" if it
is updated, I put a "YES" in cell D1

Similarly, I traverse through all such links to see which one is
updated. This is a very tedious manual task. Is it possible to
automate this by integrating a browser within excel or opening a
browser and communicating it through excel?

 

[NickHK]

How is VBA/Excel supposed to know if the file has been updated ?

NickHK

 

[Tim Williams]

Try this out....

Tim


'**********************************
'requires reference to Microsoft XML v2.6
Sub tester()
Dim c As Range

For Each c In ActiveSheet.Range("B2:B6")
If IsUpdated(c.Value) Then
c.Interior.Color = vbGreen
Else
c.Interior.Color = vbRed
End If
Next c

End Sub

Function IsUpdated(URL As String)

Dim msxml As New MSXML2.XMLHTTP26

'add a random querystring to the URL to defeat caching
msxml.Open "GET", URL & "?" & Rnd(), False
msxml.send
IsUpdated = (InStr(msxml.responseText, "updated") > 0)
Set msxml = Nothing

End Function
'**********************************

 

[Tim Williams]

One other thing you could do is to use a "HEAD" request to get the last
updated time for the document.

'***********************************
Option Explicit

Sub DateTester()
Debug.Print WhenUpdated("http://www.example.com/index.html")
End Sub

Function WhenUpdated(URL As String) As String
Dim msxml As New MSXML2.XMLHTTP26
msxml.Open "HEAD", URL & "?" & Rnd(), False
msxml.send
WhenUpdated = msxml.getResponseHeader("Last-Modified")
Set msxml = Nothing
End Function
'***********************************

Tim

 

[Maxi]

I get an error
Run-time error '-2147012890 (80072ee6)':
Method 'open' of object 'IXMLHTTPRequest' failed

Also I don't want to color the cell, instead I need to have a comment
in the column D which would say either a YES or a NO

Regarding your other solution, I don't want to check the updated time
in a document. I just want to search the work "Updated", every webpage
that I specified will have a list of sub reports and one common place
which will tell me if that report is updated or not.

 

[Tim Williams]

What's the value of the variable "URL" when you get the error?

To have the code enter a Yes/No instead:

Sub tester()
Dim c As Range

For Each c In ActiveSheet.Range("B2:B6")
c.offset(0,2).value=iif(IsUpdated(c.Value),"Yes","No")
Next c

Ens Sub

Tim

 

[Maxi]

What's the value of the variable "URL" when you get the error?

Value of the url? I didn't get you.

Also another problem: I was trying this code for another problem of
mine where I am finding a word "View" in a list of urls. In few urls
there will be a word "View" somewhere in the webpage and in some ther
will not.

The problem here, to view those urls/webpages, you need a username and
password so unless you supply a username and password you will not be
able to view those pages. Is there a way to supply the username and
password programatically?

Thanx

 

[Tim Williams]

Value of the url? I didn't get you.

What is the value in the cell containing the URL when you get the error ?
If you click "debug" and hover the cursor over the variable named "URL" then
what value shows?

Also another problem: I was trying this code for another problem of
mine where I am finding a word "View" in a list of urls. In few urls
there will be a word "View" somewhere in the webpage and in some ther
will not.

Without seeing the pages it's diffcult to suggest a solution.

The problem here, to view those urls/webpages, you need a username and
password so unless you supply a username and password you will not be
able to view those pages. Is there a way to supply the username and
password programatically?

How are the username/password supplied to your password-protected pages: are
you typing them into a "login" page ?

Tim

 

[Maxi]

How are the username/password supplied to your password-protected pages: are

you typing them into a "login" page ?

Tim

How are the username/password supplied to your password-protected pages: are
you typing them into a "login" page ?

Tim

I cannot give you my urls as it belongs to my company and are
confidential but I have created an example for you:
Lets say I have the three urls in b2:b4
http://www.orkut.com/Community.aspx?cmm=58860
http://www.orkut.com/Community.aspx?cmm=686497
http://www.orkut.com/Community.aspx?cmm=20318125

These are orkut communities, the first belongs to brazil, the second
one to united states and the third one to delhi.

Now lets say I only want to find communities that belong to Delhi,
then manually I would visit all three communities and do a CTRL+F to
check if they belong to Delhi.

In this case only the third community is valid and D4 should get a
value YES

Now the problem is you cannot access the above communities unless you
login to orkut in the login page http://www.orkut.com/GLogin.aspx and
supply a username and password.

This is the best example I can put up. Does this make sense?

 

[Tim]

If you search for "automate IE Excel" in google groups you will find
examples of how to automate entering information on a login page.

If the URL's you're checking already have a querystring (ie. end in
something like "...?x=y") then you should change my code

msxml.Open "GET", URL & "?" & Rnd(), False

to

msxml.Open "GET", URL & "&xxx=" & Rnd(), False

Tim

 

[Maxi]

Hi! Tim,

Tried a lot of things but unfortunately for me it is not working. I
want to ask you a question, when you open an IE browser virtually by
msxml.Open and the GET URL to visit a link, can you supply the
username and password there? and then start opening the next urls in
the same logged in window?

Because if you do that, it will NOT ask you the username and passowrd
again and again. I have tried this manually but not sure how to
program it.

 

[Tim Williams]

Maxi,

Automating Excel from VBA does not use MSXML - you're interacting directly
with IE.

You can fill in the username/pw and all following pages should be OK once
the initial login is done.

Tim

'*******************************
Sub Tester()


Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate "http://somedomain.com/start/login.html"
Do While IE.ReadyState <> 4 'READYSTATE_COMPLETE
DoEvents
Loop

'adjust this next part based on the names of the form
' and its input elements
With IE.document.formname
.UserName.Value = "theusername"
.Password.Value = "thepassword"
.submit
End With

Do While IE.ReadyState <> 4 'READYSTATE_COMPLETE
DoEvents
Loop

'now you're on the next page after the login process...
'{do stuff here...}

End Sub
'*************************************

 

[Maxi]

ok I got the logic but your isUpdated function will not work with this
code I guess and I may have to put a loop to access all the urls one
by one on this modified code.

Also, I may have to play with the syntax for IsUpdated =
(InStr(msxml.responseText, "updated") > 0) to something else. Right?

I am gonna try this today and will get back to you.

Thank you

 

[Tim Williams]

The original isupdated function could be modified by looking at

IE.document.body.innerHTML

to search for the required text.

Tim

 

[Maxi]

I modified the isUpdated function and was able to find the word using
IE.document.body.innerHTML. Thank you.

I have two more questions.

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 C1 D1
E1 and F1. I have all my links in column B1:N25. 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:
I've researched it a bit and got to know that there is something
called as GetElementByID but it needs to have and ID for the table
<table id="somename" but in my html code, there is no ID instead it
has class <table class="myPanel"

<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>

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

 

[Tim Williams]

1. If all of the pages have the same structure then you should be able to
get to the table like this

dim tt, t
set tt = IE.document.getElementsByTagName("TABLE")
set t = tt(x)

Where x is the number of the table (starts at 0, so experiment a bit to find
the table you want)

then try

debug.print t.rows(0).cells(1).innerHTML 'row 1 col 2
...etc to find the values you need
note: rows() and cells() are also zero-based

2. Try something like

dim lnk
for each lnk in IE.document.links
if lnk.innerText="take me here" then
lnk.click
exit for
end if
next lnk



Tim

 

[Maxi]

It is now going over my head.

When I put set t = tt(0) then I get <FORM action=/report.aspx
method=get><INPUT type=hidden value=box name=samp> <INPUT type=hidden
and blah blah till </FORM> as the output of debug.print

When I put set t = tt(1) or any number greater than zero, I get and
error "Run-time error '91 in the line Object variable or With block
variable not set" Debug.Print t.Rows(0).Cells(1).innerhtml

What am I doing wrong?

Here is the full code

Sub Tester()

Dim IE As Object
Dim tt, t

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate "http://www.mywebsite.com/"
Do While IE.ReadyState <> 4
DoEvents
Loop

With IE.document.all
.Email.Value = "myusername"
.Passwd.Value = "mypassword"
.btn.Click
End With

Do While IE.ReadyState <> 4
DoEvents
Loop

IE.Navigate "http://www.mywebsite.com/report1.htm"

Do While IE.ReadyState <> 4
DoEvents
Loop

Set tt = IE.document.getElementsByTagName("TABLE")
Set t = tt(0)
Debug.Print t.Rows(0).Cells(1).innerhtml

End Sub

*************

Or is it possible to find a string "Report Owner" in the page
report1.htm, identify which table the string is in, and then copy the
value given in the right hand side cell of the cell where the string
"Report Owner" is found and put that value in cell G1?

Sorry, I am asking too much but I am really confused as I am doing it
for the first time.
*************

 

[Tim Williams]

Can you post the source of the page you're working with?
If you don't want to show it here then you can send it to me at

tim j williams at gmail dot com (no spaces in the final address)

Tim

 

[Maxi]

I changed this part of the code

Set tt = IE.document.getElementsByTagName("TABLE")
Set t = tt(0)
Debug.Print t.Rows(0).Cells(1).innerhtml

with this one

Set tt = IE.document.getElementsByTagName("TABLE")

With tt(8)
Debug.Print .Rows(4).Cells(1).innerText
End With

and it worked.

Did not use "set t = tt(x)" at all.

When I looked at the error "Object variable or With block variable not
set", I thought lets see what happens if I put a With and EndWith.
Researched a bit and got it to work finally.

Thank you very much for your support.

I want to ask one more question:
Without doing the above things, is it DIRECTLY possible to find a
string "Report Owner" in the page report1.htm and send the string of
its adjacent cell to clipboard so that it can be pasted in Excel?

 

[Tim Williams]

Without doing the above things, is it DIRECTLY possible to find a

string "Report Owner" in the page report1.htm and send the string of
its adjacent cell to clipboard so that it can be pasted in Excel?

If you need a general-purpose function then maybe something like this
(untested)

example useage:
debug.print GetValue(IE.document, "Report Owner")

'********************************
'Find text in the first cell of a table row and
' return the text in the next cell to the right
Function GetValue(doc As Object, sValueType As String) As String
Dim t As Object, retVal As String
Dim r As Integer

retVal = ""
For Each t In doc.getElementsByTagName("TABLE")
For r = 0 To t.Rows.Length - 1
If t.Rows(r).Cells(0).innerText = sValueType Then
retVal = t.Rows(r).Cells(1).innerText
Exit For
End If
Next r
If retVal <> "" Then Exit For
Next t

GetValue = retVal

End Function
'********************************

Note you don't need to copy the value to the clipboard:

Activesheet.range("A1").value = GetValue(IE.document, "Report Owner")


Tim