Looking for ways to shorten daily process for my data

[ExcelAtEverything]

Hello! I'm nearing the end of a sales tracker I've been working on, and wondering if I can make daily life easier on the end user.

My tracker is built on data which is stored in Excel files located in a designated folder on my desktop. That folder has 6 subfolders containing different sets of Excel files. 3 of these folders receive new Excel files each day, and the other 3 each year on Jan 1. Each day, the user will need to run 12 sales reports, which are all generated as Excel files from their POS system. It's basically running 3 separate reports for each store (of which their are currently 4 stores with more coming soon). So each day I drop (soon someone else will drop) 4 new Excel files into each of 3 folders.

The data files are accessed by my sales trackers as follows: Desktop folder>>Power Query>>Data Model>>Power Pivot>>Sales Tracker​

Currently, I am not just able to download the files & drag them into my holding folder for this report. After logging into each store in the POS and generating the reports & downloading them from the POS into my Downloads folder:

1. I have to open each file, click on "Enable Editing" button at top, then re-save it into the designated matching folder.
2. There is a hidden tab/sheet called "Package Data" which must be unhidden each day in one of the reports (hidden, not very hidden. And since there are currently 4 stores, that means I must unhide that tab 4 times before re-saving it each day.

As many of you who have already helped me know, I'm still fairly new to Power Query/Pivot, and would describe my VB skills as Novice- limited to recording & light editing of macros. So my question to this community is, do you see an opportunity for me to shorten this process each morning? I feel like there must be a way to automate the unhiding of the sheet using VB, and not sure about the rest. I'm currently trying to find these answers on my own but would welcome any suggestions and be grateful for any assistance in implementing anything that helps.

Thank you.

 

[Tupe77]

I thought I could just tell them to create the same files on their desktop and it would work. Is that not correct?

Really hard to say reliably. If the macro and the file being processed are in the same folder then it may work(using that as default), if they are in a different folder then it is unlikely to work. It should be tried, I can't answer directly.

If it uses full path, then it not work.

Of course, that too is solvable, but requires more planning.

 

[Tupe77]

So then what location do you think I should put it in?

Usually some network location, but macros, queries and trusted location may not be well suited over network.

In this case, you should put the folders at the root of a disk part like "c: \", but if there are other drive IDs then preferably one without
operating system.
If the machine has more than one physical disk drive, then for one that does not have an operating system.

The exception is if there is an ssd and a mechanical hard drive then definitely to ssd disk.

It is easier to share and find files at the root of the drive than in the user folder.
BUT they don’t have to be changed, changing just would probably simplify things.

 

[ExcelAtEverything]

Really hard to say reliably. If the macro and the file being processed are in the same folder then it may work(using that as default), if they are in a different folder then it is unlikely to work. It should be tried, I can't answer directly.

If it uses full path, then it not work.

Of course, that too is solvable, but requires more planning.

Thinking about this. Desktop has my username in the file path. The queries are designed to grab everything in a certain folder that is on my Desktop. This means that yes, it will break. I need to change that. I need to find out if there is a location on a computer that is a generic path and is the same no matter what computer it is. Or if nothing like that exists, then I could put it on an external drive, then just send the drive to them. Or I'm sure there's a way where the first time it runs and can't find the current path, it asks user to redefine the path. That then will be the path until next time it happens.