I have a workbook with 2 worksheets:

worksheet 1 - is for stock prices
worksheet 2 - is for mutual fund prices

I have written (with lots of help from you nice folks) 2 sub-routines

sub-routine 1 gets stock prices for worksheet 1
sub-routine 2 gets mutual funds prices for worksheet 2

How can I get these my 2 sub-routines to work in sequence with one macro?
Specifically, I want to programmically:

1. Go to worksheet 1 and run sub-routine 1 and then
2. Go to worksheet 2 and run sub-routine 2.

What is the coding?

Hi Bob

in a very simple version (without error checking, etc) try

Sub Sequence_Macro()
Sheets("Table1").Select
sub_routine_1 ()
Sheets("Table2").Select
sub_routine_2 ()
end sub

Frank

Bob Benjamin wrote:
I have a workbook with 2 worksheets:

worksheet 1 - is for stock prices
worksheet 2 - is for mutual fund prices

I have written (with lots of help from you nice folks) 2 sub-routines

sub-routine 1 gets stock prices for worksheet 1
sub-routine 2 gets mutual funds prices for worksheet 2

How can I get these my 2 sub-routines to work in sequence with one
macro? Specifically, I want to programmically:

1. Go to worksheet 1 and run sub-routine 1 and then
2. Go to worksheet 2 and run sub-routine 2.

What is the coding?

Thanks Frank. How would I call macros instead of sub-routines?

At the moment, the 2 sub-routines are 2 different macros. One macro is
called Getstocks and the other is called GetFunds.

Could I adjust your suggested code to call the two macros in a third
controlling macro? What would the code look like?

for example, pseudo code wise:
Controlling Macro
Do macro-Getstocks
Do macro-GetFunds
Controlling Macro

Or

Could I just put both sub-routines in the same singe macro.
Would sub-routine run sequentially without being told to? What would the
code look like?

for example pseudo code wise:
Macro getprices:
Sub Getstocks
Sub Getfunds
End getprices

Regards, BobB.
"Frank Kabel" wrote in message
...
Hi Bob

in a very simple version (without error checking, etc) try

Sub Sequence_Macro()
Sheets("Table1").Select
sub_routine_1 ()
Sheets("Table2").Select
sub_routine_2 ()
end sub

Frank

Bob Benjamin wrote:
I have a workbook with 2 worksheets:

worksheet 1 - is for stock prices
worksheet 2 - is for mutual fund prices

I have written (with lots of help from you nice folks) 2 sub-routines

sub-routine 1 gets stock prices for worksheet 1
sub-routine 2 gets mutual funds prices for worksheet 2

How can I get these my 2 sub-routines to work in sequence with one
macro? Specifically, I want to programmically:

1. Go to worksheet 1 and run sub-routine 1 and then
2. Go to worksheet 2 and run sub-routine 2.

What is the coding?

Hi Bob

are you sure that you have macros and not normal VBA routines (e.g.,
other Sub procedures)? If they are macros have a look at thr Run method
of the application object in the VBA help file.

Frank

Bob Benjamin wrote:
Thanks Frank. How would I call macros instead of sub-routines?

At the moment, the 2 sub-routines are 2 different macros. One macro
is
called Getstocks and the other is called GetFunds.

Could I adjust your suggested code to call the two macros in a third
controlling macro? What would the code look like?

for example, pseudo code wise:
Controlling Macro
Do macro-Getstocks
Do macro-GetFunds
Controlling Macro

Or

Could I just put both sub-routines in the same singe macro.
Would sub-routine run sequentially without being told to? What would
the code look like?

for example pseudo code wise:
Macro getprices:
Sub Getstocks
Sub Getfunds
End getprices

Regards, BobB.
"Frank Kabel" wrote in message
...
Hi Bob

in a very simple version (without error checking, etc) try

Sub Sequence_Macro()
Sheets("Table1").Select
sub_routine_1 ()
Sheets("Table2").Select
sub_routine_2 ()
end sub

Frank

Bob Benjamin wrote:
I have a workbook with 2 worksheets:

worksheet 1 - is for stock prices
worksheet 2 - is for mutual fund prices

I have written (with lots of help from you nice folks) 2
sub-routines

sub-routine 1 gets stock prices for worksheet 1
sub-routine 2 gets mutual funds prices for worksheet 2

How can I get these my 2 sub-routines to work in sequence with one
macro? Specifically, I want to programmically:

1. Go to worksheet 1 and run sub-routine 1 and then
2. Go to worksheet 2 and run sub-routine 2.

What is the coding?

Hi Frank,

I'm not sure if they are macros or subs.

To invoke the GetStock sub I go to the Tools menu, Select Macro, and then
Run Macro GetStocks. But if I chose to edit it instead of running it, the
first line in Microsoft Visual Basic window that opens is Sub GetStocks(),
so I assume its just a sub. The other GetFund is the same.

Regards, Bob

"Frank Kabel" wrote in message
...
Hi Bob

are you sure that you have macros and not normal VBA routines (e.g.,
other Sub procedures)? If they are macros have a look at thr Run method
of the application object in the VBA help file.

Frank

Bob Benjamin wrote:
Thanks Frank. How would I call macros instead of sub-routines?

At the moment, the 2 sub-routines are 2 different macros. One macro
is
called Getstocks and the other is called GetFunds.

Could I adjust your suggested code to call the two macros in a third
controlling macro? What would the code look like?

for example, pseudo code wise:
Controlling Macro
Do macro-Getstocks
Do macro-GetFunds
Controlling Macro

Or

Could I just put both sub-routines in the same singe macro.
Would sub-routine run sequentially without being told to? What would
the code look like?

for example pseudo code wise:
Macro getprices:
Sub Getstocks
Sub Getfunds
End getprices

Regards, BobB.
"Frank Kabel" wrote in message
...
Hi Bob

in a very simple version (without error checking, etc) try

Sub Sequence_Macro()
Sheets("Table1").Select
sub_routine_1 ()
Sheets("Table2").Select
sub_routine_2 ()
end sub

Frank

Bob Benjamin wrote:
I have a workbook with 2 worksheets:

worksheet 1 - is for stock prices
worksheet 2 - is for mutual fund prices

I have written (with lots of help from you nice folks) 2
sub-routines

sub-routine 1 gets stock prices for worksheet 1
sub-routine 2 gets mutual funds prices for worksheet 2

How can I get these my 2 sub-routines to work in sequence with one
macro? Specifically, I want to programmically:

1. Go to worksheet 1 and run sub-routine 1 and then
2. Go to worksheet 2 and run sub-routine 2.

What is the coding?

Hi Bob

than my first suggestion should work:
1. Just go to to the Macro menu.
2. Type in a new name for your 'consolidation' macro (e.g.
'Sequence_macro')
3. Click 'Create' and the visual basic editor should open
4. Between the lines 'Sub Sequence_macro ()' and 'End Sub' insert the
following lines:
GetStocks
GetFund
[no brackets behind your macro names)
5. Save and exit

This should do the trick
Frank



Bob Benjamin wrote:
Hi Frank,

I'm not sure if they are macros or subs.

To invoke the GetStock sub I go to the Tools menu, Select Macro, and
then Run Macro GetStocks. But if I chose to edit it instead of
running it, the first line in Microsoft Visual Basic window that
opens is Sub GetStocks(), so I assume its just a sub. The other
GetFund is the same.

Regards, Bob

"Frank Kabel" wrote in message
...
Hi Bob

are you sure that you have macros and not normal VBA routines (e.g.,
other Sub procedures)? If they are macros have a look at thr Run
method of the application object in the VBA help file.

Frank

Bob Benjamin wrote:
Thanks Frank. How would I call macros instead of sub-routines?

At the moment, the 2 sub-routines are 2 different macros. One macro
is called Getstocks and the other is called GetFunds.

Could I adjust your suggested code to call the two macros in a
third
controlling macro? What would the code look like?

for example, pseudo code wise:
Controlling Macro
Do macro-Getstocks
Do macro-GetFunds
Controlling Macro

Or

Could I just put both sub-routines in the same singe macro.
Would sub-routine run sequentially without being told to? What
would the code look like?

for example pseudo code wise:
Macro getprices:
Sub Getstocks
Sub Getfunds
End getprices

Regards, BobB.
"Frank Kabel" wrote in message
...
Hi Bob

in a very simple version (without error checking, etc) try

Sub Sequence_Macro()
Sheets("Table1").Select
sub_routine_1 ()
Sheets("Table2").Select
sub_routine_2 ()
end sub

Frank

Bob Benjamin wrote:
I have a workbook with 2 worksheets:

worksheet 1 - is for stock prices
worksheet 2 - is for mutual fund prices

I have written (with lots of help from you nice folks) 2
sub-routines

sub-routine 1 gets stock prices for worksheet 1
sub-routine 2 gets mutual funds prices for worksheet 2

How can I get these my 2 sub-routines to work in sequence with
one
macro? Specifically, I want to programmically:

1. Go to worksheet 1 and run sub-routine 1 and then
2. Go to worksheet 2 and run sub-routine 2.

What is the coding?

Hi Frank,

Here is the actual code as seen in the Microsoft Visual Basic window for the
GetStockValue sub/macro.
(The GetFund sub/macro follows it.)

I assume that they are subs that have to be invoked from the Tool/Macro
menu.
Both are working fine separately but it would easier if I could run them
sequentially either by putting them
in the same sub/macro or whatever it is called one after the other. Or, kept
them as they are now, ie. separate,
and invoke them sequentially from a controlling macro/sub or whatever it is.

GETSTOCKVALUES code as it appears in Microsoft Visual Basic window
----------------------------------------------------------------------------
Option Explicit
Private Const msLastTraded As String = "Last Traded"
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 msEPS As String = "Earnings/Share (trailing 12 months)"
Private Const msDIVIDEND_RATE As String = "Dividend Rate"
Private Rindex As Byte, LastColumn As Byte, LastRow As Byte
Private sURL As String, sFirst As String, sSymbol As String, sLast, vs As
String
Private ie As Object, s As String, nStart As Integer, nEnd As Integer, wbk1
As Workbook
Private tx As String, cx As String
Private rng As Range
Private Start As Single, EndTime As Single, TimeTook As Single, TimeTaken As
Single, LT As Single
Private EPS As Single



Sub GetStockValues() ' I assume this indicates that it is a sub



ThisWorkbook.EnvelopeVisible = False
Start = Timer
Set rng = Range("b3:h17")

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

Set ie = CreateObject("InternetExplorer.Application")

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

Range("B3:i17").ClearContents

' Application.ScreenUpdating = False

For Rindex = 3 To 17

' Construct an sURL to Navigate with
sURL = ""
sSymbol = Trim(Cells(Rindex, 1))

sURL = sFirst & sSymbol & sLast

ie.Navigate sURL

'wait for response
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop

' get html page body text
s = ie.Document.body.innertext

' get stock quote values using the function
Cells(Rindex, 2) = GetValue(msLastTraded, s)
LT = GetValue(msROLLING_52_HIGH, s)
Cells(Rindex, 3) = Format(LT, "##.###")
Cells(Rindex, 4) = GetValue(msROLLING_52_LOW, s)
Cells(Rindex, 5) = GetValue(msPE_RATIO, s)
EPS = GetValue(msEPS, s)
Cells(Rindex, 6) = Format(EPS, "##.###")
Cells(Rindex, 7) = GetValue(msDIVIDEND_RATE, s)
Cells(Rindex, 8) = Format((Cells(Rindex, 7) / LT) * 100,
"##.###")
Cells(Rindex, 9) = Format((EPS / LT) * 100, "##.###")


'Range("Output").Cells(Rindex, 3) = GetValue(msLastTraded, s)
'Range("Output").Cells(Rindex, 4) = GetValue(msROLLING_52_HIGH,
s)
'Range("Output").Cells(Rindex, 5) = GetValue(msROLLING_52_LOW,
s)
'Range("Output").Cells(Rindex, 6) = GetValue(msPE_RATIO, s)
'Range("Output").Cells(Rindex, 7) = GetValue(msEPS, s)
'Range("Output").Cells(Rindex, 8) = GetValue(msDIVIDEND_RATE, s)
Next Rindex

'close ie and remove memory references
ie.Quit
Set ie = Nothing
EndTime = Timer
TimeTook = (EndTime - Start) / 60
TimeTaken = Format(TimeTook, "##.##")
MsgBox (TimeTaken)
' Application.ScreenUpdating = True
' wbk1.Sheets(1).Calculate
End Sub

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))
cx = ""
tx = ""

' retrieve only the number value
Dim i As Integer
For i = 1 To Len(GetValue)
tx = Mid$(GetValue, i, 1)
If InStr(1, "1234567890.-", tx) Then
cx = cx & tx
Else
Exit For
End If
Next i
GetValue = cx
End Function


GETSFUNDS code as it appears in Microsoft Visual Basic window
---------------------------------------------------------------------
Option Explicit

Sub getfunds()
'
' funds Macro
' Macro recorded 1/10/2004 by bob
'

'
Range("A1").Select
With Selection.QueryTable
.Connection = _

"URL;http://www.tse.com/HttpController?Sa...cklistView Pa
ge"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "30"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


"Frank Kabel" wrote in message
...
Hi Bob

are you sure that you have macros and not normal VBA routines (e.g.,
other Sub procedures)? If they are macros have a look at thr Run method
of the application object in the VBA help file.

Frank

Bob Benjamin wrote:
Thanks Frank. How would I call macros instead of sub-routines?

At the moment, the 2 sub-routines are 2 different macros. One macro
is
called Getstocks and the other is called GetFunds.

Could I adjust your suggested code to call the two macros in a third
controlling macro? What would the code look like?

for example, pseudo code wise:
Controlling Macro
Do macro-Getstocks
Do macro-GetFunds
Controlling Macro

Or

Could I just put both sub-routines in the same singe macro.
Would sub-routine run sequentially without being told to? What would
the code look like?

for example pseudo code wise:
Macro getprices:
Sub Getstocks
Sub Getfunds
End getprices

Regards, BobB.
"Frank Kabel" wrote in message
...
Hi Bob

in a very simple version (without error checking, etc) try

Sub Sequence_Macro()
Sheets("Table1").Select
sub_routine_1 ()
Sheets("Table2").Select
sub_routine_2 ()
end sub

Frank

Bob Benjamin wrote:
I have a workbook with 2 worksheets:

worksheet 1 - is for stock prices
worksheet 2 - is for mutual fund prices

I have written (with lots of help from you nice folks) 2
sub-routines

sub-routine 1 gets stock prices for worksheet 1
sub-routine 2 gets mutual funds prices for worksheet 2

How can I get these my 2 sub-routines to work in sequence with one
macro? Specifically, I want to programmically:

1. Go to worksheet 1 and run sub-routine 1 and then
2. Go to worksheet 2 and run sub-routine 2.

What is the coding?

Sorry
now i missed the worksheet changes. So in total the code should look
like the following

Sub sequence_macro ()
Sheets("Table1").Select
GetStocks
Sheets("Table2").Select
GetFund
End Sub

Frank

Thanks Frank.

"Frank Kabel" wrote in message
...
Sorry
now i missed the worksheet changes. So in total the code should look
like the following

Sub sequence_macro ()
Sheets("Table1").Select
GetStocks
Sheets("Table2").Select
GetFund
End Sub

Frank