How do I save external data in Excel?

  • Thread starter Ankur Bhatnagar
  • Start date

A

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!
 
Ad

Advertisements

D

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
 
A

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!
 
Ad

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top