Loading next day races automatically

[Wacom78]

Can anyone help please. I have my GRUSS Set up with auto link to Excel, each days UK and Irish racing on separate tabs. My Excel sheet moves from race to race no problem.

However, each morning I have to close GRUSS and re-open to load the next days races. Can anyone help provide some VBA code to load next day races automatically at like 6am for each tab

[tradeaj71]

viewtopic.php?f=8&t=3558

Gary has given an example of how to do this, albeit at midnight each day.

[Wacom78]

Thanks for reply, I had seen the example sheet - but don't think it works when I have multiple tabs for different courses?

For instance, today there are 4 tabs for 4 meetings, tomorrow there might be 5, then Thursday there might be 3.

[Captain Sensible]

Have you tried amending Gary's example sheet and amended the -3 that's sent to Q2 with a -4 as that's the reload code you need for multiple tabs.

http://www.gruss-software.co.uk/Betting ... _other.htm

[Captain Sensible]

You'll obviously need tabs already linked to excel as it'll only fill the tabs with markets. You can use the log multiple heets link to open as many as you think may be needed and no problem if some are empty.

[Wacom78]

I will give that a go thanks

[Wacom78]

I assume this code only needs to go on one sheet and not all of them?

[Wacom78]

I already have some code on each sheet under worksheet change - inserting this code gives me a "Compile Error"

Ambiguous name detected: Worksheet_Change

Should I insert this code at the end of my routine?

[Captain Sensible]

You just need to incorporate the refresh routine within your current worksheet_change routine for the worksheet linked to the first tab. Just the bits

Code: Select all

        If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            Range("Q2").Value = -4
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Range("Q2").Value = -5
            End If
        End If

And the routine to reload at 6 in your module

Code: Select all

Public triggerQuickPickListReload As Boolean
Public triggerFirstMarketSelect As Boolean

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

[Captain Sensible]

You can write a different routine if it's easier as all you're doing is sticking -4 in Q2 to reload the markets into separate tabs and follow up with -5 in Q2 so the program moves to the first market in each quick pick list. Gary's example was just an example of how to do it but obviously if you already had coding to stick in -3 at 6am just switch that to -4.

[Wacom78]

Ok thank you - got into a right tangle and stopped my BOT by adding the new code in - I think I will set it up as a separate Macro and call it that way

[Captain Sensible]

Calling a separate routine is probably the best way as you can then tweak that easier without messing up you main code.

Also Gary's code all runs originally by starting the routine using Workbook_Open() under Thisworkbook and then that simply loops itself so you'd need to include that if using Gary's method.

Code: Select all

Option Explicit

Private Sub Workbook_Open()
    Application.OnTime TimeValue("06:00:00"), "loadQuickPickList"
End Sub

Just remember that whilst the -4 refreshing the quick list only needs to be in the worksheet for the first open tab, the -5 to reload all tabs to the first market in the QPL will need to be in each worksheet

[Wacom78]

Thanks for the feedback, will see what I can come up with and test tonight / tomorrow

[Wacom78]

I've basically used what you put here in the "ThisWorkbook" section

Code: Select all

Public triggerQuickPickListReload As Boolean
Public triggerFirstMarketSelect As Boolean

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

I have created a Macro called Refresh - which is triggered by the above - that code is below. Do you think this will work?

Code: Select all

Sub Refresh()

   If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
Sheet("Sheet1").Select
            Range("Q2").Value = -4
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Range("Q2").Value = -5
            End If
        End If

Sheet("Sheet2").Select
Range("Q2").Value = -5
Sheet("Sheet3").Select
Range("Q2").Value = -5
Sheet("Sheet4").Select
Range("Q2").Value = -5
Sheet("Sheet5").Select
Range("Q2").Value = -5
Sheet("Sheet6").Select
Range("Q2").Value = -5

End Sub

[Wacom78]

That didn't work at all - back to scratching my head with this one.

I already have code in each sheet which triggers my bets and moves to the next race automatically, so I don't know how to refresh the markets at 6am as well as do what I'm already doing. Is anyone able to help how best to do this