PowerQuery sharepoint Credentials

[JNT44]

I wrote some files to simplify users. They select a file and some macro and power query will refresh and return them the information.

For a few of them, I need to refresh some data that is stored in a sharepoint. When a new user run the macro that will refresh the queries, it bugs because the credentials are not filled.

So I need to explain them how to open power query and set their credentials to connect to the sharepoint.

Is there anyway that this could pop-up if no credentials are setup or wrong ? So it would be easier for the users ?

I except the connection refresh to happen automatically or get a user messagebox or input box to fill their credentials instead of failing.

[#2]

@JNT44 

Strange. If without macro refresh shall work automatically if the user has organizational work account and this account has at least R/O access to SharePoint resource.

If Power Query can't recognize credentials pop-up window appears to select type of account and set credentials.

[#3]

@Sergei Baklan 

When I open PowerQuery, we would get the Credential Window where they can select anonymous, windows or others where they can use their 365 account to connect to their account.

May be it's because it goes throw VBA for refresh ?

Or because I fold some table on top of another ?

Usually privacy content will pop-up also.

A little strange then.

[#4]

@JNT44 

Perhaps VBA, but I'm not sure, never used it with Power Query.  You may try with Automate, it's a simple script

function main(workbook: ExcelScript.Workbook) {
	workbook.refreshAllDataConnections();
}

Creating the button for the script you share the link on script file.