Integrating browser with excel
 

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?

Integrating browser with excel
 

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

NickHK

"Maxi" wrote in message
oups.com...
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?

Integrating browser with excel
 

On Jun 3, 9:59 am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
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

Integrating browser with excel
 

"Maxi" wrote in message
oups.com...
On Jun 3, 9:59 am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
What's the value of the variable "URL" when you get the error?

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

Integrating browser with excel
 

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?

Integrating browser with excel
 

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" wrote in message
oups.com...
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?

Integrating browser with excel
 

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.



On Jun 4, 11:04 pm, "Tim" <tim j williams at gmail dot com wrote:
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" wrote in message

oups.com...



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 pagehttp://www.orkut.com/GLogin.aspxand
supply a username and password.

This is the best example I can put up. Does this make sense?- Hide quoted text -

- Show quoted text -

Integrating browser with excel
 

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<bTom</b</td
</tr
<tr class="row1"
<td class="rowLabel"Sent to:</td
<tdDick</td
</tr
<tr class="row0"
<td class="rowLabel"Verfied by:</td
<tdHarry </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?

Integrating browser with excel
 

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" wrote in message
oups.com...
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<bTom</b</td
</tr
<tr class="row1"
<td class="rowLabel"Sent to:</td
<tdDick</td
</tr
<tr class="row0"
<td class="rowLabel"Verfied by:</td
<tdHarry </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?

Integrating browser with excel
 

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

On Jun 16, 7:03 am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
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

Integrating browser with excel
 

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" wrote in message
ups.com...
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.
*************

On Jun 16, 7:03 am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
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

Integrating browser with excel
 

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?


On Jun 16, 9:39 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
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" wrote in message

ups.com...



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

On Jun 16, 7:03 am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
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- Hide quoted text -

- Show quoted text -