Opening New Work with Wedpage Content when address is in original workbook

[Richard Mishelof]

This is simplification



I have original worksheet which has cells in it that were created by a macro
similar to the following:





Range("D2").Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _

"http://finance.yahoo.com/q/cq?s=msft&d=v2" _

, TextToDisplay:="MicroSoft Stock"





What I would like to do is create a macro or a function such that cell E2
will have the text value E2 = http://finance.yahoo.com/q/cq?s=msft&d=v2







TIA



R. Mishelof

 

[Joel]

Sub GetStock()

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

stock = "msft"

URL = "http://finance.yahoo.com/q/cq?s=" & stock & "&d=v2"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

ID = "yfs_l10_" & stock

Set SearchResults = IE.document.getElementById(ID)

trade = SearchResults.innertext

MsgBox ("Stock " & stock & " last traded at : " & trade)

Range("E2") = trade

End Sub

 

[Richard Mishelof]

Hi Joel,

Thank you for getting back to me, but I think my simplification took you in the wrong direction.

The real problem is:
1. The NY Lottery Webpage has a list of winning numbers for the Take 5 Game.

http://www.nylottery.org/ny/nyStore...7&RngDtYrEnd=2008&RngDtDtStrt=1&RngDtDtEnd=1&


2. I captured the data and put it in a worksheet:
Date (Payout Info) Winning Numbers Drawn
11/30/2008 12 13 22 26 36
11/29/2008 01 04 05 29 39
11/28/2008 15 17 18 20 23
11/27/2008 03 05 07 10 12
11/26/2008 09 18 20 28 29
11/25/2008 03 09 14 21 39
11/24/2008 02 13 20 36 38



If you look at the dates, they are URL's to different WebPages:

For Example:
11/30/2008 points to: http://www.nylottery.org/ny/nyStore/cgi-bin/ProdDetEv_Cat_333661_NavRoot_304_ProdID_1458313.htm

The control number: 1458313 changes for each date. By being able to make the URL visible, I can get at the control number. There is a
another webpage that uses the same control number:
http://www.nylottery.org/ny/nyStore/cgi-bin/PrintablePressRelease_ProdID_1458313.htm


It is this page that I would to extract information from such as: prize amount and number of winners for each tier. These tier values will be put next to the original worksheet. Since there are several hundred date entries. I was hoping to have this run overnight.

The key is making the URL visible and manipulateable.


TIA

Richie..

 

[Joel]

I simplified my answer because you simplified your question. I use a
combination of techniques to get all the data (including the hidden data)
from a webpage. I often ue the menu item on the internet explorer View -
Source. I look in the source for
1) Tags - start and ends with Angle Brackets like <A .............. /A>
The end tag may have the Tag name or the tag name can be left out

2) Classname which is ID="ABC" which can be found using the ID shown
commented out in the code below.


Try running these routines to help you understand how to get data from a
webpage. I often set break point in the routine below to help me find the
data I'm looking for. also I add ITM as a watch item to help me debug my
code. You can also add IE.Document to the watch window and look under ALL
(this only shows the 1st 256 items in the watch window)


I think you will be interested in the 2nd macro in column D on sheet 2 which
is the href parameter.


Sub GetLottery1()

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

URL = "http://www.nylottery.org/ny/nyStore/cgi-bin/" & _
"TakeFivePastResults_Cat_337678_SubCat_337693_NavRoot_302.htm"

Request = "?DrwnDtMon=-1&" & _
"DrwnDtYr=-1&DrwnDtDt=-1&" & _
"RngDtMonStrt=11&" & _
"RngDtMonEnd=11&" & _
"RngDtYrStrt=2007&" & _
"RngDtYrEnd=2008&" & _
"RngDtDtStrt=1&" & _
"RngDtDtEnd=1&;"




IE.Navigate2 URL & Request
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

With Sheets("Sheet1")
RowCount = 1
For Each itm In IE.document.all
.Range("A" & RowCount) = itm.classname
.Range("B" & RowCount) = itm.tagname
.Range("C" & RowCount) = Left(itm.innertext, 1024)

RowCount = RowCount + 1
Next itm
End With
End Sub

Sub GetLottery2()

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

URL = "http://www.nylottery.org/ny/nyStore/cgi-bin/" & _
"TakeFivePastResults_Cat_337678_SubCat_337693_NavRoot_302.htm"

Request = "?DrwnDtMon=-1&" & _
"DrwnDtYr=-1&DrwnDtDt=-1&" & _
"RngDtMonStrt=11&" & _
"RngDtMonEnd=11&" & _
"RngDtYrStrt=2007&" & _
"RngDtYrEnd=2008&" & _
"RngDtDtStrt=1&" & _
"RngDtDtEnd=1&;"


IE.Navigate2 URL & Request
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

'Set Games = IE.document.getElementById("A") 'id is classsname
Set Games = IE.document.getelementsbytagname("A")

With Sheets("Sheet2")
RowCount = 1
For Each itm In Games
.Range("A" & RowCount) = itm.classname
.Range("B" & RowCount) = itm.tagname
.Range("C" & RowCount) = Left(itm.innertext, 1024)
.Range("D" & RowCount) = itm.href

RowCount = RowCount + 1
Next itm
End With
End Sub

 

[Richard Mishelof]

Thanks Joel,

I will play with this tonight and tomorrow and get back to you.

I appreciate the level effort of you put into this.

Richard

PS - I tried to email at (e-mail address removed), but got rejected.

 

[Joel]

Here is one more routine you will like

Sub GetLottery3()

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

URL = "http://www.nylottery.org/ny/nyStore/cgi-bin/" & _
"TakeFivePastResults_Cat_337678_SubCat_337693_NavRoot_302.htm"

Request = "?DrwnDtMon=-1&" & _
"DrwnDtYr=-1&DrwnDtDt=-1&" & _
"RngDtMonStrt=11&" & _
"RngDtMonEnd=11&" & _
"RngDtYrStrt=2007&" & _
"RngDtYrEnd=2008&" & _
"RngDtDtStrt=1&" & _
"RngDtDtEnd=1&;"




IE.Navigate2 URL & Request
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

StartData = False
FoundBR = False
With Sheets("Sheet3")
RowCount = 0
For Each itm In IE.document.all

If StartData = False Then
If FoundBR = False Then
If itm.tagname = "BR" Then
FoundBR = True = True
End If
Else
If itm.tagname = "A" Then
StartData = True = True
End If
End If
End If

If StartData = True Then
Select Case itm.tagname

Case "A":
RowCount = RowCount + 1
.Range("A" & RowCount) = itm.innertext
.Range("G" & RowCount) = itm.href
ColCount = 2

Case "TD":
If ColCount <= 6 Then
.Cells(RowCount, ColCount) = _
itm.innertext
ColCount = ColCount + 1
End If
Case "DIV":
Exit For

End Select
End If
Next itm
End With
IE.Quit
End Sub