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
.ManualUpdate = True
.EnableDrilldown = False
.EnableFieldList = False
.EnableFieldDialog = False
.EnableWriteback = False
For Each pf In .PivotFields
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
Today I would like to talk a bit about Excel VBA macros. Recently I needed to come up with a macro for an Excel 2016 report sheet, that would inject this report sheet into every Excel file in the directory. This report sheet obviously contains data connections which are also being distributed to the end users. Regarding distribution purposes, this macro replaces the report sheet injected last time with the current version. ( Using this, you can easily develop and distribute new versions of the report sheet to your users ). Sounds like some pretty useful stuff doesnt it?
I need to mention, that I am not an dedicated VBA developer, so you might find the attached code a little bit harsh, but it works perfectly. The code was also checked by the Office Code Compatibility Checker tool ( available here ). This is a pretty cool tool from Microsoft ensuring you have your VBA code compatible between MS Office versions and that classical 32/64 bit issue. You just launch this tool and it automatically rewrites and comments your code.
Now lets get to the data connections as there are definitly more options. I have been elaborating with these two: You can build .odc connection in the workbook from the .odc connection on the target machine or you can build the connection string on your own from the VBA code. I chose the second option in the final version, but I leave this up to your considerations and what better suits your environment. You can also see that there is Auto_Open VBA macro code being executed for every file injection. I will focus on this next time, you can just skip this step for now.
I think that I will do more posts on VBA macros in Excel in the future. I would also like to share my experience with the way you can deliver and secure data delivered to your Excel report users using VBA.
Application.DisplayAlerts = False
Dim MyFolder As String
Dim SrcWks As Worksheet
Dim Wkb As Workbook
Dim file As Variant
Dim delfile As Object
Set SrcWks = ThisWorkbook.Worksheets("_yourFileName_")
MyFolder = ThisWorkbook.Path & "\"
file = Dir(MyFolder)
While (file <> "")
If InStr(file, "_yourFileName_") = 0 And InStr(file, ".xlsx") > 0 Then
Set Wkb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & file, ReadOnly:=False)
MsgBox ("Injecting _yourWorksheetName_ sheet with ext.data connection into: " & MyFolder & file)
For Each delfile In Wkb.Sheets
If delfile.Name = "_yourWorksheetName_" Then delfile.Delete
Dim strConnection As String
Dim WBC As WorkbookConnection
For Each WBC In ActiveWorkbook.Connections
strConnection = WBC.Name
If InStr(WBC.OLEDBConnection.Connection, "_yourConnOLAPName_") = 0 Then
With ActiveWorkbook.Connections(strConnection). _
.CommandText = .CommandText
.CommandType = .CommandType
.Connection = .Connection & ";CustomData=""_yourConnOLAPName_"""
.RefreshOnFileOpen = True
.SavePassword = False
.SourceConnectionFile = ""
.MaxDrillthroughRecords = 1
.ServerCredentialsMethod = .ServerCredentialsMethod
.AlwaysUseConnectionFile = False
.RetrieveInOfficeUILang = False
.Name = .Name
.Description = ""
Wkb.ShowPivotTableFieldList = False
file = Dir
Application.DisplayAlerts = True