Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-500799%22%20slang%3D%22en-US%22%3EHow%20can%20you%20use%20VBA%20to%20clear%20Global%20Permissions%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500799%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20you%20use%20VBA%20to%20reset%20Data%20source%20settings%26nbsp%3B%3CSPAN%3EUnder%20Menu%20Data%20%26gt%3B%20Get%20Data%20%26gt%3B%20Data%20Source%20Settings%20where%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Emy%20worksheet%20has%20a%20Data%20source%20under%20the%20radio%20button%20'Global%20Permissions'%20%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eby%20Clicking%20the%20%22Clear%20Permissions%22%20button%20and%20selecting%20%22Clear%20All%20permissions%22%20this%20will%20clear%20out%20all%20power%20query%20URL%20references.%20But%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ehow%20can%20this%20be%20done%20in%20VBA%20%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ewith%20VBA%20I%20can%20clear%20data%20sources%20that%20are%20'in%20current%20workbook'%20(using%20code%20below)%20but%20this%20will%20not%20work%20with%20the%20%22Global%20Permissions%22.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESub%3C%2FSPAN%3E%3CSPAN%3E%20RemoveConnections()%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%3C%2FSPAN%3E%3CSPAN%3E%20i%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%20%3CSPAN%3ETo%3C%2FSPAN%3E%3CSPAN%3E%20ActiveWorkbook.Connections.Count%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%3C%2FSPAN%3E%3CSPAN%3E%20ActiveWorkbook.Connections.Count%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%20%3CSPAN%3EThen%3C%2FSPAN%3E%20%3CSPAN%3EExit%3C%2FSPAN%3E%20%3CSPAN%3ESub%3C%2FSPAN%3E%3CSPAN%3E%20ActiveWorkbook.Connections.Item(i).Delete%20i%20%3D%20i%20-%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ENext%3C%2FSPAN%3E%3CSPAN%3E%20i%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEnd%3C%2FSPAN%3E%20%3CSPAN%3ESub%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-500799%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%20SharePoint%20Lists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EVba%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-502590%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20you%20use%20VBA%20to%20clear%20Global%20Permissions%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-502590%22%20slang%3D%22en-US%22%3EDo%20you%20have%20a%20sample%20workbook%20you%20can%20share%3F%20The%20workbook%20can%20be%20mostly%20empty%2C%20I%20just%20need%20one%20of%20those%20permissions%20to%20be%20in%20there.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-583596%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20you%20use%20VBA%20to%20clear%20Global%20Permissions%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-583596%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20if%20this%20will%20help%20much%2C%20it%20seems%20setting%20for%20Global%20Permissions%20is%20outside%20of%20the%20workbook%20and%20a%20setting%20on%20the%20computer%20(example%20my%20global%20permission%20is%20my%20log%20in%20to%20sharepoint%20site).%20May%20require%20access%20a%20.DLL%20office%20API%20since%20their%20is%20no%20connection%20directly%20saved%20in%20the%20workbook.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
dj_consultant
New Contributor

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

 

2 Replies
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.
Highlighted

@Jan Karel Pieterse 

 

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. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies