Using more than one workbook

[vanbuuts]

Hi, I have been splitting races to separate tabs and trying to use a different workbook for each meeting.

Some of my code is below, and only works on the workbook that is active

The code below is when a race ends and moves to the next race. However it only fires on the active file / sheet.

Anyone have any experience of using VBA calls with multiple sheet / workbooks for different races?

Code: Select all

Range("Z1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R5C26:R60C26)"
    Range("T5:X60").ClearContents
    Range("AM5:AM60").ClearContents

[Daywalker]

Hi Vanbuuts,

I have four excel workbooks open and four tabs in Gruss open. I have different markets loaded into each tab and it works fine. My code in each work book, after checking for 16 columns updated, is enclosed in a With Workbooks (see below).

Does each of your workbooks have a unique name? This prevents any mix up.

Also I have separate instances of Excel for each workbook, rather than opening each workbook from within one excel window.

I've had issues in the past with referencing cells/sheets. When this has happened I've found the best solution is to fully reference things, for example instead of Range("Z1").Select, use Worksheets("Sheet1").Range("Z1").Select

T

Code: Select all

Public Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

With Workbooks("THE NAME OF THIS WORKBOOK").Sheets(Target.Worksheet.Name)   
  '
  'All your code goes here
  '
End With

Application.EnableEvents = True
End Sub


[vanbuuts]

Thanks for your reply, I will give it a go - I did try and refer to the individual sheet but didn;t seem to work (had bets firing in all over the place).

At the moment I am getting "General API error" while trying to access GRUSS so am not able to do any further checking

[Drunkenight]

Yes Betfair is down and has been for almost an hour. Hope it comes back soon.

[vanbuuts]

I can see where the problem is coming from. I use the "mybets" worksheet - and when you have markets that overlap, "mybets" seems to alternate between which horses are placed in the sheet.

Basically they don't stack they try and overlap.

Surely someone must have overcome this? Seems like a software flaw. Is there a way to separate horses from different markets in the "my bets" sheet as mine just alternate between the 2 races that I am betting on simultaneously

[vanbuuts]

AHah! Solved by using Sheet1_MyBets, Sheet2_MyBets etc

[MarkRussell]

I can see where the problem is coming from. I use the "mybets" worksheet - and when you have markets that overlap, "mybets" seems to alternate between which horses are placed in the sheet.

Basically they don't stack they try and overlap.

Surely someone must have overcome this? Seems like a software flaw. Is there a way to separate horses from different markets in the "my bets" sheet as mine just alternate between the 2 races that I am betting on simultaneously

Hi,

If you are logging to multiple worksheets and want to use the MyBets sheet then you need a separate MyBets sheet for each market that you are logging to.
So say you are logging a market to Sheet1 then you should name the MyBets sheet Sheet1_MyBets and then if you are logging another market to Sheet2 the MyBets for that one should be named Sheet2_MyBets and so on.

Regards,
Mark