Excel VBA macro for report sheet injection

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). _
            .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
          End If

         Next WBC

      SrcWks.Copy Before:=Wkb.Sheets(1)

      Wkb.ShowPivotTableFieldList = False

      Wkb.Close SaveChanges:=True

   End If
   file = Dir

Application.DisplayAlerts = True

End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s