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.

@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
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies