In a power query, how can I point to the current user directory for the source file?

user_2607624 0
Viewed 715

I am going to send this workbook to other users. It will query a file which will be in their user directory.

Excel's power query editor does not accept the %userprofile% I try to put in. It gives me

enter image description here How can I make it point towards the current user's directory?

powerquery excel
2 Answers

Create a cell formula with whatever you need to get that path.

Give that cell a range name and use it in place of NamedRangeName below

replace %USERPROFILE% in your formula with

Excel.CurrentWorkbook(){[Name="NamedRangeName"]}[Content]{0}[Column1]
user_92642300

Horseyride solutiont is perfect, however I didn't find a function to get enviroment variable directly from the spreadsheet. I needed a VBA function:

Function env(vn As String) As String
   env = Environ(vn)
End Function

You can call it from the named cell.

user_17430290
Related