Excel Queries from Excel Workbook located on OneDrive From Different Users

Question

Friends,

I currently am pulling data from an excel file located in OneDrive into my spreadsheet using the following source.

= Excel.Workbook(File.Contents("C:\Users\Bob Saggat\OneDrive - USS Enterprise\Prototype\InvestorsMasterProto.xlsm"), null, true)

I use the following code to successfully post data to files with different users from the OneDrive folder in VBA modules with the following code:

Workbooks.Open("C:\Users\" & Environ("username") & "\OneDrive - USS Enterprise\Prototype\OrdersMasterProto.xlsm")

I expected using Environ("username") in the Power Query Editor in the code for the source of the file to work.

= Excel.Workbook(File.Contents("C:\Users\" & Environ("username") & "\OneDrive - USS Enterprise\Prototype\InvestorsMasterProto.xlsm"), null, true)

When I do so I get the following error: Expression.Error: The name 'Environ' wasn't recognized. Make sure it's spelled correctly.

What is the solution here?

Some GoogleFu suggests I host the file on a SharePoint instead of in OneDrive but I have absolutely no experience in SharePoint and I presume there is a simple solution I am missing.

As always, your wisdom and kindness is greatly appreciated.

Thank You

--

Now, I call this module on load to find the username and put it into a named ranged. This works.

Sub GetUsernameForDataSource()
Dim OrderRunTemplate As Worksheet

Set OrderRunTemplate = ThisWorkbook.Worksheets("Order Run Template")

LocalUsername = Environ("username")

'May need to clear the CurrentUser named ranged first then add the user so it doesn't add multiple users


'Adding LocalUsername to named range to use as source in PowerQuery
    ThisWorkbook.Names.Add Name:="CurrentUser", _
        RefersTo:=LocalUsername


End Sub

Yet when I go into PowerQuery and enter the following

= Excel.Workbook(File.Contents("C:\Users\" & Excel.CurrentWorkbook(){[Name="CurentUser"]}[Content]{0}[Column1] & "\OneDrive - USS Enterprise\Prototype\InvestorsMasterProto.xlsm"), null, true)

I get the error: Expression.Error: We couldn't find an Excel table named 'CurrentUser'. Details: CurrentUser

Any suggestions?

horseyride horseyride · Accepted Answer · 2021-04-07T20:11:47

assuming you can get the value of Environ("username") into a named range such as abc, then you can read that named range in powerquery using

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

and in your code as

= Excel.Workbook(File.Contents("C:\Users\" & Excel.CurrentWorkbook(){[Name="abc"]}[Content]{0}[Column1] & "\OneDrive - USS Enterprise\Prototype\InvestorsMasterProto.xlsm"), null, true)

Excel Queries from Excel Workbook located on OneDrive From Different Users

1 Answers