I have a scenario with a customer who has a folder called Payroll 2018. In that she has 12 files called January 2018...December 2018. Then she has a summary file that pulls in the totals from each of these. So far so good. She now wants to update these for 2019. My thinking is this: Make a copy of this folder and rename to Payroll 2019, rename each of the files in it to January 2019....December 2019 and delete out the 2018 data from it, then go to the summary file and use Data | Edit Links to update the values in the Summary 2019 (which was Summary 2018) to point to the 2019 January - December files. Is there an easier way for her to update all the file names from January 2018...December 2018 to January 2019...December 2019. In all fairness she's happy to do that - I just want to know if there's an easier way - Thanks 🙂
Hi Anne,
It all depends on how experienced your customer is with creating and using scripts, for example VBA, Command or PowerShell scripts. With such you can easily have a new copy of the folder and the files with correct naming etc. I assume you can use VBA to also modify the data path in the queries. If the customer is not used to scripts, then perhaps it is better to stay with the manual copying and editing.
I still do believe it would be easier to have one file per year instead of one file per month, then you can have a generic folder name and don't need to change the path to the data source. All new files in the folder will be included in the query, as long as they follow the same structure, and so forth.
/Anders
Hi Anne
Unless there is a specific reason why you need to copy 12 files and then delete their data, I would just delete data from one file and make the additional copies from that.
As for the renaming part, you can go to the command prompt, select the folder and rename the files from there with the following command:
REN *2018.* *2019.*
Hope this helps.
Sunny
