As mentioned in the previous Excel VBA post, I would like to share my thoughts on safe Excel pivot tables reports delivery. The best I can recommend right now is an Excel file with an action, that on file open event hides all the rows in the pivot tables , so the user cannot see the data being pulled and saved into this sheet by someone else last time. The user has to refresh data, and thats where the provided credentials take place, right? Next thing I always do is, that I put password on the VBA code in the file. You have a tutorial for this step here. That way you ensure the user cannot see the macro VBA code and cannot do anything else then execute.
Private Sub Auto_Open() Dim pf As PivotField Dim PT As PivotTable Dim WS As Worksheet Dim aLock As Integer On Error Resume Next For Each WS In ActiveWorkbook.Worksheets For Each PT In WS.PivotTables With PT .ManualUpdate = True .EnableDrilldown = False .EnableFieldList = False .EnableFieldDialog = False .EnableWriteback = False For Each pf In .PivotFields With pf .DragToPage = False .DragToRow = False .DragToColumn = False .DragToData = False .DragToHide = False End With Next pf End With Next PT Next WS End Sub