It is not fun having to repeatedly browse through folders and files and copy folder paths and file names to paste in our Excel spreadsheet. A more pleasant way of indicating Excel the file we want to use is by using a file picker dialogue box. A simple detail that is not difficult to implement and makes the user's life so much easier.
This tutorial builds on the work done on two previous videos:
Produce automatic video captions on Stream; get text and timestamp values organized in Excel - T0017:
https://youtu.be/gJCrhLo2BNU
How to combine pairs of rows in Power Query with different criteria per column - T0018:
https://youtu.be/XXLuMN7T61s
Chapters:
0:08 – Introducing problem to solve
2:46 – Dealing with "Formula.Firewall" Power Query error
03:34 – Presenting finished solution proposal (File Explorer Dialogue Box with VBA)
06:41 – Greetings and Snap Reports Class program Presentation
08:15 – Presenting starting file for the business case and starting to build a solution
08:44 – Presenting a VBA Macro as Solution to implement
09:20 – Indicating the VBA information sources
10:31 – Explaining the VBA code applied
11:27 – VBA code to set Power Query privacy settings to "Ignore Privacy Settings and potentially improve performance"
12:22 – VBA code for the file picker with Application.FileDialog
15:31 – VBA code to refresh all queries in the workbook
19:22 – Macro demo
25:12 – Finishing the file
27:03 - Dealing with Error when saving (Enable Background Refresh Property in Power Query )
32:16 – Final Notes
References:
Microsoft Docs: https://docs.microsoft.com/en-us/office/vba/api/office.filedialog?WT.mc_id=M365-MVP-5003849
EDUCBA: https://www.educba.com/vba-filedialog/?source=leftnav
LearnExcel Macro: http://learnexcelmacro.com/wp/2016/12/how-to-open-file-explorer-in-vba/
Get access to the EXERCISE FILES by joining our Telegram Group at https://t.me/celiaalvessolveexcel
Subscribe to my NEWSLETTER for exciting news about the Excel world: https://bit.ly/learnfromcelia
Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer
Solve & Excel Consulting - https://solveandexcel.ca
LinkedIn: https://www.linkedin.com/in/celiajordaoalves
Facebook: https://www.facebook.com/solveandexcel
Twitter: @celia_excel
Instagram: @solveandexcel
YouTube: https:www//youtube.com/celiaalvessolveexcel
#excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA
0 Comments