Extracting Web Data

John Wilson · Jun 6, 2008

Can anyone tell me how to get what's on this web page into Excel (data or
source)?
http://nylottery.org/ny/nyStore/cgi...Week_Cat_337697_SubCat_337698_NavRoot_302.htm

Workbooks.Open URL and Workbooks.OpenText URL don't work.
Used to be able to do it with a web query but that recently stopped working
(probably due to a change they recently made to the page formatting).

Thank you.
John

Tim Williams · Jun 7, 2008

This worked for me...

Tim

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

Sub TestExtract()

Dim IE As Object, allCells, r, c, x, rw As Long

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
'next line has no break !
IE.Navigate
"http://nylottery.org/ny/nyStore/cgi...Week_Cat_337697_SubCat_337698_NavRoot_302.htm"
Do While IE.ReadyState <> 4 'READYSTATE_COMPLETE
DoEvents
Loop

Set r = Nothing
Set allCells = IE.document.getElementsByTagName("td")

For Each c In allCells
If c.innerText = "Draw #" Then
Debug.Print "found"
Set r = c.ParentElement.nextSibling 'first data row
Exit For
End If
Next c

If Not r Is Nothing Then
rw = 2
Do
For x = 1 To r.Cells.Length
Sheet3.Cells(rw, x).Value = r.Cells(x - 1).innerText
Next x
rw = rw + 1
Set r = r.nextSibling
Loop While Not r Is Nothing

End If

End Sub

John Wilson · Jun 9, 2008

Tim,

That worked perfectly!!!!!
I hadn't mentioned in my post that I only wanted the table
but you went the extra mile and even parsed that out for for me.

Thank you.
John

Tim Williams · Jun 9, 2008

No problem - glad it worked for you.

Tim

ian bartlett · Jun 10, 2008

Tim

How would one modify your excellent code to this web site
http://www.nhl.com/nhlstats/app?ser...83ALLSASAll&viewName=summary&sort=points&pg=2

what I want to do is to pull and data queries Points&pg=1,points&pg=3 etc
thru to 9 and dump them all into sheet1

Thanks for any consideration.

Bart

Tim Williams · Jun 11, 2008

Ian,

Try this - format the cells as text first, or figure out the formatting...

Anyone else with other pages - you're on your own.

Tim

'***********************************
Sub TestExtract()

Dim IE As Object, allCells, r, c, x, rw As Long
Dim row, numPage
rw = 2

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

For numPage = 1 To 9

IE.Navigate "http://www.nhl.com/nhlstats/app?service=page&page=" & _
"Stats&fetchKey=20083ALLSASAll&viewName=summary&sort=points&pg="
& _
numPage
Do While IE.ReadyState <> 4 'READYSTATE_COMPLETE
DoEvents
Loop

Set r = Nothing
Set allCells = IE.document.getElementsByTagName("td")

For Each c In allCells
If c.innerText Like "Player*" Then
'c.Style.Border = "1px solid #FF0000"
Set r = c.ParentElement.ParentElement.ParentElement 'containing
table
'c.Style.Border = "1px solid #0000FF"
Exit For
End If
Next c

If Not r Is Nothing Then
For Each row In r.Rows
For x = 1 To row.Cells.Length - 1
Sheet3.Cells(rw, x).Value = row.Cells(x - 1).innerText
Next x
rw = rw + 1
Next row
End If

Next numPage

End Sub

ian bartlett · Jun 11, 2008

Tim

This does exactly what I wanted, thankyou.

I really appreciate the time you and other excel guru's donate.

Bart

John Wilson · Jun 11, 2008

Tim,

Again, thank you for your help with my original dilema.
The coding that you gave does work great.
Caveat for anyone that happens on this thread is that it won't work in
Vista unless you have "User Account Control" disabled.

I had hoped that I could modify your code to get another page with
what "looks like" but obviously isn't the same type of layout.
I was even trying to get everything on the page and parse it out
later.

I guess that I'm not understanding the "GetElementsBy....."
in conjunction with the "If c.innerText ......" because I can't
seem to "find" anything on the page.

I have been learning a lot browsing the web and attempting to
decipher your code (I even know what "readyState" is now).

Anyway....I know you said "Anyone else...you're on your own" but
could you take a quick look at this page and tell me what I'm
missing to try to get the data (source) from it)?

http://www.nylottery.org/ny/nyStore...nth_2.htm?Year=2008&Month=2&Date=5&drawno=-1&

Thanks,
John Wilson

Tim Williams · Jun 12, 2008

OK, OK

Tim

'*********************************************************
Sub TestExtract()

Dim IE As Object, allCells, r, c, t, x, rw, y

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
'next line has no break !
IE.Navigate "http://www.nylottery.org/ny/nyStore/cgi-bin/" & _
"QuickDrawPastResults_Cat_337697_SubCat_337698" & _
"_NavRoot_302_Year_2008_Month_2.htm?Year=2008&" & _
"Month=2&Date=5&drawno=-1&"

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

rw = 2
Set t = Nothing
Set allCells = IE.document.getElementsByTagName("td")

For Each c In allCells
If c.innerText = "Draw #" Then
Debug.Print "found cell"
Set t = c.parentElement.parentElement.parentElement 'table
Exit For
End If
Next c

If Not t Is Nothing Then

For y = 2 To t.Rows.Length - 1
For x = 1 To t.Rows(y).Cells.Length
Sheet1.Cells(rw, x).Value = t.Rows(y).Cells(x - 1).innerText
Next x
rw = rw + 1
Next y
End If

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

John Wilson · Jun 13, 2008

Tim,

Thanks again.

Set t = c.parentElement.parentElement.parentElement????
I would've stumbled upon that myself (in a few years (maybe)).

When I clean up my coding I'll send you a copy of the workbook.

Thanks,
John

OK, OK

Tim

'*********************************************************
Sub TestExtract()

Dim IE As Object, allCells, r, c, t, x, rw, y

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
'next line has no break !
IE.Navigate "http://www.nylottery.org/ny/nyStore/cgi-bin/" & _
"QuickDrawPastResults_Cat_337697_SubCat_337698" & _
"_NavRoot_302_Year_2008_Month_2.htm?Year=2008&" & _
"Month=2&Date=5&drawno=-1&"

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

rw = 2
Set t = Nothing
Set allCells = IE.document.getElementsByTagName("td")

For Each c In allCells
If c.innerText = "Draw #" Then
Debug.Print "found cell"
Set t = c.parentElement.parentElement.parentElement 'table
Exit For
End If
Next c

If Not t Is Nothing Then

For y = 2 To t.Rows.Length - 1
For x = 1 To t.Rows(y).Cells.Length
Sheet1.Cells(rw, x).Value = t.Rows(y).Cells(x - 1).innerText
Next x
rw = rw + 1
Next y
End If

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

El_perdido · Sep 6, 2008

Hi guys.

Hope you will read this new reply.

Im extracting data from web based application. My code works to check if i'm
already logged on the application, if no then login, and then do the web
search; So i can get the results i need Internet Explorer. But i must copy
and paste the information I need manually.

I want to do it automatically (doing visible= off for the IE window). I have
tried on different ways but i can't. Can somebody give me some hint to do it?

Greetings.

My webpage code is:

'Start of code*************************************************
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML dir="LTR" lang="en">
<HEAD>
<TITLE>part</TITLE>
<STYLE TYPE="text/css">style information
</STYLE>
<base href="http://URL:PORT/portal/pls/portal/">
<META name="title" content="part">
<META name="description" content="part">
<META name="keywords" content="">
<META name="author" content="AEL">
</HEAD>
<BODY style="margin:0px" class="Bodyid1siteid33" >
<NOSCRIPT></NOSCRIPT>
<iframe id="portalIFrame" title="" frameborder="0" width="0" height="0"
src="/images/pobtrans.gif"></iframe><SCRIPT TYPE="text/javascript"
SRC="http://URL:PORT/portal/PORTAL.wwsbr_javascript.page_js?p_language=us&p_version=10.1.4.0.0.594">

</SCRIPT>
<TABLE WIDTH="100%" id="rg1371" border="0" cellpadding="0" cellspacing="0"
summary=""><TR ALIGN="LEFT">
<TD vAlign="top" style="padding:0px 0px 0px 0px;" width="100%"><DIV
id="p33_5173_33_5172_5172"><TABLE BORDER="0" WIDTH="100%" CELLPADDING="0"
CELLSPACING="0" class="RegionNoBorder">
<TR><TD class="RegionHeaderColor" WIDTH="100%"><DIV id="pcnt"><STYLE
TYPE="text/css"></STYLE>
<TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="100%"
class="PortletHeaderColor">
<TR class="PortletHeaderColor">
<TD ALIGN="LEFT" NOWRAP class="LeftCurve"><IMG SRC="/images/aa.gif"
border="0" width="10" ALT=""></TD>
<TD ALIGN="LEFT" NOWRAP class="PortletHeaderColor" width="100%">
<FONT class="PortletHeaderText">part</FONT>
</TD>
<TD ALIGN="right" NOWRAP class="PortletHeaderColor">

<A HREF="http://URL:PORT/portal/pls/portal/link"><FONT
class="PortletHeaderLink">Link1</FONT></A>
<A HREF="http://URL:PORT/portal/pls/portal/Link"><FONT
class="PortletHeaderLink">Link2</FONT></A>
</TD>
<TD ALIGN="right" class="RightCurve"><IMG SRC="/images/pobtrans.gif"
border="0" width="10" ALT=""></TD>
</TR>
</TABLE>
<TABLE BORDER="0" WIDTH="100%" CELLPADDING="0" CELLSPACING="0"
class="RegionNoBorder">
<TR><TD class="RegionHeaderColor" WIDTH="100%"><DIV
id="pcnt33_5173_33_5172_5172"><TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0
BGCOLOR=#EFF6FF WIDTH=100%>
<TR><TD>
<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 WIDTH=100%>
<TR class="PortletHeaderColor"><TD ALIGN=LEFT NOWRAP>
<TABLE BORDER=0 CELLPADDING=1 CELLSPACING=0>
<TR><TD>
<FORM METHOD="POST" ACTION="http://URL:PORT/portal/Dir">
<TD>
<SELECT NAME="INS" SIZE=1>
<OPTION VALUE="ERPL"
SELECTED

sdfg <OPTION VALUE="ERPT"
jety <OPTION VALUE="ERPD"
rsedbnsf <OPTION VALUE="ERP5"
sdga

</SELECT>
</TD><TD>

erabdsf

<OPTION VALUE="101"
SELECTED

23ersd <OPTION VALUE="102"
cu35e <OPTION VALUE="161"
67j <OPTION VALUE="181"
h245 <OPTION VALUE="241"
h45y <OPTION VALUE="281"
olk67 <OPTION VALUE="201"
fgsdf4 <OPTION VALUE="221"
ewyer <OPTION VALUE="222"
234 <OPTION VALUE="282"
m565 <OPTION VALUE="283"
23sadg <OPTION VALUE="261"
85y <OPTION VALUE="262"
h34ef

</SELECT><TD>
<INPUT TYPE="SUBMIT" VALUE="Re-configure">
</TD></FORM></TR></TABLE>
</TD><TD ALIGN=CENTER NOWRAP WIDTH="70">
<FORM METHOD="POST" ACTION="http://URL:PORT/portal/SEARCH" target="_parent">
<INPUT TYPE="SUBMIT" VALUE="Home">
</TD></FORM>
<TD ALIGN=CENTER NOWRAP CLASS=LOGIN>
<FONT COLOR=WHITE>User : </FONT>
<A HREF="http://URL:7777/signout"><FONT COLOR=WHITE>Logout</A>
</TD>
<TD ALIGN=LEFT NOWRAP WIDTH="90%">
</TD>
<TD ALIGN=RIGHT NOWRAP CLASS=LOGIN>
<FONT COLOR=#CCCCFF><B>APP</B>
</TD>
</TR></TABLE>
</TD></TR>
<TR><TD>
<TABLE BORDER=0 BGCOLOR=#EFF6FF>
<TR CLASS="FONT">
<FORM METHOD="POST" ACTION="http://URL:PORT/portal/searchresult">
<TD ALIGN=CENTER>A<TD ALIGN=CENTER>B</TD>
<TD COLSPAN=2 ALIGN=CENTER NOWRAP>C
</TD><TD ALIGN=CENTER>D</TD>
<TD ALIGN=CENTER>E<TD ALIGN=CENTER>gsadfh<TD ALIGN=CENTER>F<TD
ALIGN=CENTER>G</TD>
<TD ALIGN=CENTER COLSPAN=2>H</TD>
<TR CLASS="FONT">
<TD>
<INPUT TYPE="TEXT" NAME="qew" SIZE=14 MAXSIZE=16
VALUE="DAD">
<TD>
<INPUT TYPE="TEXT" NAME="sad" SIZE=14 MAXSIZE=16
VALUE="ALL">
<TD COLSPAN=2>
<INPUT TYPE="TEXT" NAME="hsrde" SIZE=14 MAXSIZE=16
VALUE="ALL">
<TD>
<INPUT TYPE="TEXT" NAME="rew" SIZE=16 MAXSIZE=32
VALUE="NOT">
<TD>
<INPUT TYPE="TEXT" NAME="dsdfs" SIZE=10 MAXSIZE=16
VALUE="ALL">
<TD>
<INPUT TYPE="TEXT" NAME="gsda" SIZE=10 MAXSIZE=16
VALUE="ALL">
<TD>
<INPUT TYPE="TEXT" NAME="hje5t" SIZE=10 MAXSIZE=16
VALUE="ALL">
<TD>
<INPUT TYPE="TEXT" NAME="kute" SIZE=10 MAXSIZE=16
VALUE="">
<TD>
<INPUT TYPE="CHECKBOX" NAME="erteq"
VALUE="OFF">
<TD>
<SELECT NAME=",krsd" SIZE=1>
<OPTION VALUE=1>1
<OPTION VALUE=2
SELECTED

2 <OPTION VALUE=3
3 <OPTION VALUE=4
4

</SELECT>
</TD>
</TR>
<TR CLASS="FONT"><TD ALIGN=CENTER>
<SELECT NAME="we5tuy" SIZE=1>

sas <OPTION VALUE=3
ktgh <OPTION VALUE=4
34rtww <OPTION VALUE=5
234bh

</SELECT>
<TD ALIGN=CENTER><TD COLSPAN=2 ALIGN=CENTER>IT</TD>
<TD COLSPAN=3 ROWSPAN=2 ALIGN=LEFT>
<TABLE WIDTH="100%"><TR CLASS="FONT"><TD ALIGN=CENTER NOWRAP>W</TD>
<TD ALIGN=CENTER>Q</TD><TD ALIGN=CENTER NOWRAP>
k'kas</TD><TD ALIGN=CENTER NOWRAP>R</TD></TR>
<TR><TD>
<INPUT TYPE="TEXT" NAME="STAT" SIZE=8 MAXSIZE=16
VALUE=""></TD>
<TD><INPUT TYPE="TEXT" NAME="VEND" SIZE=6 MAXSIZE=16
VALUE=""></TD>
<TD>
<SELECT NAME="T" SIZE=1>

n <OPTION VALUE="2"
m

</TD>
<TD>
<INPUT TYPE="TEXT" NAME="kjer" SIZE=4 MAXSIZE=16
VALUE="">
</TD></TR></TABLE>
</TD>
<TD>
<INPUT TYPE="CHECKBOX" NAME="BB"
VALUE="OFF">
A M.</TD>
<TD>
<INPUT TYPE="CHECKBOX" NAME="VV"
VALUE="OFF">
M</TD>
<TD COLSPAN=2>
<INPUT TYPE="TEXT" NAME="VALID" SIZE=10 MAXSIZE=10
VALUE="fecha">
</TR>
</TR>
<TR CLASS="FONT"><TD>
<SELECT NAME="LOOKING" SIZE=1>

fault <OPTION VALUE=2
WP

<OPTION VALUE=3
SELECTED

blue

<INPUT TYPE="HIDDEN" NAME="_STR" VALUE="1">
<INPUT TYPE="HIDDEN" NAME="HYP" VALUE="1">
<INPUT TYPE="HIDDEN" NAME="ADISP" VALUE="0">
<INPUT TYPE="HIDDEN" NAME="COLOR" VALUE="1">
<TD COLSPAN=3 ALIGN=RIGHT>
<SELECT NAME="IT_TYPE" SIZE=1>
<OPTION VALUE="ALL">ALL

<TD NOWRAP>
<INPUT TYPE="CHECKBOX" NAME="PURCH"
VALUE="OFF">
Purch
</TD>
<TD COLSPAN=3 NOWRAP>
<INPUT TYPE="CHECKBOX" NAME="LI"
VALUE="OFF">
Ord
</TD></TR>
<TR>
<TD COLSPAN=5 NOWRAP style="font-size: 16px;">
<A HREF="http://URL:PORT/portal/6"><FONT COLOR=#4000C0
FACE="Arial"><B><I>6 </FONT></A>
<A HREF="http://URL:PORT/E"><FONT COLOR=#008080
FACE="Arial"><B><I>E </FONT></A>
<A HREF="http://URL:PORT/ma"><FONT COLOR=#800080
FACE="Arial"><B><I>Ma </FONT></A>
<A HREF="http://URL:PORT/aa"><FONT COLOR=#0080C0
FACE="Arial"><B><I>aa </FONT></A>
<A HREF="http://URL:PORT/Co"><FONT COLOR=#E08040
FACE="Arial"><B><I>Co</FONT></A>
<TD CLASS="FONT" COLSPAN=2 ALIGN=CENTER NOWRAP>
<INPUT TYPE="SUBMIT" VALUE="Search">
<INPUT TYPE="CHECKBOX" NAME="XLS" VALUE="ON">xls
<INPUT TYPE="CHECKBOX" NAME="FONTOFF"
VALUE="OFF">
Off
</TD>
<TD CLASS="FONT" NOWRAP>
<INPUT TYPE="CHECKBOX" NAME="34nrs"
VALUE="OFF">
Ass
</TD>
<TD CLASS="FONT" COLSPAN=3 NOWRAP>
<INPUT TYPE="CHECKBOX" NAME="BLANK"
VALUE="OFF">
Blank
<INPUT TYPE="CHECKBOX" NAME="IMPLONL"
VALUE="OFF">
ImpOnly
</TD>
</TR>
</FORM>
</TABLE>
<SCRIPT DEFER LANGUAGE="JavaScript">document.forms[2].ITEM.focus();</SCRIPT>
</TD><TR>
</TABLE>
<TABLE BGCOLOR=#000066 CELLPADDING=0 CELLSPACING=0 BORDER=1><TR><TD><TABLE
BORDER=0 CELLPADDING=3 CELLSPACING=1>
<TR class="PortletHeaderColor"><TH class="PortletHeaderText"><A
NAME="BEG"></A>
Level</TH><TH class="PortletHeaderText">
DAD
</TH><TH class="PortletHeaderText">
REF</TH><TH class="PortletHeaderText">
PN
</TH><TH class="PortletHeaderText">
Qty</TH><TH class="PortletHeaderText">
E</TH><TH class="PortletHeaderText">
Sinv.</TH><TH class="PortletHeaderText">
Status</TH><TH class="PortletHeaderText">Type
</TH></TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>1</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>TNP</FONT>
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/t2k"><FONT COLOR=BLACK>T2K</FONT></A>
<TD NOWRAP> .0001
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>02</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#8888EE NOWRAP>ij0d</TD>
</TR>
<TR class="font" BGCOLOR=#E0E0E0>
<TD NOWRAP>1</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>TNA</FONT>
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/tna"><FONT COLOR=BLACK>hu0as0021</FONT></A>
<TD NOWRAP> .0001
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>02</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#8888EE NOWRAP>TNS</TD>
</TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>1</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>TPA</FONT>
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/tpa"><FONT COLOR=BLACK>tpa</FONT></A>
<TD NOWRAP> .0001
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>02</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#8888EE NOWRAP>tpa</TD>
</TR>
<TR class="font" BGCOLOR=#E0E0E0>
<TD NOWRAP>1</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>NPA</FONT>
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/t8e"><FONT COLOR=BLACK>TE8</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>02</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#8888EE NOWRAP>8te</TD>
</TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>1</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>T83</FONT>
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/t33"><FONT COLOR=BLACK>23sd4483</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP></TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#AAAAAA NOWRAP>t33</TD>
</TR>
<TR class="font" BGCOLOR=#E0E0E0>
<TD NOWRAP>2</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>T83</FONT>
</TD>
<TD NOWRAP>
1001
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/rem"><FONT COLOR=BLACK>rem</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>02</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#AAAAFF NOWRAP>rem</TD>
</TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>2</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>A44</FONT>
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/na4"><FONT COLOR=BLACK>NA4</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP></TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#EEE088 NOWRAP>4na</TD>
</TR>
<TR class="font" BGCOLOR=#E0E0E0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>483</FONT>
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/900"><FONT COLOR=BLACK>900</FONT></A>
<TD NOWRAP> 3
</TD>
<TD NOWRAP>
aaa
</TD>
<TD NOWRAP>55</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#8888EE NOWRAP>ca</TD>
</TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>NA4</FONT>
</TD>
<TD NOWRAP>
1008
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/a4"><FONT COLOR=BLACK>EJ2</FONT></A>
<TD NOWRAP> 2
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>55</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#AAAAFF NOWRAP>e22</TD>
</TR>
<TR class="font" BGCOLOR=#E0E0E0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>T8A</FONT>
</TD>
<TD NOWRAP>
1010
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/r2v"><FONT COLOR=BLACK>r2v</FONT></A>
<TD NOWRAP> 2
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>55</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>es</TD><TD BGCOLOR=#AAAAFF NOWRAP>se</TD>
</TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>N48</FONT>
</TD>
<TD NOWRAP>
1007
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/n48"><FONT COLOR=BLACK>84n</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>55</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>555</TD><TD BGCOLOR=#AAAAFF NOWRAP>aP</TD>
</TR>
<TR class="font" BGCOLOR=#E0E0E0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>3pt</FONT>
</TD>
<TD NOWRAP>
1006
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/f94"><FONT COLOR=BLACK>49f</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>a</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ie</TD><TD BGCOLOR=#AAAAFF NOWRAP>Pie</TD>
</TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>3ve</FONT>
</TD>
<TD NOWRAP>
1
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/20k"><FONT COLOR=BLACK>20k</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>33</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ce</TD><TD BGCOLOR=#AAAAFF NOWRAP>55</TD>
</TR>
<TR class="font" BGCOLOR=#E0E0E0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>a48</FONT>
</TD>
<TD NOWRAP>
1001
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/n1r"><FONT COLOR=BLACK>r1n</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>55</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>ij0d</TD><TD BGCOLOR=#AAAAFF NOWRAP>bd</TD>
</TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>8np</FONT>
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/t1-"><FONT COLOR=BLACK>-19</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>31</TD>
</TD><TD BGCOLOR=#00EE00 NOWRAP>A- S</TD><TD BGCOLOR=#AAAAFF NOWRAP></TD>
</TR>
<TR class="font" BGCOLOR=#E0E0E0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>264</FONT>
</TD>
<TD NOWRAP>
1006
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/2s9"><FONT COLOR=BLACK>9s3</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>55</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>l</TD><TD BGCOLOR=#AAAAFF NOWRAP>t</TD>
</TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>posd</FONT>
</TD>
<TD NOWRAP>
1029
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/klc"><FONT COLOR=BLACK>hjks</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>h</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>jds</TD><TD BGCOLOR=#AAAAFF NOWRAP>kos</TD>
</TR>
<TR class="font" BGCOLOR=#E0E0E0>
<TD NOWRAP>3</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>jops</FONT>
</TD>
<TD NOWRAP>
1022
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/lpjs"><FONT COLOR=BLACK>sojc</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>

</TD>
<TD NOWRAP>5s5</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>pio0</TD><TD BGCOLOR=#AAAAFF NOWRAP>sdg</TD>
</TR>
<TR class="font" BGCOLOR=#F0F0F0>
<TD NOWRAP>2</TD>
<TD NOWRAP>
<FONT COLOR=BLACK>fgkpsa</FONT>
</TD>
<TD NOWRAP>
1028
</TD>
<TD NOWRAP>
<A HREF="http://URL:PORT/portal/page/sjoopd"><FONT
COLOR=BLACK>49sdg</FONT></A>
<TD NOWRAP> 1
</TD>
<TD NOWRAP>
sdgas
</TD>
<TD NOWRAP>dgaes</TD>
</TD><TD BGCOLOR=#00CC00 NOWRAP>segq</TD><TD BGCOLOR=#AAAAFF NOWRAP>hw4e</TD>
</TR>
<TR class="PortletHeaderColor"><TH class="PortletHeaderText" COLSPAN=9>
results</TH>
</TR>
</TABLE></TD></TR></TABLE>
</DIV></TD></TR>
</TABLE>
</BODY>
</HTML>
'End of code********************************************************

Part of my VBA code is:

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.Navigate "MyURL"
.Visible = 0
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop

Set htmlDoc = .Document
Set htmlColl = htmlDoc.getElementsByTagName("INPUT")

If objIE.LocationName = "Conectar" Then
'codigo para ventana
inicio_sesion.Show 'code for introduce password and user

For Each htmlInput In htmlColl
If htmlInput.Name = "ssousername" Then htmlInput.Value =
strUid
If htmlInput.Name = "password" Then htmlInput.Value =
strPass
Next htmlInput

For Each htmlInput In htmlColl
If htmlInput.Name = "" Then
htmlInput.Click
Exit For
End If
Next htmlInput
End If
End With

Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop

url_busqueda = "My_URL_with_search_parameters"
objIE.Navigate url_busqueda

Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop

'Until this point, i have opened the IE window with a code like the posted
above

av · Sep 19, 2008

---------------------------------------------------------------

Thats excellent Tim
Thanks....

pgarcia · Sep 22, 2008

Are you still answering question on this? If you have a secur web page, how
to log in? You will need to put your User ID and password, but how? Also,
there is a button that reads "print freindly", how can you excute that button
then get the data?

Thanks

Web Query Stopped Working	5	Jun 6, 2008
Web Query imput/return data problem	5	Feb 8, 2006
Extracting data from web page with an imbedded DB query	1	Dec 14, 2006
Capture Web Page Source Code	5	Feb 5, 2012
web query with Workbooks.Open("http:// .....	2	Mar 25, 2008
Excel Import by Webquery	2	May 2, 2007
getting the source code of a web page	6	Oct 7, 2009
Excel Web Query	1	Dec 17, 2009

Extracting Web Data

John Wilson

Tim Williams

John Wilson

Tim Williams

ian bartlett

Tim Williams

ian bartlett

John Wilson

Tim Williams

John Wilson

El_perdido

av

pgarcia

Ask a Question

Similar Threads