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.

 

[Coding4Fun]

1) Do I first need to set up a new specific folder where I initially download my files into, rather than keeping it as my Downloads folder? My downloads folder (as well as the downloads folder of the end user) may also contain other unrelated Excel docs and I wouldn't want them to get looped into anything.

Yes you want a dedicated location both on your PC as well as the person to take over the process. This way you can when prompted tell it to grab all files in the folder. Everything is customizable so you could of course tell is where to look without having to manually indicate this and or give it specific file names to look for ect. Unless you have some reason for doing so there should be no reason to have a duplication in your downloads you could save them directly to this dedicated location.

 

[ExcelAtEverything]

Do you know where you would put this code to run it? I would say to run it and see what you get to help you understand. NOTE: When it pop's up and asks you which folder you want to look in it will unhide all the sheets in the excel files accordingly.

The folder your using is chosen at run time, so when you "run" this code it would ask you for "your information" or what folder to look in.

No, I Alt-F11 on my PC dims the screen instead of going to VB editor. Also going to Developer>Visual Basic just takes me here.

Regarding the folder, will it then continue to ask me every day, or does it remember the last one I set it to?

 

[ExcelAtEverything]

Do you know where you would put this code to run it? I would say to run it and see what you get to help you understand. NOTE: When it pop's up and asks you which folder you want to look in it will unhide all the sheets in the excel files accordingly.

The folder your using is chosen at run time, so when you "run" this code it would ask you for "your information" or what folder to look in.


I would set up a folder dedicated for this purpose yes, instead of saving them to your downloads I would save them to a specific folder. When it asks you where to look for files you would navigate to the chosen directory or path (the place where you saved the files)


I am not sure if the SaveAs would address this or not, test it and let me know

It sounds like it does yes, you can change this to match the file types you want.
myExtension = "*.xlsx"

Change "*.xlsx" to "*.xls*" and this would pick up all excel file types .xls - .xlsx - .xlsm etc.

Not a problem.

Great, thanks for the clarity! I'm all but certain that this will not take care of the "Enable Editing" problem, because all it's doing is opening then resaving the file. Initially that is what I tried to do at first but it wasn't working. None of my queries were able to access the files until I went back and clicked that button before resaving. This would defeat the entire purpose of the rest because my queries would all throw update errors and stop the rest of the sheet from working properly.

 

[ExcelAtEverything]

No, I Alt-F11 on my PC dims the screen instead of going to VB editor. Also going to Developer>Visual Basic just takes me here.
View attachment 37177
Regarding the folder, will it then continue to ask me every day, or does it remember the last one I set it to?

Just read that on some laptops I guess it's Alt-Shift-F11. That was why it wasn't working for me.

 

[ExcelAtEverything]

Just read that on some laptops I guess it's Alt-Shift-F11. That was why it wasn't working for me

...meant Alt-Function F11

 

[Coding4Fun]

Great, thanks for the clarity! I'm all but certain that this will not take care of the "Enable Editing" problem, because all it's doing is opening then resaving the file. Initially that is what I tried to do at first but it wasn't working. None of my queries were able to access the files until I went back and clicked that button before resaving. This would defeat the entire purpose of the rest because my queries would all throw update errors and stop the rest of the sheet from working properly.

This is user specific and has to do with the Macro Security settings. There are "trusted locations" that will not have this prompt and depending on what your Macro security is set to you may not see this as well. Its a security feature to let you know there is code in the file and confirm you want to "enable" it. You likely have yours set to "Open selected file types in protected view and allow editing".

 

[Coding4Fun]

This is the error you are referring to

 

[Coding4Fun]

Where to put it:
In that window you had the "dark grey" one. Go into View and select Code and Project Explorer. This is where the code lives. Right click and add a new module if one does not already exist. This is where you would insert your code. I use Alt+F11 to get into this Code window.

 

[Coding4Fun]

This is user specific and has to do with the Macro Security settings. There are "trusted locations" that will not have this prompt and depending on what your Macro security is set to you may not see this as well. Its a security feature to let you know there is code in the file and confirm you want to "enable" it. You likely have yours set to "Open selected file types in protected view and allow editing".

That was not totally accurate in that it does not necessarily mean the file has code but has been flagged to be opened in a Protected View.
If you uncheck the file type for Excel 2007 or later it should no longer prompt.

 

[Tupe77]

This worked for me, but may depend on the environment and Company security rules

Do not save to the same folder or network drive !
The Downloads folder and network drives are likely to trigger protected mode again !!!


1. Open Workbook and press Alt + F11 for Visual Basic Editor
2. Right click VBAProject(Filename)
3. Select Insert
4. Select Module ' Creating new module
5. select that new module
6. paste this code to that new module

Sub TS_ReSave_Files()

Dim wb As Workbook
Dim OpenFolder As String, OpenFileName As String, OpenFileExtension As String
Dim NewFolder As String, NewFileName As String, NewFileExtension As String

    ' OpenFolder = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Dowloads\"    ' This should work, If You try it Remove start of line APOSTROPHE (') AND add it start of NEXT line!!!
    OpenFolder = "C:\users\"                ' Folder where file is. !!! Folder MUST END WITH \         (Fill these lines)
    OpenFileName = "Test0001"               ' Filename without file -extension.                        (Fill these lines)
    OpenFileExtension = ".xlsm"             ' Filename -extension.                                    (Fill these lines)
   
    NewFolder = "E:\Temp\"                  ' Folder where save file. !!! Folder MUST END WITH \    (Fill these lines)
    NewFileName = "Renamed_Test0001"        ' Filename without file -extension.                        (Fill these lines)
    NewFileExtension = ".xlsm"              ' Filename -extension.                                    (Fill these lines)
   
Set wb = Workbooks.Open(OpenFolder & OpenFileName & OpenFileExtension)     ' Opening file
Application.Wait Now + TimeValue("00:00:02")                            ' waiting
wb.SaveAs FileName:=NewFolder & NewFileName & NewFileExtension            ' Saving file
wb.Close                                                                ' Closing that opened workbook
End Sub