How to quickly update data from another workbook

[Johnb11]

Hello

I run an Excel workbook in my department which takes some of its data from a central Excel spreadsheet run by other Departments. In order to refresh/update the data in my spreadsheet I go on the Data tab, click 'Edit links', then click on the source, then click on 'update values', then click on 'close'. I wonder if there's a quicker way of performing this task? I've tried recording a macro but that didn't work. If anyone knows a quicker way of doing it I would be grateful to know. Otherwise I'll just keep doing it the way I always have.

Many thanks for reading this post.

[Olly]

A macro should work fine. Something like this will update the links from a named workbook:

Please Login or Register  to view this content.

Obviously, edit the source file name.


Or this will update all links in the workbook:

Please Login or Register  to view this content.

[Johnb11]

Thanks very much for your reply. I'll try it at work tomorrow :-)

Update: It worked very well. thank you for your assistance.

[Johnb11]

I was just wondering is there a way of making the sheet update itself automatically, say every hour, or perhaps every time the central worksheet gets updated? Just wondered if that is possible? Many thanks

[Akbaruu]

Hi,

Data->Connection (choose connection) -> Properties -> Refresh every *minute