Define Path in Power Query

Question

I had recorded a Macro to merge 2 txt files from a folder.

Macro Looks like this:

ActiveWorkbook.Queries("test").Delete

ActiveWorkbook.Queries.Add Name:="test", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\test\Desktop\folder\test.txt"")

I am planning to copy the excel to a folder that contains the .txt files

How to replace the path C:\Users\test\Desktop\folder\test.txt to use current working folder of worksheet + "\test.txt"?

I am deleting the query in the VBA script to avoid the error "A Query with the name test already exists"

If you're moving the Excel file which is running the macro into the same folder as the target file, you could use Thisworkbook.Path to help build the full path to your target file. — Michael Murphy

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2020-01-30T01:02:22

You can do it without VBA

Only remember that if you change the folder where the files are saved, you need to refresh the query

Steps:

In a cell put this formula: =LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-1)

Assign a name range to that cell e.g. CurrentFolder

With that cell selected, click on Data | From Table / Range

Delete the automatically applied steps (depends on your configuration)

Select the path, right click it and select Drill down

*Now your query should output just the workbook path*

Create a new query that extracts the data from the folder / file
In the source step, replace the path (including the quotes) with CurrentFolder

Let me know if it works

Define Path in Power Query

1 Answers