vba - Excel Power Query - Refresh via Excel sheet located on Sharepoint

Question

I have been searching the internet quite extensively but couldn't find the answer I am looking for. Hope somebody here can help me.

I have a company Sharepoint (Office 365) where I synchronize Excel workbooks via OneDrive on a monthly basis. These Excel workbooks are forming the basis of a Power Query that several employees have sitting in an Excel workbook on their local machine. Now the idea is, that every employee can refresh the data in their local Excel workbook at any given time. While the whole stretch is working in general, I have hit a major usability issue. When trying to refresh the Power Query I (and any other user within the company) gets the error message: Exception of type 'Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException' was thrown. I can work around that error by going through the painful stretch of:

1. Excel menu - Query - Edit
2. Power Query menu - Home - Data Source Settings
3. Enable Radio button Data sources in current workbook, select the data source and press Edit Permissions
4. Delete the current Credentials
5. Edit current credentials, select Organizational account, press Sign in
6. When asked Pick an account select the one shown (which will be the one of the user)
7. Press Save, press OK, press Close
8. Power Query menu - Home - Close & Load
9. Back in the Excel worksheet right mouse click refresh and it works a charm! But you will agree that this is not feasible from a user's perspective.

Now I was hoping to find a switch where I could change that to permanently working or as a programming solution, supply a Refresh button that then does the trick. Unfortunately so far I failed with both of my ideas.

Can anybody point me in the right direction here? Any help is much appreciated! Thx a lot!

Regards

Answer 1

Alright, seems that I got lucky with my latest search!

https://www.youtube.com/watch?v=igcCbKqtwrk

In short: I messed up with the URL and the way how I connected to the Excel file on the Sharepoint! The above video explains quite nicely how to do it correct. Now it only stays a nightmare for the developer, but the user's path to refresh the data is much less painful and in my opinion reasonable!