How can I force the 'Access Web content' user authentication dialogue box to pop up using VBA?

White, Jim (J.G.) 0 Reputation points
2024-06-12T13:19:53.15+00:00

I have some Power Queries in my Excel file that pull data from other Excel files stored on a SharePoint site. When a new user tries to manually refresh those queries using the "Refresh All" button on the ribbon (Data / Queries & Connections) they are presented with an "Access Web content" authentication dialogue pop up. This is correct behaviour.

But, if I run the followingVBA code "ActiveWorkbook.RefreshAll" then the authentication dialogue box does NOT appear, so the user does not get the chance to properly authenticate. Instead the VBA code just fails with error "1004 [Permission Error] The credentials provided for the web source are invalid". How can I force VBA to display the dialogue box to allow the user to properly authenticate?

Note that all my queries have their OLEDBConnection.BackgrounQuery property already set to False.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,685 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,715 questions
0 comments No comments
{count} votes