QUIZ: Is this possible? If so, how ;)

[DavidVernon]

Hello, thanks for looking at this for me. I have laid out the scenario as best I can below (apologies for the poor text formatting).

I've got a series of individual files (all formatted exactly the same) containing data. These are located in specific directories.
I have one consolidation file, that has hard-coded links to the data contained in the individual files.
The consolidation needs to take place on the 1st working day of every week (UK Calendar) at 4pm GMT.
The manual operation to do this is to simply open Excel, open the consolidation file, enable macro's, update links, calc-all, save the file, email the consolidation file to specific userID's, close the file, close Excel.
I want to be able to do this completely automatically, without any user interface.
All files are available over a network drive to many users - there will always be someone online at 4pm, but, I may not be.
I would prefer if the 'host-user' did not have to interact with the process at all.
Is it possible to perform the consolidation as described?

 

[Michaels]

Hello, thanks for looking at this for me. I have laid out the scenario as best I can below (apologies for the poor text formatting).

I've got a series of individual files (all formatted exactly the same) containing data. These are located in specific directories.
I have one consolidation file, that has hard-coded links to the data contained in the individual files.
The consolidation needs to take place on the 1st working day of every week (UK Calendar) at 4pm GMT.
The manual operation to do this is to simply open Excel, open the consolidation file, enable macro's, update links, calc-all, save the file, email the consolidation file to specific userID's, close the file, close Excel.
I want to be able to do this completely automatically, without any user interface.
All files are available over a network drive to many users - there will always be someone online at 4pm, but, I may not be.
I would prefer if the 'host-user' did not have to interact with the process at all.
Is it possible to perform the consolidation as described?

Hello !

You can do this by recording a macro and assigning the macro to a text box, it will do from open consolidation file to saving the file, rest you have to do it yourself.

 

[DavidVernon]

Thanks for replying Shawn
I have managed to find the code that starts from the opening the file all the way to auto-emailing and closing the file.
The bits I'm stuck on are:
The bit that will automatically open Excel at the specified time on the 1st workday of every week; the bit that then auto-opens the file, and, the bit that closes Excel.
It would be a pity to have to do these manually every week
DV