How do I get imported data to refresh each time the Excel file opens?

[kw127533]

I have a template that will be used to track data across multiple outside spreadsheets. The outside spreadsheets will be imported to different tabs of the template.

I know that once the data is imported, the connection properties can be adjusted so that the data refreshes when the template file is opened, however, this is tedious as new data imports will be added constantly.

Is there a way to get the template file to refresh all data without adjusting the connection properties of each set of imported data? I know the 'Refresh All' button is another option but the imported data changes day to day so again, this is not ideal.

[twinnyfo]

Doesn't the "Refresh All" refresh all links? How does that not capture the day-to-day changes? I am a bit confused.

[kw127533]

It does but I would like the data to refresh automatically. Some of my coworkers who will be using the spreadsheet are not that familiar with Excel so I'd like to automate things as much as possible.

[twinnyfo]

I thought there was a setting to automatically update your links when the Spreadsheet was opened, too. Let me look into it....

There might also be some ways to do it programmatically in VBA.

[twinnyfo]

I am able to refresh links whenever I open the Spreadsheet.

BTW, Excel is not usually the best Application for sharing data across a network. it can turn into a disaster quickly and if anything breaks or crashes, you are usually a lot out of luck...

[kw127533]

Yeah I thought there was a setting for that also but I can't seem to find it.

It's not really data sharing, it's more of a tracking device so one person can monitor their work across multiple spreadsheets.

[twinnyfo]

Is it one master spreadsheet that links to multiple children? Do all have access to the master? Trying to figure why multiple users would need access to the same spreadsheet in such an environment. Again--asking for problems.

[kw127533]

Yes, for example each client has a spreadsheet associated with it to track tasks and their completion dates. We wanted a centralized place so that each consultant could track tasks across all clients by looking in one place.

So the template is a personal tool to be used by each consultant. Only one consultant would be accessing the file at a time.

[twinnyfo]

Again, when I open a linked spreadsheet, it allows me to 1) either update the links at that time, or 2) automatically update when I open. If only one has it open at a time, it should be fine.

(I think)

I guess I'm not exactly sure the question here, as this should be automatic to start. Are you saying yours does not update automatically?

[kw127533]

Hmmm...I'm not getting that option when I open the file. I remember at my old job we had spreadsheets with that prompt when you opened the file, so I guess my question is how do I get the spreadsheet to prompt a refresh when it is opened? I'm using Excel 2010 by the way.

[twinnyfo]

File, Info....

It should have notes on linked documents and options for setting preferences.

[kw127533]

I'm still not finding anything under the 'File' tab...can you be more specific as to where you're seeing this?

[dalecharley]

I learned about that a few years ago but forgotten. Yet i remember it back. Have a look at http://softwareslock.blogspot.com/

[twinnyfo]

KW,

What version of Excel are you using?

[kw127533]

I'm using Excel 2010.

[twinnyfo]

Take a look at the attached.

Hope this hepps!