Looking for ways to shorten daily process for my data

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
296
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Nov 26, 2020
Messages
96
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
380
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

Active Member
Joined
Jan 30, 2021
Messages
296
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Nov 26, 2020
Messages
96
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 ... :rolleyes: 😳

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

Active Member
Joined
Jan 30, 2021
Messages
296
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jan 30, 2021
Messages
296
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 30, 2021
Messages
296
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jan 30, 2021
Messages
296
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 30, 2021
Messages
296
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Nov 26, 2020
Messages
96
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,471
Messages
5,636,514
Members
416,920
Latest member
Riskyplan

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
Top