How to add a File Picker in Excel to select the data source file for Power Query – T0019

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

excelmsexcelpower query