How can I modify the following code to direct its output
to a worksheet instead of to the immediate window?


Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
& "etailedView=DetailedPrices&Language=en&QuoteSymbo l_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub

Replace all the debug print with sheet reference.

Since you are building a text string for each output line, that string could
be entered directly into the sheet with

'Set up a named range Output (not essential but I prefer this approach)
ActiveWorkbook.Names.Add Name:="Output", RefersToR1C1:="=Sheet1!R1C1:R20C1"

' store string into the cell row 1 of the output range
Range("Output").Cell.(1,1) = msROLLING_52_HIGH & ": " & Mid$(s, nStart,
nEnd - nStart)

Using this method you can programmatically control the location using the
Cell row and column index values and index through all the values. You
might consider wrting each stock value in one row, in which case make the
range multiple columns and change the column index for each stock value,
before changing the row index to move onto the next stock.

Cheers
N


"Bob Benjamin" wrote in message
...
How can I modify the following code to direct its output
to a worksheet instead of to the immediate window?


Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
& "etailedView=DetailedPrices&Language=en&QuoteSymbo l_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Thanks, Nigel, for the help. Regards, BobB
"Nigel" wrote in message
...
Replace all the debug print with sheet reference.

Since you are building a text string for each output line, that string
could
be entered directly into the sheet with

'Set up a named range Output (not essential but I prefer this approach)
ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R20C1"

' store string into the cell row 1 of the output range
Range("Output").Cell.(1,1) = msROLLING_52_HIGH & ": " & Mid$(s, nStart,
nEnd - nStart)

Using this method you can programmatically control the location using the
Cell row and column index values and index through all the values. You
might consider wrting each stock value in one row, in which case make the
range multiple columns and change the column index for each stock value,
before changing the row index to move onto the next stock.

Cheers
N


"Bob Benjamin" wrote in message
...
How can I modify the following code to direct its output
to a worksheet instead of to the immediate window?


Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
& "etailedView=DetailedPrices&Language=en&QuoteSymbo l_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

Hi,

I tried the suggested approach but I get the error message"

"Run-time error '1004'
"Method 'Range of object'_Global failed'"

Is it a syntact error?

The error is for line:

Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" &
Mid$(s, nStart, nEnd - nStart)
Note I added ".Value" after I first got the error and s to Cell.

Below is the entire code for the sub-routine:

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")
Set wbk1 = ActiveWorkbook

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R50C1"


With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&" _
&
"DetailedView=DetailedPrices&Language=en&QuoteSymb ol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
' Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
' nEnd - nStart)
' Range("Output").Cell(1, 1) = msROLLING_52_HIGH & ":" & Mid$(s, nStart,
nEnd - nStart)
Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" & Mid$(s,
nStart, nEnd - nStart)
' Range("Output").Cells(1, 1).Value = Mid$(s, nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub



"Nigel" wrote in message
...
Replace all the debug print with sheet reference.

Since you are building a text string for each output line, that string
could
be entered directly into the sheet with

'Set up a named range Output (not essential but I prefer this approach)
ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R20C1"

' store string into the cell row 1 of the output range
Range("Output").Cell.(1,1) = msROLLING_52_HIGH & ": " & Mid$(s, nStart,
nEnd - nStart)

Using this method you can programmatically control the location using the
Cell row and column index values and index through all the values. You
might consider wrting each stock value in one row, in which case make the
range multiple columns and change the column index for each stock value,
before changing the row index to move onto the next stock.

Cheers
N


"Bob Benjamin" wrote in message
...
How can I modify the following code to direct its output
to a worksheet instead of to the immediate window?


Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
& "etailedView=DetailedPrices&Language=en&QuoteSymbo l_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

Hi Bob,

I re-tested my version and I do not get the error!! What version of Excel
are you using?
Here is the listing I used......

Option Explicit

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

ActiveWorkbook.Names.Add Name:="Output", RefersToR1C1:="=Sheet1!R1C1:R4C1"

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(1, 1) = msROLLING_52_HIGH & ":" & Mid$(s,
nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(2, 1) = msROLLING_52_LOW & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(3, 1) = msPE_RATIO & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(4, 1) = msDIVIDEND_RATE & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

End Sub




"Bob Benjamin" wrote in message
...
Hi,

I tried the suggested approach but I get the error message"

"Run-time error '1004'
"Method 'Range of object'_Global failed'"

Is it a syntact error?

The error is for line:

Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" &
Mid$(s, nStart, nEnd - nStart)
Note I added ".Value" after I first got the error and s to Cell.

Below is the entire code for the sub-routine:

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")
Set wbk1 = ActiveWorkbook

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R50C1"


With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&" _
&
"DetailedView=DetailedPrices&Language=en&QuoteSymb ol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
' Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
' nEnd - nStart)
' Range("Output").Cell(1, 1) = msROLLING_52_HIGH & ":" & Mid$(s, nStart,
nEnd - nStart)
Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" & Mid$(s,
nStart, nEnd - nStart)
' Range("Output").Cells(1, 1).Value = Mid$(s, nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub



"Nigel" wrote in message
...
Replace all the debug print with sheet reference.

Since you are building a text string for each output line, that string
could
be entered directly into the sheet with

'Set up a named range Output (not essential but I prefer this approach)
ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R20C1"

' store string into the cell row 1 of the output range
Range("Output").Cell.(1,1) = msROLLING_52_HIGH & ": " & Mid$(s, nStart,
nEnd - nStart)

Using this method you can programmatically control the location using
the
Cell row and column index values and index through all the values. You
might consider wrting each stock value in one row, in which case make
the
range multiple columns and change the column index for each stock value,
before changing the row index to move onto the next stock.

Cheers
N


"Bob Benjamin" wrote in message
...
How can I modify the following code to direct its output
to a worksheet instead of to the immediate window?


Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend
Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
& "etailedView=DetailedPrices&Language=en&QuoteSymbo l_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Hi Nigel,

Your code below works perfectly on my version of Excel 2002.

Thanks again for all your help. I'll now try to implement
the other features that you suggested.
Regards, BobB

"Nigel" wrote in message
...
Hi Bob,

I re-tested my version and I do not get the error!! What version of Excel
are you using?
Here is the listing I used......

Option Explicit

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R4C1"

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate

"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(1, 1) = msROLLING_52_HIGH & ":" &
Mid$(s,
nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(2, 1) = msROLLING_52_LOW & ": " &
Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(3, 1) = msPE_RATIO & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(4, 1) = msDIVIDEND_RATE & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

End Sub




"Bob Benjamin" wrote in message
...
Hi,

I tried the suggested approach but I get the error message"

"Run-time error '1004'
"Method 'Range of object'_Global failed'"

Is it a syntact error?

The error is for line:

Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" &
Mid$(s, nStart, nEnd - nStart)
Note I added ".Value" after I first got the error and s to Cell.

Below is the entire code for the sub-routine:

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")
Set wbk1 = ActiveWorkbook

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R50C1"


With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&" _
&
"DetailedView=DetailedPrices&Language=en&QuoteSymb ol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
' Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
' nEnd - nStart)
' Range("Output").Cell(1, 1) = msROLLING_52_HIGH & ":" & Mid$(s, nStart,
nEnd - nStart)
Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" & Mid$(s,
nStart, nEnd - nStart)
' Range("Output").Cells(1, 1).Value = Mid$(s, nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub



"Nigel" wrote in message
...
Replace all the debug print with sheet reference.

Since you are building a text string for each output line, that string
could
be entered directly into the sheet with

'Set up a named range Output (not essential but I prefer this
approach)
ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R20C1"

' store string into the cell row 1 of the output range
Range("Output").Cell.(1,1) = msROLLING_52_HIGH & ": " & Mid$(s,
nStart,
nEnd - nStart)

Using this method you can programmatically control the location using
the
Cell row and column index values and index through all the values.
You
might consider wrting each stock value in one row, in which case make
the
range multiple columns and change the column index for each stock
value,
before changing the row index to move onto the next stock.

Cheers
N


"Bob Benjamin" wrote in message
...
How can I modify the following code to direct its output
to a worksheet instead of to the immediate window?


Private Const msROLLING_52_HIGH As String = "Rolling 52 Week
High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend
Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
&
"etailedView=DetailedPrices&Language=en&QuoteSymbo l_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

I need some more help with the Range statements.



I am trying to write a VBA sub-routine to fill in spreadsheet (sheet1),
range R3C2:R17C7 with stock information.



This is what sheet (1) looks like:



Note: I want to fill in R3C2 to R17C7 with stock quote values from
www.tse.ca....



A B C D
E F G 1


Last Trade
Hi
Lo
PE
EPS
DIV

Search Key
Last Traded
52Week High
52 Week Low
P/E Ratio
Earnings/Share
dividend rate

ATY







BBD.B







BCE







BMO







GND







NT







T







T.A







TA







ZEN







POW







TOC







ROC







FFH







DBC.A










I have added a Function to eliminate some of the repetitive code. The
function works fine.



But line: Range("Output").Cells(4, 4) = GetValue(msROLLING_52_HIGH,
s)



which calls the function to write to R4C4 (for example)



keeps causing the following error message:



Run-time error '1004'

Method Range of object _ Global failed



I can't fiqure out why this Range statement (and ones similar to it) does
not work.



Below is my listing: (Note I only included 1 range statement to keep it
simple)







Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"

Private Rindex As Byte, LastRow As Byte

Private sURL As String, sFirst As String, sSymbol As String, sLast, vs
As String



Function GetValue(vs As String, s As String) As String

nStart = InStr(1, s, vs, vbTextCompare)

If nStart Then

nStart = nStart + Len(vs)

nEnd = InStr(nStart, s, vbCrLf)

End If

GetValue = Trim(Mid$(s, nStart, nEnd - nStart))

c$ = ""

t$ = ""



' retrieve only the number value



For i = 1 To Len(GetValue)

t$ = Mid$(GetValue, i, 1)

If InStr(1, "123567890.", t$) Then

c$ = c$ & t$

Else

Exit For

End If

Next i

GetValue = c$

End Function





Sub GetStockValues()

Dim ie As Object

Dim s As String

Dim nStart As Integer

Dim nEnd As Integer



ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R17C8"



' How many stock quotes are there?



Set wbk1 = ThisWorkbook

With wbk1.Sheets(1)

' LastColumn = .Cells(1, 2).End(xlToRight).Column

LastRow = .Cells(2, 1).End(xlDown).Row

End With



'Get values for every quote using row index (rindex)





For Rindex = 3 To LastRow



Set ie = CreateObject("InternetExplorer.Application")





' Construct an sURL to Navigate with



sFirst =
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1="

sLast = "&x=18&y=7"

sURL = ""

sSymbol = Trim(Cells(Rindex, 1))





sURL = sFirst & sSymbol & sLast





With ie

'.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1=BCE&x=18&y=7"

.Navigate sURL



Do Until Not .Busy And .ReadyState = 4

DoEvents

Loop



s = ie.Document.body.innertext

.Quit

End With



' Set ie = Nothing



' get stock quote values using the function





Range("Output").Cells(Rindex 3) = GetValue(msROLLING_52_HIGH, s)





Next Rindex



End Sub



Any help would be appreciated.



BobB



"Nigel" wrote in message
...
Hi Bob,

I re-tested my version and I do not get the error!! What version of Excel
are you using?
Here is the listing I used......

Option Explicit

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R4C1"

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate

"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(1, 1) = msROLLING_52_HIGH & ":" &
Mid$(s,
nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(2, 1) = msROLLING_52_LOW & ": " &
Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(3, 1) = msPE_RATIO & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(4, 1) = msDIVIDEND_RATE & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

End Sub




"Bob Benjamin" wrote in message
...
Hi,

I tried the suggested approach but I get the error message"

"Run-time error '1004'
"Method 'Range of object'_Global failed'"

Is it a syntact error?

The error is for line:

Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" &
Mid$(s, nStart, nEnd - nStart)
Note I added ".Value" after I first got the error and s to Cell.

Below is the entire code for the sub-routine:

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")
Set wbk1 = ActiveWorkbook

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R50C1"


With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&" _
&
"DetailedView=DetailedPrices&Language=en&QuoteSymb ol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
' Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
' nEnd - nStart)
' Range("Output").Cell(1, 1) = msROLLING_52_HIGH & ":" & Mid$(s, nStart,
nEnd - nStart)
Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" & Mid$(s,
nStart, nEnd - nStart)
' Range("Output").Cells(1, 1).Value = Mid$(s, nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub



"Nigel" wrote in message
...
Replace all the debug print with sheet reference.

Since you are building a text string for each output line, that string
could
be entered directly into the sheet with

'Set up a named range Output (not essential but I prefer this
approach)
ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R20C1"

' store string into the cell row 1 of the output range
Range("Output").Cell.(1,1) = msROLLING_52_HIGH & ": " & Mid$(s,
nStart,
nEnd - nStart)

Using this method you can programmatically control the location using
the
Cell row and column index values and index through all the values.
You
might consider wrting each stock value in one row, in which case make
the
range multiple columns and change the column index for each stock
value,
before changing the row index to move onto the next stock.

Cheers
N


"Bob Benjamin" wrote in message
...
How can I modify the following code to direct its output
to a worksheet instead of to the immediate window?


Private Const msROLLING_52_HIGH As String = "Rolling 52 Week
High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend
Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
&
"etailedView=DetailedPrices&Language=en&QuoteSymbo l_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

I need some more help with the Range statements.



I am trying to write a VBA sub-routine to fill in spreadsheet (sheet1),
range R3C2:R17C7 with stock information.



This is what sheet (1) looks like:



I want to fill in R3C2 to R17C7 with stock quote values



A B C D E
F G


Last Trade
Hi
Lo
PE
EPS
DIV

Search Key
Last Traded
Week High
Week Low
P/E Ratio
Earnings/Share
dividend rate

ATY







BBD.B







BCE







BMO







GND







NT







T







T.A







TA







ZEN







POW







TOC







ROC







FFH







DBC.A










I have added a function to eliminate some of the repetitive code.



But line: Range("Output").Cells(4, 4) = GetValue(msROLLING_52_HIGH, s)

keeps causing the following error message:



Run-time error '1004'

Method Range of object _ Global failed



Below is my listing:



Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"

Private Rindex As Byte, LastRow As Byte

Private sURL As String, sFirst As String, sSymbol As String, sLast, vs
As String





Function GetValue(vs As String, s As String) As String

nStart = InStr(1, s, vs, vbTextCompare)

If nStart Then

nStart = nStart + Len(vs)

nEnd = InStr(nStart, s, vbCrLf)

End If

GetValue = Trim(Mid$(s, nStart, nEnd - nStart))

c$ = ""

t$ = ""



' retrieve only the number value



For i = 1 To Len(GetValue)

t$ = Mid$(GetValue, i, 1)

If InStr(1, "123567890.", t$) Then

c$ = c$ & t$

Else

Exit For

End If

Next i

GetValue = c$

End Function





Sub GetStockValues()

Dim ie As Object

Dim s As String

Dim nStart As Integer

Dim nEnd As Integer



ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R17C8"



' How many stock quotes are there?



Set wbk1 = ThisWorkbook

With wbk1.Sheets(1)

' LastColumn = .Cells(1, 2).End(xlToRight).Column

LastRow = .Cells(2, 1).End(xlDown).Row

End With



'Get values for every quote using row index (rindex)





For Rindex = 3 To LastRow



Set ie = CreateObject("InternetExplorer.Application")





' Construct an sURL to Navigate with



sFirst =
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1="

sLast = "&x=18&y=7"

sURL = ""

sSymbol = Trim(Cells(Rindex, 1))





sURL = sFirst & sSymbol & sLast





With ie

'.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1=BCE&x=18&y=7"

.Navigate sURL



Do Until Not .Busy And .ReadyState = 4

DoEvents

Loop



s = ie.Document.body.innertext

.Quit

End With



' Set ie = Nothing



' get stock quote values using the function





Range("Output").Cells(Rindex 3) = GetValue(msROLLING_52_HIGH, s)





Next Rindex



End Sub



Any help woud be appreciated.



BobB



"Nigel" wrote in message
...
Hi Bob,

I re-tested my version and I do not get the error!! What version of Excel
are you using?
Here is the listing I used......

Option Explicit

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R4C1"

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate

"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(1, 1) = msROLLING_52_HIGH & ":" &
Mid$(s,
nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(2, 1) = msROLLING_52_LOW & ": " &
Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(3, 1) = msPE_RATIO & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(4, 1) = msDIVIDEND_RATE & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

End Sub




"Bob Benjamin" wrote in message
...
Hi,

I tried the suggested approach but I get the error message"

"Run-time error '1004'
"Method 'Range of object'_Global failed'"

Is it a syntact error?

The error is for line:

Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" &
Mid$(s, nStart, nEnd - nStart)
Note I added ".Value" after I first got the error and s to Cell.

Below is the entire code for the sub-routine:

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")
Set wbk1 = ActiveWorkbook

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R50C1"


With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&" _
&
"DetailedView=DetailedPrices&Language=en&QuoteSymb ol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
' Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
' nEnd - nStart)
' Range("Output").Cell(1, 1) = msROLLING_52_HIGH & ":" & Mid$(s, nStart,
nEnd - nStart)
Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" & Mid$(s,
nStart, nEnd - nStart)
' Range("Output").Cells(1, 1).Value = Mid$(s, nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub



"Nigel" wrote in message
...
Replace all the debug print with sheet reference.

Since you are building a text string for each output line, that string
could
be entered directly into the sheet with

'Set up a named range Output (not essential but I prefer this
approach)
ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R20C1"

' store string into the cell row 1 of the output range
Range("Output").Cell.(1,1) = msROLLING_52_HIGH & ": " & Mid$(s,
nStart,
nEnd - nStart)

Using this method you can programmatically control the location using
the
Cell row and column index values and index through all the values.
You
might consider wrting each stock value in one row, in which case make
the
range multiple columns and change the column index for each stock
value,
before changing the row index to move onto the next stock.

Cheers
N


"Bob Benjamin" wrote in message
...
How can I modify the following code to direct its output
to a worksheet instead of to the immediate window?


Private Const msROLLING_52_HIGH As String = "Rolling 52 Week
High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend
Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
&
"etailedView=DetailedPrices&Language=en&QuoteSymbo l_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart, _
nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

Bob
Is it a simple case of a mssing comma in the cells index settings?

eg

Range("Output").Cells(Rindex 3) = GetValue(msROLLING_52_HIGH, s)

should read

Range("Output").Cells(Rindex, 3) = GetValue(msROLLING_52_HIGH, s)

Cheers
Nigel


"Bob Benjamin" wrote in message
...
I need some more help with the Range statements.



I am trying to write a VBA sub-routine to fill in spreadsheet (sheet1),
range R3C2:R17C7 with stock information.



This is what sheet (1) looks like:



I want to fill in R3C2 to R17C7 with stock quote values



A B C D E
F G


Last Trade
Hi
Lo
PE
EPS
DIV

Search Key
Last Traded
Week High
Week Low
P/E Ratio
Earnings/Share
dividend rate

ATY







BBD.B







BCE







BMO







GND







NT







T







T.A







TA







ZEN







POW







TOC







ROC







FFH







DBC.A










I have added a function to eliminate some of the repetitive code.



But line: Range("Output").Cells(4, 4) = GetValue(msROLLING_52_HIGH, s)

keeps causing the following error message:



Run-time error '1004'

Method Range of object _ Global failed



Below is my listing:



Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"

Private Rindex As Byte, LastRow As Byte

Private sURL As String, sFirst As String, sSymbol As String, sLast, vs
As String





Function GetValue(vs As String, s As String) As String

nStart = InStr(1, s, vs, vbTextCompare)

If nStart Then

nStart = nStart + Len(vs)

nEnd = InStr(nStart, s, vbCrLf)

End If

GetValue = Trim(Mid$(s, nStart, nEnd - nStart))

c$ = ""

t$ = ""



' retrieve only the number value



For i = 1 To Len(GetValue)

t$ = Mid$(GetValue, i, 1)

If InStr(1, "123567890.", t$) Then

c$ = c$ & t$

Else

Exit For

End If

Next i

GetValue = c$

End Function





Sub GetStockValues()

Dim ie As Object

Dim s As String

Dim nStart As Integer

Dim nEnd As Integer



ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R17C8"



' How many stock quotes are there?



Set wbk1 = ThisWorkbook

With wbk1.Sheets(1)

' LastColumn = .Cells(1, 2).End(xlToRight).Column

LastRow = .Cells(2, 1).End(xlDown).Row

End With



'Get values for every quote using row index (rindex)





For Rindex = 3 To LastRow



Set ie = CreateObject("InternetExplorer.Application")





' Construct an sURL to Navigate with



sFirst =

"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1="

sLast = "&x=18&y=7"

sURL = ""

sSymbol = Trim(Cells(Rindex, 1))





sURL = sFirst & sSymbol & sLast





With ie

'.Navigate

"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1=BCE&x=18&y=7"
\0
.Navigate sURL



Do Until Not .Busy And .ReadyState = 4

DoEvents

Loop



s = ie.Document.body.innertext

.Quit

End With



' Set ie = Nothing



' get stock quote values using the function





Range("Output").Cells(Rindex 3) = GetValue(msROLLING_52_HIGH, s)





Next Rindex



End Sub



Any help woud be appreciated.



BobB



"Nigel" wrote in message
...
Hi Bob,

I re-tested my version and I do not get the error!! What version of
Excel
are you using?
Here is the listing I used......

Option Explicit

Private Const msROLLING_52_HIGH As String = "Rolling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R4C1"

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate


"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&DetailedView =Detail
edPrices&Language=en&QuoteSymbol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(1, 1) = msROLLING_52_HIGH & ":" &
Mid$(s,
nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(2, 1) = msROLLING_52_LOW & ": " &
Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(3, 1) = msPE_RATIO & ": " & Mid$(s,
nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Range("Output").Cells(4, 1) = msDIVIDEND_RATE & ": " &
Mid$(s,
nStart, _
nEnd - nStart)
End If

End Sub




"Bob Benjamin" wrote in message
...
Hi,

I tried the suggested approach but I get the error message"

"Run-time error '1004'
"Method 'Range of object'_Global failed'"

Is it a syntact error?

The error is for line:

Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" &
Mid$(s, nStart, nEnd - nStart)
Note I added ".Value" after I first got the error and s to Cell.

Below is the entire code for the sub-routine:

Private Const msROLLING_52_HIGH As String = "Rlling 52 Week High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend
Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")
Set wbk1 = ActiveWorkbook

ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R50C1"


With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&" _
&
"DetailedView=DetailedPrices&Language=en&QuoteSymb ol_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
' Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart,
_
' nEnd - nStart)
' Range("Output").Cell(1, 1) = msROLLING_52_HIGH & ":" & Mid$(s,
nStart,
nEnd - nStart)
Range("Output").Cells(1, 1).Value = msROLLING_52_HIGH & ":" & Mid$(s,
nStart, nEnd - nStart)
' Range("Output").Cells(1, 1).Value = Mid$(s, nStart, nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub



"Nigel" wrote in message
...
Replace all the debug print with sheet reference.

Since you are building a text string for each output line, that
string
could
be entered directly into the sheet with

'Set up a named range Output (not essential but I prefer this
approach)
ActiveWorkbook.Names.Add Name:="Output",
RefersToR1C1:="=Sheet1!R1C1:R20C1"

' store string into the cell row 1 of the output range
Range("Output").Cell.(1,1) = msROLLING_52_HIGH & ": " & Mid$(s,
nStart,
nEnd - nStart)

Using this method you can programmatically control the location
using
the
Cell row and column index values and index through all the values.
You
might consider wrting each stock value in one row, in which case
make
the
range multiple columns and change the column index for each stock
value,
before changing the row index to move onto the next stock.

Cheers
N


"Bob Benjamin" wrote in message
...
How can I modify the following code to direct its output
to a worksheet instead of to the immediate window?


Private Const msROLLING_52_HIGH As String = "Rolling 52 Week
High"
Private Const msROLLING_52_LOW As String = "Rolling 52 Week
Low"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend
Rate"

Sub GetStockValues()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate
"http://www.tse.ca/HttpController?GetPage=QuotesViewPage&D" _
&
"etailedView=DetailedPrices&Language=en&QuoteSymbo l_1=BCE&x=18&y=7"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop

s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing

'/ get rolling 52-wk high
nStart = InStr(1, s, msROLLING_52_HIGH, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_HIGH)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_HIGH & ": " & Mid$(s, nStart,
_
nEnd - nStart)

End If

'/ get rolling 52-wk low
nStart = InStr(1, s, msROLLING_52_LOW, vbTextCompare)
If nStart Then
nStart = nStart + Len(msROLLING_52_LOW)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msROLLING_52_LOW & ": " & Mid$(s, nStart,
_
nEnd - nStart)
End If

'/ get p/e ratio
nStart = InStr(1, s, msPE_RATIO, vbTextCompare)
If nStart Then
nStart = nStart + Len(msPE_RATIO)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msPE_RATIO & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

'/ get dividend rate
nStart = InStr(1, s, msDIVIDEND_RATE, vbTextCompare)
If nStart Then
nStart = nStart + Len(msDIVIDEND_RATE)
nEnd = InStr(nStart, s, vbCrLf)
Debug.Print msDIVIDEND_RATE & ": " & Mid$(s, nStart, _
nEnd - nStart)
End If

End Sub







----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---