Forum Discussion

dj_consultant's avatar
dj_consultant
Copper Contributor
Apr 29, 2019

How can you use VBA to clear Global Permissions in Power Query

How can you use VBA to reset Data source settings Under Menu Data > Get Data > Data Source Settings where my worksheet has a Data source under the radio button 'Global Permissions' ?

by Clicking the "Clear Permissions" button and selecting "Clear All permissions" this will clear out all power query URL references. But, how can this be done in VBA ?

 

with VBA I can clear data sources that are 'in current workbook' (using code below) but this will not work with the "Global Permissions". 

 

Sub RemoveConnections()

For i = 1 To ActiveWorkbook.Connections.Count

If ActiveWorkbook.Connections.Count = 0 Then Exit Sub ActiveWorkbook.Connections.Item(i).Delete i = i - 1

Next i

End Sub

 

excel
Power Query SharePoint Lists
VBA

2 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Apr 30, 2019
    Do you have a sample workbook you can share? The workbook can be mostly empty, I just need one of those permissions to be in there.
    • dj_consultant's avatar
      dj_consultant
      Copper Contributor
      May 17, 2019

      JKPieterse 

       

      I don't know if this will help much, it seems setting for Global Permissions is outside of the workbook and a setting on the computer (example my global permission is my log in to sharepoint site). May require access a .DLL office API since their is no connection directly saved in the workbook. 

Resources