|
Nearly There! (Revisted)
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 |
Nearly There! (Revisted)
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" wrote: 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 |
Nearly There! (Revisted)
On Sat, 17 Dec 2005 11:56:03 -0800, cush wrote:
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. |
Nearly There! (Revisted)
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 giCount2 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" wrote: On Sat, 17 Dec 2005 11:56:03 -0800, cush wrote: 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. |
Nearly There! (Revisted)
On Sun, 18 Dec 2005 03:46:02 -0800, cush wrote:
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. |
Nearly There! (Revisted)
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 InsertModule 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 giCount2 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" wrote: On Sun, 18 Dec 2005 03:46:02 -0800, cush wrote: 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. |
Nearly There! (Revisted)
On Sun, 18 Dec 2005 03:46:02 -0800, in microsoft.public.excel.programming
you wrote: 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("Show s").Cells(1, 1).Value) ThisWorkbook.Worksheets("Template").Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksh eets.Count) ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Co unt).Name = newSheetName 'Copy the show values; formatting has been transferred via the template. ThisWorkbook.Worksheets(newSheetName).Range("A1:I1 02").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("RefreshI nterval").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, procedu=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, procedu=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 ..............................................(Mod ule3) 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 |
Nearly There! (Revisted)
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" wrote: On Sun, 18 Dec 2005 03:46:02 -0800, in microsoft.public.excel.programming you wrote: 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) |
Nearly There! (Revisted)
On Sun, 18 Dec 2005 05:06:03 -0800, cush wrote:
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 InsertModule 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 giCount2 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! |
Nearly There! (Revisted)
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=...i.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. |
| All times are GMT +1. The time now is 07:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com