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.
Sub Distribute_Sheet() 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) Application.Run (Auto_Open) MsgBox ("Injecting _yourWorksheetName_ sheet with ext.data connection into: " & MyFolder & file) For Each delfile In Wkb.Sheets If delfile.Name = "_yourWorksheetName_" Then delfile.Delete Next delfile 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). _ OLEDBConnection .CommandText = .CommandText .CommandType = .CommandType .Connection = .Connection & ";CustomData=""_yourConnOLAPName_""" .RefreshOnFileOpen = True .SavePassword = False .SourceConnectionFile = "" .MaxDrillthroughRecords = 1 .ServerCredentialsMethod = .ServerCredentialsMethod .AlwaysUseConnectionFile = False .RetrieveInOfficeUILang = False End With With ActiveWorkbook.Connections(strConnection) .Name = .Name .Description = "" End With ActiveWorkbook.Connections(strConnection).Refresh End If Next WBC SrcWks.Copy Before:=Wkb.Sheets(1) Wkb.ShowPivotTableFieldList = False Wkb.Close SaveChanges:=True End If file = Dir Wend Application.DisplayAlerts = True End Sub