Hi all!
I'm having problems with the path of an excel query and I'm afraid there is no solution. maybe you can help me.
Our team works with dropbox, and not with a local server which causes every different user to have a different file location.
for example :
C:\Users\Robbe\Dropbox (xxx)\General\01 Operations\19 General File
C:\Users\Thomas\Dropbox (xxx)\General\01 Operations\19 General File
Is there any way I could bypass this problem and make my query refresh on a different users computer ?
I'm not familiar with dropbox, but you can pass variable from sheet to PowerQuery.
Lets say, you set up named range for single cell in Sheet1!A1, and named it "vUser".
Go into advanced editor in query editor and add below line right after "let"
Then you'd edit the source by concatenating path string with User variable.
User = Excel.CurrentWorkbook(){[Name="vUser"]}[Content]{0}[Column1],
EDIT: This example is for querying Excel File, but same principle applies if you query folder etc.Source = Excel.Workbook(File.Contents("C:\Users\" & User & "\Documents\Customers.xlsx"), null, true),
In addition you can combine it with VBA to automatically update value in the named range.
Private Sub Workbook_Open() Sheet1.Cells(1, 1) = Environ("UserName") End Sub
Last edited by CK76; 06-01-2017 at 07:53 AM.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
Thanks for the reply.
Unfortunately i'm no that great with this!
If I'd name column "T" in the source file "vUser" what should I write?
User = Excel.CurrentWorkbook(){[Name="vUser"]}[Content]{0}[Column17], ?
If I do this it cant find a column named "vUser"
Couple of things.
1. Named Range should refer to single cell. (You can have dropdown list in this cell, but only single variable should be passed to PowerQuery)
2. [Column1] should not change since named range in this set up should have only single column.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks