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

DavidVernon

Board Regular
Joined
Jun 30, 2006
Messages
71
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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top