PDA

View Full Version : SharePoint connection within PowerQuery/PowerPivot



Harry111
2016-05-31, 06:00 AM
Hi Ken,

My Source data files are located on my corporate network on Share Point. if I want to connect to the Share Point (via RNA or from home), I must need to provide my Login credentials. My current situation is that I have a VBA code in excel file which can mapped Share Point link to My Computer with Login prompt. Is there any built in solution available in Power Query which can use Windows Login (existing user) directly without any other work around.

Ken Puls
2016-05-31, 10:27 PM
Hey,

So there are three methods to connect to Sharepoint from Power Query:



SharePoint.Files("https://url")
SharePoint.Contents("https://url")
SharePoint.Tables("https://url")

The method you need depends on which you're after.

When you try to connect to one of these the first time, you should get prompted to enter your credentials, which will then be cached in your user profile.

Harry111
2016-06-09, 07:45 AM
Thanks Ken. :)

n.kehayova
2018-02-16, 11:15 AM
Ok, I have a question regarding the prompt for credentials.
Is there a way to automate it with VBA to log with the AD credentials on the first update of the query? The whole thing to remain invisible to the user?

Ken Puls
2018-02-16, 05:14 PM
Unfortunately no. But if the user knows their AD credentials, it shouldn't be an issue. If you think the user needs more help to pick the right setup, you could use VBA to prompt them with a "You're about to be asked to log in. Please choose this option and enter your normal user ID and password" or something.

n.kehayova
2018-02-16, 06:38 PM
Unfortunately no. But if the user knows their AD credentials, it shouldn't be an issue. If you think the user needs more help to pick the right setup, you could use VBA to prompt them with a "You're about to be asked to log in. Please choose this option and enter your normal user ID and password" or something.

Ok, thanks :)