Based on your outline I assume you can clearly define the criteria for the historic records to be retained in the previously processed spreadsheet.
In which case a couple of approaches suggested themselves to me.
If there is a clear time element to this you could keep all the daily reports and simply process all of them as far back as you need to go for each daily run.
If you need time plus something else the report date (or the date being reported) offers (perhaps) time and the 'something else' can be a filter to reduce the total number of records.
Alternatively you could, as part of the batch file, prune the Excel File. I'm sure there would be a way to do this with Excel and or VB but you could also do it with Monarch and write back (with Overwrite) the new version and then append to that using your current process.
Does this offer you any useful ideas?
Thanks for the reply Grant. I was already leaning towards an automated way of pruning the spreadsheet once the main batch file was run. My criteria hasn't yet been defined, however I'm trying to have it commit over a period of time. i.e. any information older the X days gets dropped from the spreadsheet.
To do the daily work the user will already manually be combining several new reports ( I beleive about 25 of them) into one input file, to do this and sort through them on a daily basis if this was to last a week would end up manually combining 175 files. (Which is a little much to do) An automated drp off would be ideal.
Bu your suggestion have given me something to think about. I've not tried to read inan excel spreadsheet as an input to the process to prune the days off. Something I might be able to jury rig and make work.
Thanks for the input. It's helped a lot.
Your pruning could be x-days AND some other criteria if it would help. However I like to keep things simple and unless there is any major advantage for the size of the Excel sheet or the use of it by introducing secondary pruning criteria I would tend to leave it as a date pruning activity.
If all the files the are the same format (or can be processed to the same format) it should be possible to automatically create the single input file - especially if all of the files were resident in the same directory. Obviously there could be a naming issue there but that too should be resolvable in a way that allows Monarch, via the batch file, to do most of the work.
My batch file involvement is somewhat irregular, to say the least, but I'm sure some of the other regulars here have much more input to offer. I seem to recall that there should be a few posts covering the matter of processing multiple files using a batch file in earlier forum posts. I'll see if I can find them.
Here's a reference that seems to relate: