Power Query Experts Add file picker and pass data to the query issue

Excel 2019

I needed a power Query that imports a delimited text file and performs the same query but on different files as needed using a file picker.

I recorded the query as a macro as it does everything I need except to pass the user selected file name and other related data from the file picker to the locations indicated in the code and the Parts 1 - 6 below.

I have been able to add a file picker dilogue and added some code to the query to pass the data to it but it still does not work.
When I run the sub the file picker opens, I can choose and click on any text file but the code still opens the file that is hard coded in to the query by the recorded macro.

As you will see from the sub name, I am on revision 8 and I am out of my depth with Power Query hence the title of the post.

I attach a small workbook and text file of fake data.

From what I can tell...
Part 1.
In the code below at this line of code "ActiveWorkbook.Queries.Add Name:="Fake Test Data 1", Formula:=" the sub needs to pass the user selected file name from the file picker and replace "Fake Test Data 1" with the name of the user selected file, the file suffix is NOT REQUIRED.

Part 2.
In the code below at this line of code "Source = Csv.Document(File.Contents(""C:\Test File\Fake Test Data 1.txt"")," the sub needs to pass the full path and file name of the user selected file with suffix from the file picker.

Part 3.
In the code below at this line of code ".CommandText = Array("SELECT * FROM [Fake Test Data 1]")" ' the sub needs to pass the user selected file name from the file picker and replace "Media Pro Tools NEW" with the name of the user selected file, the file suffix is NOT REQUIRED.

Part 4.
In the code below at this line of code "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Fake Test Data 1"";Extended Properties=""""" _ ' the sub needs to pass the user selected file name from the file picker and replace "Media Pro Tools NEW" with the name of the user selected file, the file suffix is NOT REQUIRED.

Part 5.
In the code below at this line of code "#.ListObject.DisplayName = "Fake Test Data 1"" 'the sub needs to pass the user selected file name from the file picker and replace "Media Pro Tools NEW" with the name of the user selected file, the file suffix is NOT REQUIRED and any SPACES in the file name must be replaced with a Underscore "_".

Part 6.
In the code below at this line of code "ActiveSheet.ListObjects("Fake_Test_Data_1").Unlist" 'the sub needs to pass the user selected file name from the file picker and replace "Media Pro Tools NEW" with the name of the user selected file, the file suffix is NOT REQUIRED and any SPACES in the file name must be replaced with a Underscore "_".

This is the code from the macro recorded query.

Please Login or Register  to view this content.

This is the file picker and extra that I added to the query.

Please Login or Register  to view this content.

This is the full sub code (such as it is) with the new code added.

Please Login or Register  to view this content.

Originally Posted by romperstomper

Is this you too? http://www.vbaexpress.com/forum/show...lid-characters

Hi romperstomper

Yes, that was a question that I had about an error message "query name contains invalid characters" and it was answered and closed so I did not think
it qualified as a cross post as the post here, although about a query, is about how to pass the file name details from the file picker to the various parts
of the query code.

Hope this clears that up.

This topic is kind of the same

https://www.excelforum.com/excel-pro...urce-file.html

Originally Posted by JEC.

This topic is kind of the same

https://www.excelforum.com/excel-pro...urce-file.html

Hi JEC.

Yeah that is "kind of" a simular question but that OP's needs are lesser than mine.

I know very little about Power Query (hence asking for PQ Experts) but in my code the query needs specific information passing to it from the file picker which I listed in my OP but for completeness...

From what I can tell...
Part 1.
In the code below at this line of code "ActiveWorkbook.Queries.Add Name:="Fake Test Data 1", Formula:=" the sub needs to pass the user selected file name from the file picker and replace "Fake Test Data 1" with the name of the user selected file, the file suffix is NOT REQUIRED.

Part 2.
In the code below at this line of code "Source = Csv.Document(File.Contents(""C:\Test File\Fake Test Data 1.txt"")," the sub needs to pass the full path and file name of the user selected file with suffix from the file picker.

Part 3.
In the code below at this line of code ".CommandText = Array("SELECT * FROM [Fake Test Data 1]")" ' the sub needs to pass the user selected file name from the file picker and replace "Media Pro Tools NEW" with the name of the user selected file, the file suffix is NOT REQUIRED.

Part 4.
In the code below at this line of code "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Fake Test Data 1"";Extended Properties=""""" _ ' the sub needs to pass the user selected file name from the file picker and replace "Media Pro Tools NEW" with the name of the user selected file, the file suffix is NOT REQUIRED.

Part 5.
In the code below at this line of code "#.ListObject.DisplayName = "Fake Test Data 1"" 'the sub needs to pass the user selected file name from the file picker and replace "Media Pro Tools NEW" with the name of the user selected file, the file suffix is NOT REQUIRED and any SPACES in the file name must be replaced with a Underscore "_".

Part 6.
In the code below at this line of code "ActiveSheet.ListObjects("Fake_Test_Data_1").Unlist" 'the sub needs to pass the user selected file name from the file picker and replace "Media Pro Tools NEW" with the name of the user selected file, the file suffix is NOT REQUIRED and any SPACES in the file name must be replaced with a Underscore "_".

Originally Posted by romperstomper

All you really need is a parameter query with a named range for the file path. Then all your code needs to do is updated the named range with the new file path and simply refresh the existing query.

Thank romperstomper

and is this suitable for using with a VBA file picker?

Originally Posted by romperstomper

All you really need is a parameter query with a named range for the file path. Then all your code needs to do is updated the named range with the new file path and simply refresh the existing query.

Ah ok. I have spent a couple of hours researching your suggestion as I had no idea what it meant, like I said, PQ is not my area so its all new to me.


From what little I have been able to find, I do not think that this is good for me.
The text files will all be named differently each time and may well be stored in a different location so the flexability of using a file picker is why I am going down that route.

Thanks anyway.

So I have made some progress.

The code that I have added now seems to pass the file name path directly to the query but I am now getting a the error...
Run-time error '-2147024809 (80070057) The query name 'Fake Test Data 1.txt' contains characters that are not valid.

Can anyone suggest a cure?

Please Login or Register  to view this content.

Originally Posted by romperstomper

Anyway, you could do something like this:

Hi romperstomper

Thank you for your suggestion but the code gives the Run-time error '1004': [Expression.Error] The import Csv.DocumentFile.Contents matches no exports. Did you miss a module reference?
In code line ".Refresh BackgroundQuery:=False ' Refresh the query without running in the background"

Please Login or Register  to view this content.

Originally Posted by romperstomper

No, it doesn't add an extra step. You use the file picker to update the cell that is used for the parameter. It's exactly the same number of steps for the user (click a button) and a lot less code.

I have gave this some thought over night and I apoligise if I can across as argumentitive which I know that I can do but I did not mean it.
When I have spent so long on something I kinda get tunnel vision as I have not being able to do finish something that I have started.

Maybe PQ is a step to far for me right right now.

If I may ask.

So I could have a marcro with a file picker, the user selects what ever file, and the file picker passes the file path, as a an example, "C:\Some Folder\Some File.txt to the field on the Paramter query table and would there be code in the macro that then runs / refress the query based on the new file regardless of ean file being on any path or having any name, as long as the data within that file is in the same format / same named columns and so on.

Would this work if the worksheet with the parameter table was hidden?

Thank you everyone for your replies.

I am going to mark this thread as solved so I can post in a different place as I am getting no where here
and do not want to risk someone being over zealous and accusing me of cross posting.

For the record, it is perfectly acceptable to ask the same question in a different place if the Op closes
the original question due to not receiving the help they seek.

I apprecate everyones time but on the other hand I do not have weeks to sort this issue.

It is taking up an inordinate amount of time and it is impacting my family time.

For what it is worth, this is not a work project and I am not a student seeking to have my assignments
completed for me.

I have researched to the limits of my extremly modest knowledge and abilitys and I came here seeking
help but I found none.

I did exactly what the forum rules ask for, I created a example workbook and provided test data so
what more was expected of me I do not know.

I have been a member here since 2015 and this forum used to be a friendly helpful community where
people gave real straight answers insted of vague hints. It is a shame, it really is.

If I knew how to do something then I would not be here asking for help.