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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Opening New Work with Wedpage Content when address is in original workbook (https://www.excelbanter.com/excel-programming/420692-opening-new-work-wedpage-content-when-address-original-workbook.html)

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

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

Opening New Work with Wedpage Content when address is in original

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" wrote:

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

Opening New Work with Wedpage Content when address is in original

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/...&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/..._1458313.ht m

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/...ID_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" wrote in message ...
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" wrote:

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

Opening New Work with Wedpage Content when address is in origi

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_NavR oot_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_NavR oot_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" wrote:

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/...&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/..._1458313.ht m

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/...ID_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" wrote in message ...
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" wrote:

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

Opening New Work with Wedpage Content when address is in origi