Trying to record historical data

[Turnup4thebooks]

Hi folks,

I'm sure there's a simple answer to this but it's beyond me.

I'm trying to write a bit of vba code that will copy and paste data from ba to worksheet every second or so, in order that i can study the price movements at my leisure. The basic operation is simply:-

Range("a1:x9999").Select
Selection.Copy
Range("a25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I have the ba trigger on and cells a1 to x(field size+4) get updated with the next bit of data (i am using a refresh rate of 1 second) and the process repeats itself until the scheduled start time.

The only way I've been able to do this so far is by inserting a message box in the code and just sitting there continually pressing the return key. If I don't do this the macro doesn't seem to give ba the chance to log the next refresh of data on to my worksheet.

I thought using the wait method might do the trick, i.e:-

Do While tl > 0.015 And beyond = 0
newhour = Hour(Now())
newminute = Minute(Now())
newsecond = Second(Now()) + 2
waittime = TimeSerial(newhour, newminute, newsecond)
Application.Wait waittime

Range("a1:x9999").Select
Selection.Copy
Range("a25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

tl = Range("d2").Value
Loop
But this doesn't seem to allow the ba data to be logged on to my worksheet either.

As you can probably guess, VBA is new to me, so any suggestions would be graefully received.

Cheers

[PeteB]

You can make your code run each time BA updates the sheet, by putting your code in the Worksheet_Change event - search the forum for "Worksheet_Change" and you will see the different things people are doing - the usual technique is something like this:

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

   ' BA does several updates - we want the one with 16 columns
   If Target.Columns.Count <> 16 Then Exit Sub
 
   ' Put your copying code here

End Sub



BA can't log to Excel if Excel is busy (even if it is only busy waiting!)
(If you have time-consuming processing in Excel then you can call DoEvents at appropriate intervals which will allow Excel to stop processing your code and respond to BA before resuming your code)

By the way, you don't want to do .Select, as this is slow in Excel - instead just do:

Code: Select all

        Range("a1:x20").Copy
        Range("a25").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


(I've changed it to only copy 20 rows of data, otherwise you will also copy your log. You will also need a row counter (declared outside this function) that you increment each time you copy, so each paste is to a new row - if you copy 20 rows of data, you will need to increment the counter by 20)

This copy will trigger the worksheet_change event again, and so put you in an infinite loop, so you need to stop Excel responding to Events during your copy - so in fact you will do this:

Code: Select all

        Application.EnableEvents = False
        Range("a1:x20").Copy
        Range("a25").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.EnableEvents = True


[Turnup4thebooks]

Thanks for coming up with such a succint answer so quickly Pete B.

I still have a couple of stupid questions though:-

1. How do you call this sub in the main body of the program (I know, I really am a beginner at this stuff), or does the update automatically call the sub.

2. You say "I've changed it to only copy 20 rows of data, otherwise you will also copy your log." Umm, where is it, what is it and why would it matter if I copied it?

Thanks again

[PeteB]

You're welcome

1. Worksheet_ Change will run whenever the sheet is updated (manually, or by BA, or anything else), so you don't need to call it
We've added a test so that we only actually do anything if an update is a block 16 columns wide - which is what the BA price update is

(might be worth reading up a bit - google for "excel event model" - in fact I did this just now and the second result takes you to a preview section of the Eric Carter VSTO book - a great book - well worth reading this section)

So you probably won't have a "main body" in your sense - or even if you did, you would have to hang it off an Excel event like a button click or a sheet change

2. You were pasting your results to a25 - so I called this your log - i.e. where you are storing your price history. And you were copying a1:x9999, which would include the results already pasted at a25, so I cut this down to copy only a1:x20

[Turnup4thebooks]

Thanks again Pete.

You're right, I do need to read up on this. I have Excel VBA in easy steps by Ed Robinson and I'll check out Google aswell as you suggest.