Nearly There! (Revisted)

[Saxman]

Below is the copy/paste code discussed earlier that works faultlessly.
It is Module4 of a four Module Excel spreadsheet. However, when I run the
'Sub Marco' (this one), it freezes the main macro that gathers data from
the web (which is where the sub macro gathers the copy data).

Can I run several macros simultaneously? I probably need to break
everything down and start afresh?

Should I incorporate this code into one of the other modules and run the
main macro. The code is lengthly, so I cannot post it here.

Thanks.


Sub The_Sub()
Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))......(and so on)....
End_Sub

 

[Guest]

Saxman,

What are you trying to accomplish?

Your current code does the following
Copies data from Sheets("Show") and paste it in
Sheets("Chartdata") Cell B2 then in C2 (same data)

Is this what you want?
If not then tell us what you want?

Aside from the above, you dont neet to select different sheets or ranges
to copy and paste. It wasts a lot of time and coding. You can accomplish
the above by simply stating the following:

Sheets("Show").Selection.Copy
'or Sheets("Show").Range("D14").Copy
'or Sheets("Show").Range("Cars")(4).Copy -- to copy the 4th item in
a list named "Cars"
Sheets("Chartdata").Range("B2").PasteSpecial xlValues
Sheets("Chartdata").Range("C2") = Sheets("Chartdata").Range("B2")

 

[Saxman]

Saxman,

What are you trying to accomplish?

Your current code does the following
Copies data from Sheets("Show") and paste it in
Sheets("Chartdata") Cell B2 then in C2 (same data)

Is this what you want?
If not then tell us what you want?

("Show") gets the data from the web that I require and I parse (filter) the
data before it is copied.

The worksheet ("Show") is dynamic and runs as a macro gathering the
required data from the web. The timing of the data can be adjusted by the
user (anything from 1 to 60 seconds). (This is why I need to copy/paste it
several times, so I can track movements in a graph).

When I run 'The_Sub' macro (copy/paste) as well, this is where things screw
up. The ("Show") macro hangs (which gathers the data). I'm sure 'The_Sub'
macro coding wants incorporating into the main code, but I'm no programer,
but I do have some knowledge of VB.

I can send you the main coding or spreadsheet if you want me to, then maybe
you will be able to understand things a bit better?

Thanks for your reply.

 

[Guest]

So the real issue is coordinating the two macros, right ?

Two macros can NOT be run simultaneously. What you can do is to call one
macro from inside of another macro. In your case, as I understand it:

The 'Show ' macro is your main macro which runs quite frequently on a timed
basis controlled by some user input ( every 1 to 60 seconds ?). Inside of
this macro you could do something like:

Sub Show()
' some code
' some code
'some more code
Call MyCopyPasteMacro
' some code
' some code
End Sub

This would copy/paste EVERY time the Show macro is run.

If you wanted to copy/Paste not every time but, say every 3rd time the Show
macro is run you could do it like:

Option Explicit

'Declare a global variable at the very top of your module:
Public giCount as Integer
'------------------------------------------------

Sub Show()
'increment your global variable
giCount = giCount +1
' some code
' some code
'some more code
If giCount>2 then '<===Adjust number
Call MyCopyPasteMacro
'reset giCount back to zero
giCount = 0
End If
' some code
' some code
End Sub

As you can see, the two macros are not running SIMULTANEOUSLY. The main one
starts. Then when the code reaches the IF statement, if the condition is
met, the control actually PASSES from the main to the sub-macro which runs to
completion, then control is passed back to the main-macro for it to complete.

Hope this helps

 

[Saxman]

So the real issue is coordinating the two macros, right ?

That is exactly right.

Two macros can NOT be run simultaneously.

That is what I thought.

What you can do is to call one
macro from inside of another macro.

I will give it a go, but it will take some doing with my experience!

Thanks very much for the feedback. I will let you know how I get on.

 

[Guest]

I would suggest you "learn" on a new test workbook, rather than your
main project.

It is really quite simple. Try the following:
1 Select and Copy the code found below:
2 Open a New wbk
3 Alt+F11 to open the visual Basic Editor
4 Insert>Module
5 Click in the right panel of the new module
6 Ctl-V to paste the following code.
7 Resize and/or move the VB editor screen so you can see the upper
left of the new worksheet behind the VBE.
8 Click so your cursor is anywhere in the middle of the FIRST SUB.
9 Step thru the code by repeatedly clicking the F8 key.
Observe the action as you step thru the code.
Note that the first and 2nd time thru the code it does not run the 2nd
macro, but it will on the 3rd run-thru, and will do so every 3rd run.

HTH

Code:
----------------------------------------

Option Explicit
'Declare public variable
Public giCount as Integer


Sub MainMacro()
giCount = giCount +1
With Activesheet
.Range("A1:B7").ClearContents
.Range("A1")="This starts my main macro."
.Range("A2")="This is also part of my main macro."
.Range("A3")="Now I'm going to check the Counter."
Msgbox " The counter is now at: " & giCount
If giCount>2 then
msgbox "That is high enough to call my other macro. So..."
MySubMacro 'just type the NAME of the macro to call it.
'reset the counter
giCount=0
Msgbox " I just set the counter back to zero."
Else
Msgbox " I'm waiting for the counter to reach 3, before
calling my other macro."
End if
'more code:
.Range("A6")="Now I am continuing with my main macro."
.Range("A7")="This is the End of my main macro."
End Sub


Sub MySubMacro()
Range("B4")="This starts my 2nd macro."
Range("B5")="This is the end of my 2nd macro."
Msgbox"Now I will jump back to my first macro."
End sub

 

[Saxman]

Sub Show()
' some code
' some code
'some more code
Call MyCopyPasteMacro
' some code
' some code
End Sub

The above is the ideal situation. A macro within a macro. It would be
ideal to send the gathered data upon each refresh to another worksheet to
be interpreted by a graph.

This would copy/paste EVERY time the Show macro is run.

Remember, the data changes every minute (or other period set by user) and I
need each set of new data pasting in a new column of 'Chartdata' upon each
refresh for the graph to interpret.

If you wanted to copy/Paste not every time but, say every 3rd time the Show
macro is run you could do it like:

No, I need it every time.

The data is gathered in cells G4, G6, G8, G10.................of the 'Show'
sheet and copied to cells J4, J5, J6, J7.....of the same sheet (Compacts
the data).

Here is my full coding (copy/paste macro) which runs successfully
independently from the main macro. It copies the data from cells J4, J5,
J6, J7.....of the 'Show' sheet (every 30 seconds in this case).

I find it difficult to know where to place my macro with the main macro.
Will the main macro go to my second instruction upon the second refresh, so
it gets pasted into a new column on the 'Chartdata' sheet?

After this coding is the full coding for your perusal. If you need the
spreadsheet with instructions, let me know.

Thanks,

John.
.....................................................................

Sub The_Sub()
Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("L2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:30"))

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("P2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub
.........................................................(End of my macro)

Option Explicit
Sub GetExchangeShow()
'Extract and store Betfair price shows.

Dim sURL, sHTML As String
Const sBFMarketPrefix As String =
"http://www.betfair.com/betting/LoadMarketDataAction.do?mi="

'Mask screen redraws during automated operations.
Application.ScreenUpdating = False

'Clear the work area.
ClearSnapShot

'Construct the full Betfair market page name from the fixed and
variable parts.
sURL = sBFMarketPrefix &
ThisWorkbook.Worksheets("Console").Range("MarketID").Cells(1, 1).Value

'Extract the show text.
sHTML = GetExchangeData(sURL)

'Parse the betting fields from the HTML text ...
' ... and write the show into the Snapshot worksheet.
CreateShow (sHTML)

'Store the show in its own permanent worksheet.
StoreShow

'Visibly reposition to the control worksheet.
Application.ScreenUpdating = True
ThisWorkbook.Worksheets("Show").Activate

End Sub
Sub ClearSnapShot()

ThisWorkbook.Worksheets("Latest
Snapshot").Range("EventName").ClearContents
ThisWorkbook.Worksheets("Latest
Snapshot").Range("Selections").ClearContents
ThisWorkbook.Worksheets("Latest Snapshot").Range("Back").ClearContents
ThisWorkbook.Worksheets("Latest Snapshot").Range("Lay").ClearContents
ThisWorkbook.Worksheets("Latest
Snapshot").Range("TimeStamp").ClearContents

End Sub
Sub CreateShow(sHTML)
'Extracts the event name, names of selections(<=100), back/lay prices and
amounts available ...
' ... returning the results to the Snapshot worksheet via named ranges.
'We cannot continue to use the Webrowser Control because the data is held
in VBscript calls.
'No problem though since the arguments are effectively quote or
comma-delimited data.

Dim sQuote, sAmount As String
Dim StartField, EndField As Integer
Dim SelectionNo, QuoteNo As Integer
Dim sEName, sSels(1 To 100, 1 To 1) As String
Dim sBacks(1 To 100, 1 To 3), sLays(1 To 100, 1 To 3) As String

'If something goes wrong proceed directly to copy any data gathered
back to the worksheet.
On Error GoTo Cb

'Extract the name of the event: the text in single quotes after the
p.m_M declaration.
StartField = InStr(sHTML, "p.m_M")
StartField = InStr(StartField + 1, sHTML, "'") + 1
EndField = InStr(StartField + 1, sHTML, "'") - 1
sEName = Mid(sHTML, StartField, EndField - StartField + 1)

'Extract the names of up to 100 selections, the best three back and lay
prices ...
' ... and the amount of money currently available at those prices.
SelectionNo = 1
StartField = InStr(EndField, sHTML, "p.m_R")
While StartField <> 0 And SelectionNo < 101

'First the selection name between single quotes again ...
StartField = InStr(StartField + 1, sHTML, "'") + 1
EndField = InStr(StartField + 1, sHTML, "'") - 1
sSels(SelectionNo, 1) = Mid(sHTML, StartField, EndField -
StartField + 1)

'... then the 3 back and 3 lay prices, comma delimited, skipping 2
unwanted fields ...
StartField = InStr(EndField, sHTML, ",") + 1
EndField = InStr(StartField + 1, sHTML, ",") - 1
StartField = InStr(EndField, sHTML, ",") + 1
EndField = InStr(StartField + 1, sHTML, ",") - 1
For QuoteNo = 1 To 3
'Back price ...
StartField = InStr(EndField, sHTML, ",") + 1
EndField = InStr(StartField + 1, sHTML, ",") - 1
sQuote = Mid(sHTML, StartField, EndField - StartField + 1)
' ... amount available.
StartField = InStr(EndField, sHTML, ",") + 1
EndField = InStr(StartField + 1, sHTML, ",") - 1
sAmount = Mid(sHTML, StartField, EndField - StartField + 1)
sBacks(SelectionNo, QuoteNo) = sQuote & "(" & sAmount & ")"
Next QuoteNo
For QuoteNo = 1 To 3
'Lay price ...
StartField = InStr(EndField, sHTML, ",") + 1
EndField = InStr(StartField + 1, sHTML, ",") - 1
sQuote = Mid(sHTML, StartField, EndField - StartField + 1)
'... amount available.
StartField = InStr(EndField, sHTML, ",") + 1
EndField = InStr(StartField + 1, sHTML, ",") - 1
sAmount = Mid(sHTML, StartField, EndField - StartField + 1)
sLays(SelectionNo, QuoteNo) = sQuote & "(" & sAmount & ")"
Next QuoteNo
' ... on to the next selection (if any).
SelectionNo = SelectionNo + 1
StartField = InStr(EndField, sHTML, "p.m_R")
Wend

'Copy the data collected in VBA arrays back to EXCEL ranges.
Cb: ThisWorkbook.Worksheets("Latest Snapshot").Range("EventName").Value =
sEName
ThisWorkbook.Worksheets("Latest Snapshot").Range("Selections").Value =
sSels
ThisWorkbook.Worksheets("Latest Snapshot").Range("Back").Value = sBacks
ThisWorkbook.Worksheets("Latest Snapshot").Range("Lay").Value = sLays

'Timestamp the data.
ThisWorkbook.Worksheets("Latest Snapshot").Range("TimeStamp").Cells(1,
1).Formula = "=Now()"
ThisWorkbook.Worksheets("Latest Snapshot").Range("TimeStamp").Cells(1,
2).Formula = "=Now()"

End Sub
Sub StoreShow()

Dim newSheetName As String

'Create a new worksheet whose name is the show number ... at the end of
the workbook.
'The possibility of sheet naming conflicts has already been eliminated.
newSheetName =
Str(ThisWorkbook.Worksheets("Console").Range("Shows").Cells(1, 1).Value)
ThisWorkbook.Worksheets("Template").Copy
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name =
newSheetName

'Copy the show values; formatting has been transferred via the
template.
ThisWorkbook.Worksheets(newSheetName).Range("A1:I102").Value =
ThisWorkbook.Worksheets("Latest Snapshot").Range("A1:I102").Value

End Sub
......................................................(Module1)
Option Explicit
Public RunWhen As Double
Public RunIntervalSeconds As Integer
Public Const cRunWhat = "DataRefresh"
Public ShowNumber As Integer
Sub EngageWeb()
'Starts up the scheduling process: in the nature of an initialisation
routine.

'Check the workbook has valid sheet names allowing updates to be stored
sequentially.
If Not WorkSheetNameIntegrity() Then
MsgBox "Show number inconsistent with existing worksheet names."
Exit Sub
End If
'Change some of the EXCEL colour palette to match Betfair's show
scheme.
SetColourScheme

'Set the data acquisition interval from the parameter worksheet cell.
RunIntervalSeconds =
ThisWorkbook.Worksheets("Console").Range("RefreshInterval").Cells(1,
1).Value

'Determine the last show number.
ShowNumber = ThisWorkbook.Worksheets("Console").Range("Shows").Cells(1,
1).Value

'Obtain the latest show
DataRefresh

End Sub
Sub DataRefresh()

'Acquire data, parse out the latest prices and store them away.
ShowNumber = ShowNumber + 1
ThisWorkbook.Worksheets("Console").Range("Shows").Cells(1, 1).Value =
ShowNumber
GetExchangeShow

'Prime the next refresh, up to 500 shows.
If ShowNumber < 500 Then
StartTimer
Else
DisEngageWeb
End If

End Sub
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, RunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat,
schedule:=True
End Sub
Sub DisEngageWeb()
'Shuts down the scheduling process: in the nature of a closing routine.
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat,
schedule:=False
ThisWorkbook.ResetColors
End Sub
Function WorkSheetNameIntegrity() As Boolean
'The shows are stored in sheets having sequential integer names so we check
that ...
'... the stated number of shows gathered is not less than the maximum sheet
name.
'If there are no numeric sheet names the starting show number is set to 0.

Dim idx, Max, No As Integer

Max = -1
For idx = 1 To ThisWorkbook.Worksheets.Count
If IsNumeric(ThisWorkbook.Worksheets(idx).Name) Then
No = Val(ThisWorkbook.Worksheets(idx).Name)
If No > Max Then Max = No
End If
Next
If ThisWorkbook.Worksheets("Console").Range("Shows").Cells(1, 1).Value
< Max Then
WorkSheetNameIntegrity = False
Else
WorkSheetNameIntegrity = True
If Max = -1 Then
ThisWorkbook.Worksheets("Console").Range("Shows").Cells(1, 1).Value = 0
End If

End Function
Sub SetColourScheme()

'Alice blue for the market name.
ThisWorkbook.Colors(37) = RGB(217, 232, 234)
'Light and steel blues for back prices.
ThisWorkbook.Colors(33) = RGB(210, 225, 233)
ThisWorkbook.Colors(41) = RGB(177, 201, 216)
'Pinkish for lay prices.
ThisWorkbook.Colors(44) = RGB(234, 203, 219)
ThisWorkbook.Colors(40) = RGB(228, 189, 208)

End Sub
......................................................(Module2)

Option Explicit
Function GetExchangeData(sURL) As String
'Extract the HTML text containing the event data from the Betfair market
page ...
'... using the Microsoft Internet Controls (enable in
VBE/Tools/References).

Dim IeApp As InternetExplorer
Dim IeDoc As Object
Dim lStartSecs, lElapsedSecs, lTimeOutSecs As Long

'In case an unexpected error occurs ... say the user accidentally
closes the show web page.
'The string returned is an error message in place of an event name, one
without contestants.
GetExchangeData = "p.m_M, 'Data collection failed.'"
On Error GoTo cl

'Create a new instance of IE & make it visible: some things don¢t work
unless it¢s visible.
Set IeApp = New InternetExplorer
IeApp.Visible = True

'Call up the page required ... give it time to load (90% of the refresh
interval).
lTimeOutSecs = RunIntervalSeconds * 0.9
lStartSecs = Timer()
IeApp.Navigate sURL
Do
lElapsedSecs = Timer() - lStartSecs
If lElapsedSecs < 0 Then lElapsedSecs = lElapsedSecs + 60 * 60 * 24
'period spanning 12A.M.
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE Or lElapsedSecs >
lTimeOutSecs

If lElapsedSecs > lTimeOutSecs Then
GetExchangeData = "p.m_M, 'Web access timed out.'"
Else
'Store the page's Document Object ... and see if it looks like a
market has loaded.
Set IeDoc = IeApp.Document
If InStr(IeDoc.documentElement.innerHTML, "p.m_M") = 0 Then
GetExchangeData = "p.m_M, 'Invalid market ID.'"
Else
'Grab the text (inside the first script).
GetExchangeData = IeDoc.Scripts(0).Text
End If
Set IeDoc = Nothing
End If

'Close the IE window and clean up storage.
IeApp.Quit
Set IeApp = Nothing

cl: lStartSecs = 0 'Couldn't find a NULL statement!

End Function
..............................................(Module3)
The code below is from sheet 3 (Console) which starts/finishes the data
gathering via a command button.

.....................................................

Private Sub CommandButton1_Click()
EngageWeb
End Sub
Private Sub CommandButton2_Click()
DisEngageWeb
End Sub

 

[Guest]

Try sometihng like:

Option Explicit
Public iCol as Integer

Sub The_Sub()

Dim Source as Range
Dim Dest as Range
Dim MaxCols as integer

MaxCols = 13 '<===== Adjust for the number of columns you want - C to
O=13
Set Source = Sheets("Show").Selection
Set Dest = Sheets("Chartdata").Range("B2")
Source.Copy
Dest.Offset(0,iCol).PasteSpecial xlPasteValues
iCol = iCol+1
If iCol = MaxCols then
'reset back to zero
iCol =0
End If
End Sub

This is all we need to do the copy/pasting. As you can see, rather than
selecting sheets and ranges I have just told the code what range to copy from
(Source) and where to paste it (Dest). The Offset iCol variable will also
allow us to write the code only once instead of 13 times as you have done.
ICol starts with a value of zero and needs to be a global variable (Declared
Public before any subs are written in the module) in order for its value to
be retained outside of the macro that uses it. If it is dimmed inside of
The_Sub, its value is lost when The_Sub ends.
Now we need to call this from your main sub.
It looks like you should call this right at the very end of your main sub,
if you want it to copy/paste everytime.
One issue that has not been dealt with is : what happens after your
program is run 13 times? -5.5 minutes at 30 second intervals? Do you want to
save the data in the Chartdata sheet? Do you want it overwritten - I think
this is what will currently happen. Do you want to save the Chartdata sheet
and create a new Chartdata2....x sheet? ????

Sub GetExchangeShow()
'Extract and store Betfair price shows.

Dim sURL, sHTML As String
Const sBFMarketPrefix As String =
"http://www.betfair.com/betting/LoadMarketDataAction.do?mi="

'Mask screen redraws during automated operations.
Application.ScreenUpdating = False

'Clear the work area.
ClearSnapShot

'Construct the full Betfair market page name from the fixed and
variable parts.
sURL = sBFMarketPrefix &
ThisWorkbook.Worksheets("Console").Range("MarketID").Cells(1, 1).Value

'Extract the show text.
sHTML = GetExchangeData(sURL)

'Parse the betting fields from the HTML text ...
' ... and write the show into the Snapshot worksheet.
CreateShow (sHTML)

'Store the show in its own permanent worksheet.
StoreShow

'Visibly reposition to the control worksheet.
Application.ScreenUpdating = True
ThisWorkbook.Worksheets("Show").Activate

' ***********
' Call the routine to copy/paste data to the Chartdata sheet
The_Sub
'*************
End Sub

 

[Saxman]

I would suggest you "learn" on a new test workbook, rather than your
main project.

It is really quite simple. Try the following:
1 Select and Copy the code found below:
2 Open a New wbk
3 Alt+F11 to open the visual Basic Editor
4 Insert>Module
5 Click in the right panel of the new module
6 Ctl-V to paste the following code.
7 Resize and/or move the VB editor screen so you can see the upper
left of the new worksheet behind the VBE.
8 Click so your cursor is anywhere in the middle of the FIRST SUB.
9 Step thru the code by repeatedly clicking the F8 key.
Observe the action as you step thru the code.
Note that the first and 2nd time thru the code it does not run the 2nd
macro, but it will on the 3rd run-thru, and will do so every 3rd run.

I have done the above the above and it appears to run through 'The_Sub'
macro without any problems as stated before, but I am totally confused by
the following code which is now in Module5 as instructed. (See my full
code to you earlier). My copy/paste code ('The_Sub') is in Module4.

1. Is this a completly new macro below, as there is no Sub MainMacro() in
the original code, or do I have to rename it?

2. The cell references refer to what? Are they actual references or
examples which I have to modify?

There are 5 sheets in the spreadsheet.

'Console'. Where one sets the time in seconds for gathering the data and
for entering a web reference number for the required data and for
connecting/diconnecting from the web using 'Internet Explorer'.

'Show'. A replica of the web page data (but in spreadsheet format, not
HTML), from whence the copying is done.

'Latest Snapshot'. Similar to the above, but with less formatting.

'Template' Template for 'Latest Snapshot'.

'Chartdata' Contains the pasted data from 'Console' for use by the graph
embedded on the same worksheet.

Running the macro below gives a compile error 'Expected 'End With''

Code:
----------------------------------------

Option Explicit
'Declare public variable
Public giCount as Integer


Sub MainMacro()
giCount = giCount +1
With Activesheet
.Range("A1:B7").ClearContents
.Range("A1")="This starts my main macro."
.Range("A2")="This is also part of my main macro."
.Range("A3")="Now I'm going to check the Counter."
Msgbox " The counter is now at: " & giCount
If giCount>2 then
msgbox "That is high enough to call my other macro. So..."
MySubMacro 'just type the NAME of the macro to call it.
'reset the counter
giCount=0
Msgbox " I just set the counter back to zero."
Else
Msgbox " I'm waiting for the counter to reach 3, before
calling my other macro."
End if
'more code:
.Range("A6")="Now I am continuing with my main macro."
.Range("A7")="This is the End of my main macro."
End Sub


Sub MySubMacro()
Range("B4")="This starts my 2nd macro."
Range("B5")="This is the end of my 2nd macro."
Msgbox"Now I will jump back to my first macro."
End sub

Thanks again,

I am most grateful, but can see myself resorting to doing this all manually
somehow. It's now beginning to look like the easier option!

 

[Saxman]

On Sun, 18 Dec 2005 07:51:02 -0800, cush wrote:
Thanks for your patience.

The spreadsheet that I have is probably too 'heavy' for what I require, but
beggars cannot be choosers! I am trying to adapt it for my own needs. The
workbook is mainly for observing horse racing (or other events) shows/odds
before the 'off'. I need to observe this data for about 15 minutes before
the 'off', hence the 15 shows. This data can fluctuate quite rapidly, so I
thought a graph would be more effective. This data would become redundant
after the event, so it could be cleared automatically of manually ready for
the next event.

If you go to the Betfair website at:-

http://www.betfair.com/?pi.localeId=en_US&pi.regionId=GBR

In the column 'Racing' you will see the forthcoming races. Right click on
any of these and select properties. This give the address of the data,
something like '4286794'. Clicking on any of these races gives the current
back/lay odds. It is the back odds that I am interested in. Just the
odds, not the amount available in £, so this would require parsing within
the spreadsheet. This data is updated every minute by default.

I'm sure something could be written far simpler without all the formatting
attached, probably on one or two sheets maximum. It's just the raw data
that I require for a graph, but it has to be updated as already stated.

I will try what you have given below.

Try sometihng like:

Option Explicit
Public iCol as Integer

Thanks.