Using Import External Data

gordon3d

New Member
Joined
Mar 26, 2004
Messages
37
Is there anyway of importing data from other Excel spreadsheets (scattered throughout the company network) efficiently and WITHOUT having to open these sources? I am at present utilising Import External Data function with "Update every 30minutes" and that this spreadsheet is always open. However, I get various errors of "cannot find ...etc" when the auto-update looks for sources that are not open but a few more clicks on the "Update Now", it then open these sources as"Read Only", updates but leaves the source spreadsheet open. Further auto-updates comes from these opened spreadsheets which has data that may not be current.

Idea: What I have to do is consolidate various data from various user's spreadsheet (and Access eventually), put them in a spreadsheet as a central spreadsheet that has calculated fields and from which various external generated reports can get its information.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
You can use External Data > new database query to set up similar imports which do not require the file to be opened to import the required data, nor to set up the query. Much like external links in formulas the query can refresh the file while the source file is closed. You have the same time-refresh options on a MS Query as you do on the Import External Data option.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,056
Messages
5,569,953
Members
412,299
Latest member
agentless
Top