BA/Excel refresh puzzler

[Frinck]

Hi all,

I'm really struggling to get to grips with the way BA refreshes Excel worksheets, and was hoping to get some help...

I already use the following code (originally posted by Mitch I believe) in a Worksheet Change event:

If Target.Columns.Count <> 16 Then Exit Sub

However, when my VBA code (to capture historical data) looks at each runners odds, some of them are 0, implying that the refresh is not yet complete. Naturally, each 'scrape' is then worthless, since some nags have a price and others don't.

By utilising the If statement above, I thought the refresh would then be complete, but can anyone confirm this please? It would be really helpful to understand exactly how BA does refresh Excel as this would save me from going bald prematurely!

Any hints/tips/explanations would be much appreciated,
Frinck.

[BlueBoy]

I have studied the passing of data from BA to excel and, although I dont claim to be an expert i think that you will find that a refresh consists of these 'transfers'

1. The range Q1:X1 - the headings "trigger" to "profit/Loss". The colums count wil be 8

2. The range A1:X?? - the main body showing market details and selections. The column count will be 16.
?? will be the number of selections in the market plus 4 (themarket details and the heading line beggining Selection
Name)

3. The range T5:X?? - your current position for each selection in the market( Bet ref etc). ?? is as above. The column
count will be 5.


I hope this helps

[Mitch]

If you want to see for yourself how BA updates Excel, use a new workbook and export data to it from BA.

Enter something in any cell in column A a few rows under the last horse, then put this in the worksheet code.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Range("A65536").End(xlUp).Offset(1, 0) = Target.Address
    Application.EnableEvents = True
End Sub


I think that's right although I can't check it for myself at the moment.

It will keep repeating but just let BA refresh a few times then stop it. You will see a pattern repeat in the addresses which will show you exactly what gets sent and in what order.