Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA to read HTML; Search for TDelement
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 56
Default VBA to read HTML; Search for TDelement
I have code which goes to a internal corporate site, takes names from
ColumnZ in my spreadsheet, and pops them into a search box on a
company web site, searches, and returns, the results in another web
page. All of this works fine. Now, I’m trying to look through the TD
Elements on the search page, find something called SOEID, and import
the inner text (from that) into my spreadsheet, into ColumnAA. For
now, I’m testing to see if I can get the results into a MsgBox. If I
get that I can get the rest working. I noticed that TDelement.bgColor
= "#d9d9d9" will give me the SOEID, but there are a few
TDelement.bgColor = "#d9d9d9" in the page, so I have to drill down
another level…



Directly under TDelement.bgColor = "#d9d9d9 is <span
class="fineleft". This too is not unique, but under this is SOEID
and this is unique! So I want to search for that combination, and
import the inner text of the SOEID.



This is how it looks in the IE Developer Tool:

<td width="15%" bgColor="#d9d9d9"

<span class="fineleft"

Text – SOEID

<td width="15%"

<span class="fineattr"

Text - GT89111



I want to import that ‘GT89111’



Set TDelements = HTMLdoc.getElementsByTagName("TD")

For Each TDelement In TDelements

If TDelement.bgColor = "#d9d9d9" And . . . < -- problem is here . . .
Then

MsgBox (TDelement.innerText)

‘sa = TDelement.innerText

‘ With Sheets("List of FAs")

‘ .Range("AA" & CStr(RowCount)).Value =
sa

‘ End With

End If

Next

RowCount = RowCount + 1



How can I reference this:

<span class="fineleft"





Or, how can I reference this:

objCell.innerText

itm.innerText



So, any ideas on how to do this? I’m sure it is possible. I’m just
at a loss now as to how to actually do it.



Thanks everyone!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
Junior Member
  
Posts: 11
Default VBA to read HTML; Search for TDelement
ryguy7272 wrote:
I have code which goes to a internal corporate site, takes names from
ColumnZ in my spreadsheet, and pops them into a search box on a
company web site, searches, and returns, the results in another web
page. All of this works fine. Now, I’m trying to look through the TD
Elements on the search page, find something called SOEID, and import
the inner text (from that) into my spreadsheet, into ColumnAA. For
now, I’m testing to see if I can get the results into a MsgBox. If I
get that I can get the rest working. I noticed that TDelement.bgColor
= "#d9d9d9" will give me the SOEID, but there are a few
TDelement.bgColor = "#d9d9d9" in the page, so I have to drill down
another level…
Directly under TDelement.bgColor = "#d9d9d9 is <span
class="fineleft". This too is not unique, but under this is SOEID
and this is unique! So I want to search for that combination, and
import the inner text of the SOEID.
This is how it looks in the IE Developer Tool:
<td width="15%" bgColor="#d9d9d9"
<span class="fineleft"
Text – SOEID
<td width="15%"
<span class="fineattr"
Text - GT89111
I want to import that ‘GT89111’
Set TDelements = HTMLdoc.getElementsByTagName("TD")
For Each TDelement In TDelements
If TDelement.bgColor = "#d9d9d9" And . . . < -- problem is here . . .
Then
MsgBox (TDelement.innerText)
‘sa = TDelement.innerText
‘ With Sheets("List of FAs")
‘ .Range("AA" & CStr(RowCount)).Value =
sa
‘ End With
End If
Next
RowCount = RowCount + 1
How can I reference this:
<span class="fineleft"

Or, how can I reference this:
objCell.innerText
itm.innerText
So, any ideas on how to do this? I’m sure it is possible. I’m just
at a loss now as to how to actually do it.
Thanks everyone!

Will this approach work for you?

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd"
<html
<head
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"
<titleUntitled</title
<style type='text/css'
body{
font-family: "courier new";
}
td{
border: 1px solid green;
}
span{
border: 1px solid green;
padding: 2px 5px 2px 5px;
margin: 5px;
}
</style
<script type="text/javascript"
function doit(){
var coll_td=document.getElementsByTagName("td");
var s1;
for (n=0; n<coll_td.length; n++){
if (coll_td[n].className="fineleft"){
s1=coll_td[n].innerText;
if (s1.indexOf("SOEID") != -1) {
var coll_span= coll_td[n].getElementsByTagName("span");
alert ("span contents\n" + coll_span[0].innerText + "\n" +
coll_span[1].innerText);
break;
}
}
}
}
</script
</head
<body
<table
<tr<td<span class="fineleft"zzzzz</span<span
class="fineattr"11111</span</td</tr
<tr<td<span class="fineleft"aaaaa</span<span
class="fineattr"22222</span</td</tr
<tr<td<span class="fineleft"bbbbb</span<span
class="fineattr"33333</span</td</tr
<tr<td<span class="fineleft"SOEID</span<span
class="fineattr"44444</span</td</tr
<tr<td<span class="fineleft"ccccc</span<span
class="fineattr"55555</span</td</tr
</table
<p<input type="button" onclick="doit()" value="doit"
</body
</html
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 56
Default VBA to read HTML; Search for TDelement
On Aug 15, 2:19*am, mscir wrote:
ryguy7272 wrote:
I have code which goes to a internal corporate site, takes names from
ColumnZ in my spreadsheet, and pops them into a search box on a
company web site, searches, and returns, the results in another web
page. *All of this works fine. *Now, I’m trying to look through the TD
Elements on the search page, find something called SOEID, and import
the inner text (from that) into my spreadsheet, into ColumnAA. *For
now, I’m testing to see if I can get the results into a MsgBox. *If I
get that I can get the rest working. *I noticed that TDelement.bgColor
= "#d9d9d9" will give me the SOEID, but there are a few
TDelement.bgColor = "#d9d9d9" in the page, so I have to drill down
another level…
Directly under TDelement.bgColor = "#d9d9d9 is <span
class="fineleft". *This too is not unique, but under this is SOEID
and this is unique! *So I want to search for that combination, and
import the inner text of the SOEID.
This is how it looks in the IE Developer Tool:
<td width="15%" bgColor="#d9d9d9"
<span class="fineleft"
Text – SOEID
<td width="15%"
<span class="fineattr"
Text - *GT89111
I want to import that ‘GT89111’
Set TDelements = HTMLdoc.getElementsByTagName("TD")
For Each TDelement In TDelements
If TDelement.bgColor = "#d9d9d9" And . . . < -- problem is here . . .
Then
* * * * * * * * * * * * MsgBox (TDelement.innerText)
* * * * * * * * * * * * ‘sa = TDelement.innerText
* * * * * * * * * * * * ‘ * *With Sheets("List of FAs")
* * * * * * * * * * * * ‘ * * * *.Range("AA" & CStr(RowCount)).Value =
sa
* * * * * * * * * * * * ‘ * *End With
End If
Next
RowCount = RowCount + 1
How can I reference this:
<span class="fineleft"

Or, how can I reference this:
objCell.innerText
itm.innerText
So, any ideas on how to do this? *I’m sure it is possible. *I’m just
at a loss now as to how to actually do it.
Thanks everyone!

Will this approach work for you?

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd"
<html
<head
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"
<titleUntitled</title
<style type='text/css'
body{
* *font-family: "courier new";}

td{
* *border: 1px solid green;}

span{
* *border: 1px solid green;
* *padding: 2px 5px 2px 5px;
* *margin: 5px;}

</style
<script type="text/javascript"
function doit(){
* *var coll_td=document.getElementsByTagName("td");
* *var s1;
* *for (n=0; n<coll_td.length; n++){
* * *if (coll_td[n].className="fineleft"){
* * * *s1=coll_td[n].innerText;
* * * *if (s1.indexOf("SOEID") != -1) {
* * * * *var coll_span= coll_td[n].getElementsByTagName("span");
* * * * *alert ("span contents\n" + coll_span[0].innerText + "\n" +
coll_span[1].innerText);
* * * * *break;
* * * *}
* * *}
* *}}

</script
</head
<body
<table
<tr<td<span class="fineleft"zzzzz</span<span
class="fineattr"11111</span</td</tr
<tr<td<span class="fineleft"aaaaa</span<span
class="fineattr"22222</span</td</tr
<tr<td<span class="fineleft"bbbbb</span<span
class="fineattr"33333</span</td</tr
<tr<td<span class="fineleft"SOEID</span<span
class="fineattr"44444</span</td</tr
<tr<td<span class="fineleft"ccccc</span<span
class="fineattr"55555</span</td</tr
</table
<p<input type="button" onclick="doit()" value="doit"
</body
</html


That looks like C#. I'm using VBA. I'm just now learning how to use
C#. I'm not sure how to use the code you posted though.

Can someone help get me going in the right direction with a VBA
solution?

Thanks!!
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
ron ron is offline
external usenet poster
  
Posts: 118
Default VBA to read HTML; Search for TDelement
On Aug 14, 12:22*pm, ryguy7272 wrote:
I have code which goes to a internal corporate site, takes names from
ColumnZ in my spreadsheet, and pops them into a search box on a
company web site, searches, and returns, the results in another web
page. *All of this works fine. *Now, I’m trying to look through the TD
Elements on the search page, find something called SOEID, and import
the inner text (from that) into my spreadsheet, into ColumnAA. *For
now, I’m testing to see if I can get the results into a MsgBox. *If I
get that I can get the rest working. *I noticed that TDelement.bgColor
= "#d9d9d9" will give me the SOEID, but there are a few
TDelement.bgColor = "#d9d9d9" in the page, so I have to drill down
another level…

Directly under TDelement.bgColor = "#d9d9d9 is <span
class="fineleft". *This too is not unique, but under this is SOEID
and this is unique! *So I want to search for that combination, and
import the inner text of the SOEID.

This is how it looks in the IE Developer Tool:

<td width="15%" bgColor="#d9d9d9"

<span class="fineleft"

Text – SOEID

<td width="15%"

<span class="fineattr"

Text - *GT89111

I want to import that ‘GT89111’

Set TDelements = HTMLdoc.getElementsByTagName("TD")

For Each TDelement In TDelements

If TDelement.bgColor = "#d9d9d9" And . . . < -- problem is here . . .
Then

* * * * * * * * * * * * MsgBox (TDelement.innerText)

* * * * * * * * * * * * ‘sa = TDelement.innerText

* * * * * * * * * * * * ‘ * *With Sheets("List of FAs")

* * * * * * * * * * * * ‘ * * * *.Range("AA" & CStr(RowCount)).Value =
sa

* * * * * * * * * * * * ‘ * *End With

End If

Next

RowCount = RowCount + 1

How can I reference this:

<span class="fineleft"

Or, how can I reference this:

objCell.innerText

itm.innerText

So, any ideas on how to do this? *I’m sure it is possible. *I’m just
at a loss now as to how to actually do it.

Thanks everyone!

Whats the source code look like around SOEID?..Ron
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default VBA to read HTML; Search for TDelement
On 8/15/2010 12:19 PM, ryguy7272 wrote:
On Aug 15, 2:19 am, wrote:
ryguy7272 wrote:
I have code which goes to a internal corporate site, takes names from
ColumnZ in my spreadsheet, and pops them into a search box on a
company web site, searches, and returns, the results in another web
page. All of this works fine. Now, I’m trying to look through the TD
Elements on the search page, find something called SOEID, and import
the inner text (from that) into my spreadsheet, into ColumnAA. For
now, I’m testing to see if I can get the results into a MsgBox. If I
get that I can get the rest working. I noticed that TDelement.bgColor
= "#d9d9d9" will give me the SOEID, but there are a few
TDelement.bgColor = "#d9d9d9" in the page, so I have to drill down
another level…
Directly under TDelement.bgColor = "#d9d9d9 is<span
class="fineleft". This too is not unique, but under this is SOEID
and this is unique! So I want to search for that combination, and
import the inner text of the SOEID.
This is how it looks in the IE Developer Tool:
<td width="15%" bgColor="#d9d9d9"
<span class="fineleft"
Text – SOEID
<td width="15%"
<span class="fineattr"
Text - GT89111
I want to import that ‘GT89111’
Set TDelements = HTMLdoc.getElementsByTagName("TD")
For Each TDelement In TDelements
If TDelement.bgColor = "#d9d9d9" And . . .< -- problem is here . . .
Then
MsgBox (TDelement.innerText)
‘sa = TDelement.innerText
‘ With Sheets("List of FAs")
‘ .Range("AA"& CStr(RowCount)).Value =
sa
‘ End With
End If
Next
RowCount = RowCount + 1
How can I reference this:
<span class="fineleft"

Or, how can I reference this:
objCell.innerText
itm.innerText
So, any ideas on how to do this? I’m sure it is possible. I’m just
at a loss now as to how to actually do it.
Thanks everyone!

Will this approach work for you?

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd"
<html
<head
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"
<titleUntitled</title
<style type='text/css'
body{
font-family: "courier new";}

td{
border: 1px solid green;}

span{
border: 1px solid green;
padding: 2px 5px 2px 5px;
margin: 5px;}

</style
<script type="text/javascript"
function doit(){
var coll_td=document.getElementsByTagName("td");
var s1;
for (n=0; n<coll_td.length; n++){
if (coll_td[n].className="fineleft"){
s1=coll_td[n].innerText;
if (s1.indexOf("SOEID") != -1) {
var coll_span= coll_td[n].getElementsByTagName("span");
alert ("span contents\n" + coll_span[0].innerText + "\n" +
coll_span[1].innerText);
break;
}
}
}}

</script
</head
<body
<table
<tr<td<span class="fineleft"zzzzz</span<span
class="fineattr"11111</span</td</tr
<tr<td<span class="fineleft"aaaaa</span<span
class="fineattr"22222</span</td</tr
<tr<td<span class="fineleft"bbbbb</span<span
class="fineattr"33333</span</td</tr
<tr<td<span class="fineleft"SOEID</span<span
class="fineattr"44444</span</td</tr
<tr<td<span class="fineleft"ccccc</span<span
class="fineattr"55555</span</td</tr
</table
<p<input type="button" onclick="doit()" value="doit"
</body
</html


That looks like C#. I'm using VBA. I'm just now learning how to use
C#. I'm not sure how to use the code you posted though.

Can someone help get me going in the right direction with a VBA
solution?

Thanks!!

It's javascript, you would make a few minor changes to use the code in
Excel, the main thing is you can get the innertext of the spans inside
the div with this approach.

These are the changes from javascript to VBA, try it in your sheet and
if it doesn't work post your code he

var
dim

for (n=0; n<coll_td.length; n++){
for n = 0 to coll_td.length

if (s1.indexOf("SOEID") != -1) {
if instr(s1,"SOEID")0 then

Mike
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Search HTML [email protected] Excel Programming 1 October 30th 08 02:09 AM
Search HTML Source code. [email protected] Excel Programming 0 October 29th 08 01:15 PM
Search and replace chunks of html code method373 Excel Discussion (Misc queries) 0 March 27th 06 03:46 PM
How to read HTML [email protected] Excel Programming 2 January 2nd 06 05:33 PM
REQ: Simplest way to parse (read) HTML formatted data in via Excel VBA (or VB6) Steve[_29_] Excel Programming 3 August 25th 03 10:43 PM


All times are GMT +1. The time now is 11:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement