VBA to Open Power Query and set folder to append .csv files

%3CLINGO-SUB%20id%3D%22lingo-sub-2376054%22%20slang%3D%22en-US%22%3EVBA%20to%20Open%20Power%20Query%20and%20set%20folder%20to%20append%20.csv%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376054%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20automate%20some%20functions%20in%20Power%20Query%20using%20VBA%20because%20users%20most%20likely%20will%20not%20know%20how%20to%20use%20Power%20Query%20to%20do%20the%20following.%20I%20already%20have%20some%20code%20where%20a%20user%20selects%20a%20folder%20path%20to%20some%20files.%20The%20code%20does%20more%20than%20this%20of%20course%20but%20utilizing%20the%20fact%20that%20the%20user%20has%20already%20selected%20the%20folder%20location%2C%20I%20want%20to%20use%20that%20data%20source%20toward%20the%20end%20of%20the%20process%20to%20open%20up%20Power%20Query.%20Then%2C%20do%20the%20equivalent%20of%20Selecting%20get%20data%2C%20Get%20Folder%20(this%20would%20be%20already%20declared).%20Where%20it%20would%20retrieve%20several%20.csv%20files.%20Now%20this%20may%20not%20be%20do-able%20but%20I%20would%20also%20like%20to%20utilize%20what%20functions%20I%20write%20in%20Power%20Query%20after%20those%20files%20have%20merged%20and%20run%20those%20set%20of%20instructions%20on%20the%20files%20before%20Closing%20%26amp%3B%20Loading%20into%20the%20Workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2376054%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2384013%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20Open%20Power%20Query%20and%20set%20folder%20to%20append%20.csv%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384013%22%20slang%3D%22en-US%22%3EI%20don't%20understand%20the%20problem.%20Users%20should%20not%20have%20to%20open%20PowerQuery%20as%20long%20as%20you%20have%20set%20up%20that%20query%20properly%2C%20all%20they%20have%20to%20do%20is%20refresh%20the%20query%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2384353%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20Open%20Power%20Query%20and%20set%20folder%20to%20append%20.csv%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3BSorry%2C%20I%20did%20not%20mention%20that%20this%20process%20would%20be%20used%20as%20a%20first%20time%20setup%20for%20other%20users.%20Not%20every%20user%20knows%20how%20to%20use%20Get%20%26amp%3B%20Transform%20(Power%20Query%20sounds%20so%20much%20better)%2C%20so%2C%20it%20would%20just%20be%20something%20to%20initially%20gather%20the%20data%20and%20set%20the%20PQ%20up.%20I%20did%20find%20some%20information%26nbsp%3Bhere%20%3CA%20href%3D%22https%3A%2F%2Fexcelunplugged.com%2F2018%2F01%2F23%2Fmodifying-power-query-m-code-with-vba%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelunplugged.com%2F2018%2F01%2F23%2Fmodifying-power-query-m-code-with-vba%2F%3C%2FA%3E%26nbsp%3Bthat%20I%20have%20not%20tried%20yet%20but%20hope%20it%20will%20work%20for%20what%20I%20am%20doing.%20I%20will%20follow%20up%20and%20update%20here%26nbsp%3Bif%20it%20does%20or%20does%20not%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I would like to automate some functions in Power Query using VBA because users most likely will not know how to use Power Query to do the following. I already have some code where a user selects a folder path to some files. The code does more than this of course but utilizing the fact that the user has already selected the folder location, I want to use that data source toward the end of the process to open up Power Query. Then, do the equivalent of Selecting get data, Get Folder (this would be already declared). Where it would retrieve several .csv files. Now this may not be do-able but I would also like to utilize what functions I write in Power Query after those files have merged and run those set of instructions on the files before Closing & Loading into the Workbook.

2 Replies
I don't understand the problem. Users should not have to open PowerQuery as long as you have set up that query properly, all they have to do is refresh the query?

@Jan Karel Pieterse Sorry, I did not mention that this process would be used as a first time setup for other users. Not every user knows how to use Get & Transform (Power Query sounds so much better), so, it would just be something to initially gather the data and set the PQ up. I did find some information here https://excelunplugged.com/2018/01/23/modifying-power-query-m-code-with-vba/ that I have not tried yet but hope it will work for what I am doing. I will follow up and update here if it does or does not work.