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

White, Jim (J.G.) 0

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.

Yuliansyah, Firman 0

Sub RefreshAllConnections()
    Dim conn As WorkbookConnection

    ' Loop through each connection in the workbook
    For Each conn In ThisWorkbook.Connections
        ' Check if the connection is a Power Query connection
        If conn.Type = xlConnectionTypeODBC Or conn.Type = xlConnectionTypeOLEDB Then
            conn.OLEDBConnection.BackgroundQuery = False
            conn.Refresh
        End If
    Next conn
End Sub

Share via

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

Your answer