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

 

[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