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:

   • Produce automatic video captions on Stream...  

How to combine pairs of rows in Power Query with different criteria per column - T0018:

   • How to combine pairs of rows in Power Quer...  

________________________________________________________
Learn how to AUTOMATE YOUR EXCEL REPORTS in minutes instead of hours without copy-paste or coding: https://snapreportschamp.com/course

Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists:
https://solveandexcel.ca/dynamic-depe...
___________________________________________________________________

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/offi...

EDUCBA: https://www.educba.com/vba-filedialog...

LearnExcel Macro: http://learnexcelmacro.com/wp/2016/12...


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:   / celiajordaoalves  

Facebook:   / solveandexcel  

Twitter: @celia_excel

Instagram: @solveandexcel

YouTube: https:www//   / celiaalvessolveexcel  


#excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA