Loading next day races automatically

[Wacom78]

Hello again Captain, I'm afraid this still does not work.

I've removed all the previous code, loaded as you posted in sheet 1, saved the file, then when I set up the quick link it goes through all the tabs as usual - but nothing pulls through into Excel. Each tab shows frozen races from 14th August (last time I saved the file).

Each race tab shows its connected to separate Excel tabs - but none of todays races pull through??

[Captain Sensible]

That's why it's usually best to simply start from scratch and keep backups. Hard to know why your sheet isn't linking to excel, maybe it's corrupt. If you remove the Quick pick refresh code does it work? Maybe add this code to a module and run it as you may have saved it in some suspended state. The code will simply reset events and clear any saved variables.

Code: Select all

Sub Reset_sheet()
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
   MsgBox "events reset"
  End
End Sub


[Wacom78]

Thanks again for your reply, I know its nothing to do with the file being saved in a certain state, as it works when the code isn't modified (each day I am not saving the file - simply re-using it as it was from 14th August save).

However, today, I have tried what you said (ie used the new code in sheet 1) but LEFT the code below in the module and just removed the code from "This Workbook". I am not sure what I should have removed to begin with - but now, I have now code on worksheet open, but I do have the below code in a module and your new code in my sheet.

The good news is the file is connected to Gruss and taking live info - what remains to be seen is if all my routine works as previously, and the golden part if at 6am tomorrow the new days races load and connects to the first race., I will keep you updated

Code: Select all

Public triggerQuickPickListReload As Boolean


Public Sub loadQuickPickList()
    triggerQuickPickListReload = True
    Application.OnTime TimeValue("06:00:00"), "loadQuickPickList"

End Sub


[Captain Sensible]

You don't need to wait til 6 every day you can simply change the time to whatever it is now to test it.

The last code I posted is to completely replace the worksheet open and loadQuickPickList routines as I said in the post you'd need to remove all the previous code and start from scratch. I see you're also reerencing BA's com features with the line Dim ba As BettingAssistantCom.ComClass so maybe some of the routines that's using are interferring.

VBA can be a bit of a learning curve and it might be better to simply get someone to write the sheet for you. There's a find a developer section on the forum and user Osknows is very good, mght be worth PM'ing him to see his charges as he'd write a very efficient sheet to whatever specs you wanted.

viewforum.php?f=12

[Wacom78]

Hi Captain, thanks for your post - it almost worked!

I waited til 6am, and today's races loaded, and the the first race selected BUT - only in the first 3 tabs.

Yesterday when I set up the quick link, there were only 3 races, today there are 5. Sheets 4 and 5 are both linked, but they failed to select the first race

[Captain Sensible]

Have you linked all those sheets 4-5-6 using the excel log multiple sheets already? If so you'll have to email the admin see if they can see why the tick box isn't loading your markets as it may be a bug

Untitled1.png

[Wacom78]

Hi Captain, I use the multiple quick link - so don't get that pop up box that you show when linking individually

[Captain Sensible]

That's a global option, although I see you said it's ticked on the 22nd. Mabe have a look at viewtopic.php?f=5&t=10617&p=53699 to check you're linking the sheets properly

[Wacom78]

All seems to have worked

Need to keep an eye on it obviously but so far so good. the only other thing I need to ask about is "turning off" a sheet / venue once its completed. My CPU usage is quite high, and as I want this to be set and forget it will run all the time. Right now for instance, there is only one race left - at Wolves, but all other sheets are still linked and refreshing. Is there a way to turn off sheet refreshes when all races have completed for the day?

[Captain Sensible]

Can't 'disconnect' as you'd then lose the connection to excel and BA wouldn't recieve any requests to reload markets etc. Most people just write routines to adjust the refresh rate via Q2 and turn off streaming or lower refresh rates when they're not needed. The max you can set is about 1000 seconds so 1 refresh every 16 mnutes isn't going to cause any problems. I've always found BA to be very memory efficient, maybe you need to look at optimising your routines if it's high or using up resources.

[Wacom78]

Hi Captain, no don't get me wrong, i don't want to physically disconnect, just turn off the routine as it keeps running.

This part, which records the prices and time keeps running. I'd like this to stop if F2 is closed. Would it be s simple as putting OR if [F2] <> Closed in the part that shows if [A1].Value = Mymarket?

Code: Select all

  If Target.Columns.Count <> 16 Then Exit Sub
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual

    If [A1].Value = MyMarket Then



       Range("AF3").Value = switched
       

       
    If Range("AA4").Value + Range("AB1").Value <= Time() And Range("E2").Value = "In Play" Then
        Range("AB4:bz60").Value = Range("AA4:bz60").Value
        Range("AA5:AA60").Value = Range("O5:O60").Value
        Range("AA4").Value = Time()
    End If


     
       
        Else
       
        MyMarket = [A1].Value
        Worksheets("Sheet1").Range("AA4:bz60").Value = ""
        switched = "No"
       
     
    End If

    If [Y2] = "OK" And switched = "No" _
     Then
    switched = "Yes"
    GoTo Switch_Market
    End If

[Captain Sensible]

Yep you can simply add an additional AND condition to whatever you want to stop or use Goto to bypass code

[rluk]

Hi, is there a way to load more than one day's races through this method?

I live in Australia so when I use "-4.1" to update the Quick Pick List it will only update it to midnight Australian time. I have setup my workbooks to update the Quick Pick List just after midnight so I get the rest of the UK day's racing but I lose the trading window between the last race before midnight and midnight for each venue. Not a major crisis but would like to fix if I can.

[Captain Sensible]

Any reason why you don't use -4 and just have it load up the default markets set for Auto load and have the auto load markets set for UK and AUS races.

I don't have "Only load today's events" in preferences and have a mix of UK and AUS in my sheet but I don't have them in separate tabs. Alternatively why not run two instances of BA one doing UK one doing AUS, I have more than one running different sheets with no problem.

[rluk]

Ahhh, I didn't even know that "Only Load Today's Events" existed. Unticked it and solved problem.

I have a separate tab for each venue and each tab is linked to a separate instance of Excel. I maintain one control instance of Excel, this one triggers the Quick Pick refresh in BA and all the other tabs and Excels downstream update from that with no other code needed.