How do I save external data in Excel?

[Ankur Bhatnagar]

I use 'Get External Data' 'From Web' to pull an HTML table of single row from
a website into my Excel file. In 'External Data Range Properties' in 'Refresh
Control' I have set it to 'Refresh every "1" minutes'.

I wish to save new data from the sever in every refresh in my Excel file,
one row above the previous so that the latest data arrives in the top row and
the previous data is pushed down.

What is the best way for doing this? Is there a way I can trigger a macro on
every refresh?

Thanks in advance!

 

[Don Guillett]

Put =now() somewhere on the sheet. right click sheet tab>view code>insert
this>name your copy from range

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("lasttrade").Copy
Range("a1").Insert Shift:=xlDown
Application.EnableEvents = True
End Sub

to copy to the last row
Private Sub Worksheet_Calculate()
lr = Cells(Rows.Count, "f").End(xlUp).Row + 1
Application.EnableEvents = False
Range("lasttrade").Copy Cells(lr, "f")
Application.EnableEvents = True
End Sub

 

[Ankur Bhatnagar]

Don,

Could you please explain me how to implement your suggestion in a step by
step way?

I have done the following:

1. Entered the formula NOW() in cell A1 on Sheet2.

2. Did Right Click on Sheet2 > View Code and pasted your code.

3. On Sheet2, A3, I set up the Web Query for External Data (Get External
Data > From Web) and set the refresh rate to 1 minute.

Now what steps should I take next? Also, I keep getting this error message
dialog box in 'Microsoft Visual Basic' application: "Compile error: Ambiguous
name detected: Worksheet_Calculate" with OK/Help buttons.

Thanks for responding!

 

[Don Guillett]

You should NOT have put in both calculated events. The first did as you
asked but if you wanted the other you could use INSTEAD.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.