fred3
Member
This seems like a fairly common Excel application where there are lots of sources of data in separate (but identical) files and a Summary is needed.
Large number of Data files:
- each retrieves data daily
- each has an amount of calculation done on the data
- each can run Solver with various objectives (time consuming)
- each is controlled by parameters linked into cells from a "Control File" (which is very simple)
- each Data file has a Private Sub Workbook_Open() that runs when it starts. Actions are controlled by data in cells with links to the Control file.
- time lines
"open and stay open"
"open, update data and stay open or close"
"open, update data, run Solver "n" times and stay open or close"
Typically, there is a daily run of "open, update and close". The files are run using a batch file that opens the files sequentially.
(Opening too many of these files at once hangs everything up).
Control File:
- to provide parameters to the Data files via cell links
Summary File:
- an array of cells which are populated from the numerous Data files via cell links.
- summarize daily (after data retrievals are done).
Objective:
If time were no object, then every time a data file would be opened, it could "open, update data and run Solver 3 times then stay open or close". But the Solver runs take about 2 minutes apiece and overall this takes hours. Having answers to the following questions might help.
Questions:
Either having a cell with external links requires the linked file to be opened or not.
If it does then time is taken opening the other files.
Each Data file has this code segment in the Private Sub Workbook_Open():
I see the Control file open and close each time a Data file is run. Is this necessary to update the linked cells? Or, will (or can) the links be updated without necessarily opening the Control file each time? It looks like an opportunity to save time. Some simple experiments suggest the links can be updated without opening the source file.
So, with my limited experience, I'm left wanting to do this:
"Assure that the links in the Data file are updated, when the Data file is opened, without opening the Control file"
How?
Similarly, with all the links in the Summary file, if every Data file has to be opened in order to update the links then it will take a long time. And, presumably, if the Control file calls for Solver runs then it will take a *very* long time.
I'm trying to get my head around how this all works and how to best structure these files as a system.
Another example:
Using a batch file to run the Data files is straightforward and handy.
It's mostly just a list of file names.
But this shuts down Excel for each Data file that's run.
It seems that a means to provide a list of files that could be opened and closed sequentially without having to instantiate Excel for each one would be preferable.
I can envision a VBA sub which would loop like this:
If list still has names
- get the next filename from the list
- open the file (which will update)
- close the file
End If
Then all I'd have to do is update the list now and then.
Suggestions? (I'm not sure how to code this in VBA).
Large number of Data files:
- each retrieves data daily
- each has an amount of calculation done on the data
- each can run Solver with various objectives (time consuming)
- each is controlled by parameters linked into cells from a "Control File" (which is very simple)
- each Data file has a Private Sub Workbook_Open() that runs when it starts. Actions are controlled by data in cells with links to the Control file.
- time lines
"open and stay open"
"open, update data and stay open or close"
"open, update data, run Solver "n" times and stay open or close"
Typically, there is a daily run of "open, update and close". The files are run using a batch file that opens the files sequentially.
(Opening too many of these files at once hangs everything up).
Control File:
- to provide parameters to the Data files via cell links
Summary File:
- an array of cells which are populated from the numerous Data files via cell links.
- summarize daily (after data retrievals are done).
Objective:
If time were no object, then every time a data file would be opened, it could "open, update data and run Solver 3 times then stay open or close". But the Solver runs take about 2 minutes apiece and overall this takes hours. Having answers to the following questions might help.
Questions:
Either having a cell with external links requires the linked file to be opened or not.
If it does then time is taken opening the other files.
Each Data file has this code segment in the Private Sub Workbook_Open():
Code:
Dim curWorkbook As Workbook
Set curWorkbook = ActiveWorkbook
MyPath = ActiveWorkbook.Path
Workbooks.Open _
Filename:=MyPath & "\__Hour.xlsm" _
, UpdateLinks:=xlUpdateLinksAlways
Workbooks("__Hour.xlsm").Close False
justOpen = ThisWorkbook.Worksheets("Model").Range("D1").Value
'If justOpen=0 then skip all the updates and optimizations and leave the file open.
'If justOpen=1 then update prices and follow the optimization and leave open or close settings.I see the Control file open and close each time a Data file is run. Is this necessary to update the linked cells? Or, will (or can) the links be updated without necessarily opening the Control file each time? It looks like an opportunity to save time. Some simple experiments suggest the links can be updated without opening the source file.
So, with my limited experience, I'm left wanting to do this:
"Assure that the links in the Data file are updated, when the Data file is opened, without opening the Control file"
How?
Similarly, with all the links in the Summary file, if every Data file has to be opened in order to update the links then it will take a long time. And, presumably, if the Control file calls for Solver runs then it will take a *very* long time.
I'm trying to get my head around how this all works and how to best structure these files as a system.
Another example:
Using a batch file to run the Data files is straightforward and handy.
It's mostly just a list of file names.
But this shuts down Excel for each Data file that's run.
It seems that a means to provide a list of files that could be opened and closed sequentially without having to instantiate Excel for each one would be preferable.
I can envision a VBA sub which would loop like this:
If list still has names
- get the next filename from the list
- open the file (which will update)
- close the file
End If
Then all I'd have to do is update the list now and then.
Suggestions? (I'm not sure how to code this in VBA).
