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]

After rethinking about it.

Probably a better and easier way is to add a trusted location.
However, I would not recommend using the download folder for it. If you ever download an unknown excel file there, Excel does not ask anything when you open that file with macros.

To add trusted locations (Company may have restricted / blocked this setting)
Click File > Options.
Click Trust Center > Trust Center Settings > Trusted Locations.
Click Add new location.
Click Browse to find the folder, select a folder, and then click OK.

Add, remove, or change a trusted location in Microsoft Office - Microsoft Support

A trusted location is a folder that contains files you're confident are safe. See how to add, remove, or change a trusted location in Microsoft Office.

support.microsoft.com

 

[Coding4Fun]

Adding a trusted location is actually a better solution. This then lets you save the files in a trusted location to avoid the prompt, the method I provided would adjust it for all files which may not be a good idea but depends on your use case and situation.

 

[ExcelAtEverything]

Ok, so that's what that means! Thanks to the both of you, as I have been trying to figure that out for some time now. In fact, I believe I have another earlier thread where I was trying to figure that out and never got an answer. I'll have to look later. But that helps a lot! I have to redo all my work from last nght, tonight; because I got a virus (already removed) which corrupted my file. Luckily I made a duplicate file the night prior, so I only lost 1 night. So after I do that, I'll check out the code. I can't thank you enough for explaining that to me Tupe77 & Coding4Fun! I added a trusted location and that past works well now. No more "Enable Editing".

 

[Tupe77]

Read this completely before you start thinking about the answers, because as I wrote I noticed various problems / questions and they are in the order in which they came to my mind. This writing became quite confusing in structure ... ?

I don't know if it's possible to download files automatically with macro, but
1.You can move / copy files to the correct folders with a macro.
2.Unhiding Sheet with macro works well.


1.I have to open each file, click on "Enable Editing" button at top, then re-save it into the designated matching folder. (designated matching folder: names, locations if not directly under "Sales Tracker" folder)
1.1 "Enable Editing" -Fixed by trusted location (add trusted location to -> Desktop folder>>Power Query>>Data Model>>Power Pivot>>Sales Tracker) AND select include sub folders!
1.2 re-save it into the designated matching folder. Not needed for "Enable Editing" , BUT part 2 unhide "Package Data" need it and the files need to get there anyway.

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.
2 re-save it into the designated matching folder. Not needed for "Enable Editing" , BUT part 2 unhide "Package Data" need it and the files need to get there anyway.


What we need!
( Pattern to identify to which folder each file is copied. [How you decide where to copy each file?(is that part of file name or something inside file)]
("Downloads" folder: locations)
(designated matching folder: names)
(designated matching folder: locations if not directly under "Sales Tracker" folder)
(and maybe operating system language if is not english) (user folder structure in environ is same, but if it's some other language, must use directly environ values, not environ values in string)


So when you say that this work will soon be done by someone else!!!!!!!!!!!
Question 1. Supposedly he logs in with different IDs?
Question 2. However, is it the same computer?

Problems (only if Question 1 is true)!
1. Downloads and the Desktop folder are user specific. (Normally other user have no access to other user Downloads and the Desktop folder.)
-Copying folders to another user's desktop changes the folder structure. (Username in path changes) (desktop folder is part of Environ, but downloads folder found only by registry key.)
- This makes writing macro little more complicate.
- How about your Power Query, is it resistant to those folder path changes????

2. Trusted locations are user specific. (If not controlled by GPO -> User configuration > Administrative Templates >)
- Not a big deal, but "you" have to remember the add it to the next user too!

So few point...
1. "Downloads" folder is not good place, but if it is used anyway, then "you" should transfer those excel files to elsewhere every day. macro should do this, but where ?
- 1.1 macro move them directly to the "Sales Tracker" subfolder
- 1.2 or somewhere else, where from they are copied to their final folders and saved again. (this make "backup" before re-save files with unhidden sheets) ?

2. "Desktop" folder is not good place...

So maybe before making a macro, you should think about those points and at least this:
1.Will there be problems with macros or Power Query from user-specific folders?
-Then: Change folder structure or macros and queries?

 

[ExcelAtEverything]

What we need!
( Pattern to identify to which folder each file is copied. [How you decide where to copy each file?(is that part of file name or something inside file)]
("Downloads" folder: locations)
(designated matching folder: names)
(designated matching folder: locations if not directly under "Sales Tracker" folder)
(and maybe operating system language if is not english) (user folder structure in environ is same, but if it's some other language, must use directly environ values, not environ values in string)

The pattern to identify would be a small part of the name. "Order Returns" is an example of one. The 5 folder names are different. This would always only be english.

 

[ExcelAtEverything]

So when you say that this work will soon be done by someone else!!!!!!!!!!!
Question 1. Supposedly he logs in with different IDs?
Question 2. However, is it the same computer?

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

 

[ExcelAtEverything]

Problems (only if Question 1 is true)!
1. Downloads and the Desktop folder are user specific. (Normally other user have no access to other user Downloads and the Desktop folder.)
-Copying folders to another user's desktop changes the folder structure. (Username in path changes) (desktop folder is part of Environ, but downloads folder found only by registry key.)
- This makes writing macro little more complicate.
- How about your Power Query, is it resistant to those folder path changes????

All of my Power Queries are resistant to that, yes.The file names are chopped with a spacebar delimiter and the part that would change and cause an issue is discarded. The file system is currently on my desktop because that was just easiest while I created the sheet. It doesn't have to be there. Where should it reside for something like this?

 

[ExcelAtEverything]

2. Trusted locations are user specific. (If not controlled by GPO -> User configuration > Administrative Templates >)
- Not a big deal, but "you" have to remember the add it to the next user too!

When you say remember to add it to next user too" what do you mean? Tell them that they need to add as trusted location, too? Because I believe theirs already is, but I can verify that.

 

[ExcelAtEverything]

1. "Downloads" folder is not good place, but if it is used anyway, then "you" should transfer those excel files to elsewhere every day. macro should do this, but where ?
- 1.1 macro move them directly to the "Sales Tracker" subfolder
- 1.2 or somewhere else, where from they are copied to their final folders and saved again. (this make "backup" before re-save files with unhidden sheets) ?

2. "Desktop" folder is not good place...

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

 

[Tupe77]

When you say remember to add it to next user too" what do you mean? Tell them that they need to add as trusted location, too? Because I believe theirs already is, but I can verify that.

Yes, just tell them that the folder for those files needs to be added to trusted locations.