Hi all,
I am new to VBA and I have been trying to write (I puzzled some codes from the web) the below code.
The purpose of the code is:
- unprotect the all Workbook
- Refresh the power query connections & data model
- protect the workbook again but allow filtering of table, the outlining and the slicers of the Power pivot .
I am not sure if the why I have written/ puzzled the code is the optimal.
Btw I run the code through a button in the worksheet (when clicking on the button the code runs)
Everything works except for the Power pivot slicer, and for the fact that I get a message at the end that the Worksheet is protected (I would like to not get this pop up message).
Thanks in advance for any help.
KR
------------------------------------------------------------------
------------------------------------------------------------------
Public Sub UpdatePowerQueries()
' Macro to update my Power Query script(s)
For Each rs In Worksheets
rs.Unprotect Password:="123"
Next rs
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ActiveWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn
For Each ra In Worksheets
With ra
.Protect Password:="123", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
.AllowUsingPivotTables = True
End With
Next
Worksheets("INPUT Table").Activate
End Sub
------------------------------------------------------------------
------------------------------------------------------------------
Bookmarks