Trying to use a linked workbook without opening the source files

Question

I'm trying to create a database that is fed by several hundred files.  Each source file contains a lot of data on several tabs, but all I'm pulling out is the summary information from one tab (it ends up being a 9x24 array for each source...again hundreds and hundreds of these simulation files)...however these files take over 60 seconds to open.  I know how to create links from each source to the database; the problem is opening and updating the destination file.  I do NOT want all the source files to open each time I try to open the database, it would take all day to open and update them all.

The source files are generated by a separate program which automatically updates and saves over the old source file when I re-run any given simulation.  So I want the database to just automatically reflect those changes without having to constantly cut and paste data every time.

Is there a way to have the database update without actually opening each file?  Or is there a way to disable updates until I ask it to update specific links?

This is the current path (with a few minor privacy changes):

=Excel.Sheet.8|'C:\Users\Xxxxx.X.Xxxx\Documents\XXXX\XXX Project #X\ANALYSIS\Database\loop 4\L4 . D5-6-8 . No592 . SN3\L4 . D5-6-8 . No592 . SN3.xls'!'!Distress Summary!R5C5:R28C14'

From what I've read I believe the issue is with the "Excel.Sheet.8|" part...I've tried to get it to work with just the source path, but it won't let me edit this (maybe because it's an array so I can't just change the one cell).  And the cell reference R5C5:R28C14...I don't understand what that is referencing...this particular file is sourced in cells E5:N28 and the destination cells are R366:AA389

I chose excel because a)I'm more familiar with it than Access but b) I need this data to be very flexible and easy to plot up/run regressions on.

Is it possible to make this work efficiently?

Answer 1

Shane, thanks for clarifying the row/column thing, that makes more sense.

The options under Data/Edit Links/Startup Prompt are better routes than giving permissions to the folder, though I have figured out the issue.

I use dual monitors to make working on two excel sheets faster, ironically this is the source of my issue.

Having two excel windows open (as opposed to two projects inside one excel window) is the problem.  Copying and pasting the link between two windows inserts that "=Excel.Sheet.8" in there and thus prompts the destination file to open the source.  Doing the copying and pasting between project files that are in the same excel window works fine and will then update without opening any other files.

To do this excel should show the 'paste special' window that has bubble options to paste all/formulas/values/etc as opposed to the 'paste special' window that makes you choose from a list of pasting formats (picture/bitmap/etc).

Shane thanks for your comments!  Hope this helps save someone else a lot of frustration!

Answer 2

If I understand correctly, you can choose Data, Edit Links, Update Values

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 3

Part of the problem may have been I had set the source folders and subfolders as 'trusted' so it was automatically trying to update without prompts...I've fixed this so it has to ask me before updating.

I tried controlling what gets updated through the Data tab 'update valued'...no luck, it changes everything to #REF!

Answer 4

#REF! would mean that the links were not correct any longer.  Instead of doing the "trusted" folder thing you might look under Data, Edit Links, Startup Prompt and experiment with these three options.

I'm not sure how helpful the above is for you but regarding "And the cell reference R5C5:R28C14...I don't understand what that is referencing...this particular file is sourced in cells E5:N28 "

R5 means row 5 C5 means Column 5 and so on. so R5C5 is E5.  This is just Excel's R1C1 notation.  You could simplify things if you named each refernce range, for example E5:N28 could be called Dept1 (or something that makes sense).  The advantage is that the long reference could be replaced by a slightly shorter and more informative one like:

=Excel.Sheet.8|'C:\Users\Xxxxx.X.Xxxx\Documents\XXXX\XXX Project #X\ANALYSIS\Database\loop 4\L4 . D5-6-8 . No592 . SN3\L4 . D5-6-8 . No592 . SN3.xls'!Dept1

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 5

I know your post is from May 2010, but I had the exact need, and solved it by creating a formula in each cell of the destination workbook that "reads" each named Excel workbook loaded under the same folder, without having to open each source workbook.  The formula relies on only knowing the full path name to the folder containing all of your source workbooks, and that each workbook have the the cell you are extracting data from in the exact tab and row-column, which sounds like our source files.

No need for updating links.  Just run the macro I built.

If you still need help on this, let me know and I will answer it.