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?
