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]

This would open the files and unhide all the hidden sheets, it could be adjusted to only unhide a certain sheet but provides the concept. This will also save the file.

Source:
Loop Through All Excel Files In A Given Folder — TheSpreadsheetGuru

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xlsx"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
   
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
   
    'Unhide Sheets
      For i = 1 To Worksheets.Count
     
      Sheets(i).Visible = True
     
      Next i
   
    'Save and Close Workbook
      wb.Close SaveChanges:=True
     
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[ExcelAtEverything]

Wow Coding4Fun, this sounds amazing! So I don't speak "code" very well, but in my limited understanding of what you have going on there it looks as if there is some info possibly that I have to edit here and there? But I'm not sure what exactly is going on even with your comments. Would you be able to walk me thru what's going on, and what things I need to edit? I currently understand about only 10% of your code. Again, much appreciated!

 

[Coding4Fun]

Wow Coding4Fun, this sounds amazing! So I don't speak "code" very well, but in my limited understanding of what you have going on there it looks as if there is some info possibly that I have to edit here and there? But I'm not sure what exactly is going on even with your comments. Would you be able to walk me thru what's going on, and what things I need to edit? I currently understand about only 10% of your code. Again, much appreciated!

I added a bit more clarity to the comments hopefully that will help.

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com

'' Setting up some variables (Containers to hold stuff later)
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed - Turning off Screen Update (The Excel screen will not live update while this runs), Stops Events and Calculations.
'This is to allow the macro to run faster.
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User - This is basically saying to use the File Explorer to select the file to open
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

''With the File Explorer we set the title of the window "Select A Target Folder"
'' If we cancel or close the window it will exit (If .Show <> -1 Then GoTo NextCode - NextCode would point to "" and thus stop)
'' We capture the folder selected in the Pop Up window.
'' This control what folder we are targeting to loop through
    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*") - We are setting what file types to use in this case we are looking for .xlsx files
  myExtension = "*.xlsx"

'Target Path with Ending Extention - setting our file by combining the values we previously set, essentially the path to the file.
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder, we are looping through (While myFile <> ""), basically while we still have files to run through,
'we set this at the end wo we will check again after the first loop.
  Do While myFile <> ""
    'Set variable equal to opened workbook - this essentially opens the file
      Set wb = Workbooks.Open(Filename:=myPath & myFile)

    'Ensure Workbook has opened before moving on to next line of code
      DoEvents

    'Unhide Sheets - here we loop through each sheet in the workbook and set its Visible value to True, this is the same as Unhiding the sheet.
    ' I use a loop here, basically start at sheet 1 and go until you hit the number of worksheet in the file.
    ' This is what you would want to edit to change if all sheets are unhidden or just a specific one.
      For i = 1 To Worksheets.Count
  
      Sheets(i).Visible = True
  
      Next i

    'Save and Close Workbook
      wb.Close SaveChanges:=True
  
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[ExcelAtEverything]

Sorry, while I appreciate the extra effort on the comments, I still don't quite understand. Imagine looking at something written in a language that you barely speak. There are about 4 o 5 places where it feels sort of like you are saying to customize the info, but I'm just unsure because I don't understamd the code, and some of your comments I just don't follow what you mean. It's not you, it's me! Lol. My apologies for being a newb, but is there any chance you could just tell me which places to enter my own info? Even in your comments are past my level of understanding in most places. As an example it seems like the code is saying to look in a specific folder for the files which need to be converted. But mine are initially in my Downloads folder, and there are also typically a ton of other unrrelated Excel files in there. Does this imply that I need to put them all into a different folder first? I don't know.

Also, I don't see a place where it specifically says that its taking care of me having to click "Enable Editing" before resaving. Does this take care of it?

Also, I noticed in another spot that you are specifically looking at xlsx files, but mine are both xlsx and xlsm. Does that matter?

 

[ExcelAtEverything]

Hang on, I'll be more specific and just ask the questions as they come up. I realize that your time may be limited.

 

[ExcelAtEverything]

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.

2) I don't understand this. So am I supposed to edit something here?

'Retrieve Target Folder Path From User - This is basically saying to use the File Explorer to select the file to open​

Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)​

​

3) Do I change out "Select A Target Folder" with the name of my new folder? Does it matter where the folder is located?

With FldrPicker​

.Title = "Select A Target Folder"​

.AllowMultiSelect = False​

If .Show <> -1 Then GoTo NextCode​

myPath = .SelectedItems(1) & "\"​

End With​

 

[ExcelAtEverything]

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.

2) I don't understand this. So am I supposed to edit something here?

'Retrieve Target Folder Path From User - This is basically saying to use the File Explorer to select the file to open​

Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)​

​

3) Do I change out "Select A Target Folder" with the name of my new folder? Does it matter where the folder is located?

With FldrPicker​

.Title = "Select A Target Folder"​

.AllowMultiSelect = False​

If .Show <> -1 Then GoTo NextCode​

myPath = .SelectedItems(1) & "\"​

End With​

​

 

[Coding4Fun]

Sorry, while I appreciate the extra effort on the comments, I still don't quite understand. Imagine looking at something written in a language that you barely speak. There are about 4 o 5 places where it feels sort of like you are saying to customize the info, but I'm just unsure because I don't understamd the code, and some of your comments I just don't follow what you mean. It's not you, it's me! Lol. My apologies for being a newb, but is there any chance you could just tell me which places to enter my own info?

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.

As an example it seems like the code is saying to look in a specific folder for the files which need to be converted. But mine are initially in my Downloads folder, and there are also typically a ton of other unrrelated Excel files in there. Does this imply that I need to put them all into a different folder first?

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)

Also, I don't see a place where it specifically says that its taking care of me having to click "Enable Editing" before resaving. Does this take care of it?

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

Also, I noticed in another spot that you are specifically looking at xlsx files, but mine are both xlsx and xlsm. Does that matter?

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.

 

[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.

2) I don't understand this. So am I supposed to edit something here?

'Retrieve Target Folder Path From User - This is basically saying to use the File Explorer to select the file to open​

Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)​

​

3) Do I change out "Select A Target Folder" with the name of my new folder? Does it matter where the folder is located?

With FldrPicker​

.Title = "Select A Target Folder"​

.AllowMultiSelect = False​

If .Show <> -1 Then GoTo NextCode​

myPath = .SelectedItems(1) & "\"​

End With​

No - this will automatically ask you which folder to use, unless you dont want to tell it where to look there is nothing to adjust.