Templated Excel workbooks with PowerPivot and Macro’s

A often asked question is: “Can I create a templated report where I can create a PowerPivot workbook for my customers that will point to a database on the customers network”. This can be very useful for example as an ISV where you have a product that is installed on a customers domain and has all its data in a database but the database server name is different at each customer.  You still want to give out a PowerPivot workbook that just automatically gets its data from the right database so they can do their analysis on their own data, It would be very nice if we can package the PowerPivot workbook with the client application when installing on the clients machine.

This used to be nearly impossible, the good news is with Excel 2013 you can use Macro’s to do all kinds of magic :).

Imagine I (the ISV) have created the following report in Excel 2013 based on the database that comes with the product that I deliver to the customer.

I created a single connection that points the data model to the database that contains the information that I want to analyse, I gave the connection a custom name called “DWConnection”:

Nothing unusual yet.  Now I want to make sure that when the workbook is opened on the clients machine it points to customers database and not the database as was configured when creating the workbook. I want to make sure the destination is configurable during installation so it can be picked up during workbook load. For example we could add a registry key in the client installer the ISV supplies we that contains the database and server name.  Now I want to use the information from the registry to update the connection in the PowerPivot workbook when the client open the workbook.

I have written a macro that will do that for us, this will check if the connection exists in the workbook and get the information from somewhere (registry/file/etc) and update the connection in the workbook if need. If we updated the connection string it will refresh the connection.

Sub ChangeConn()
'If registry check is true
Dim connectionstring As Variant
'Check if the connection exists
For Each cn In ThisWorkbook.Connections
    If cn.Name = "DWConnection" Then
        connectionstring = cn
        Exit For
    End If
Next cn
Dim Servername As String
Dim DatabaseName As String
'Can get info from everywhere like registry or file
Servername = "MYSERVERNAME"
DatabaseName = "MYDATABASENAME"
'Connection string found in workbook
If Not IsEmpty(connectionstring) Then
    'Right connection string not found, update it
    If InStr(ThisWorkbook.Connections("DWConnection").OLEDBConnection.Connection, Servername) = 0 Then
        With ThisWorkbook.Connections("DWConnection") _
           .OLEDBConnection
           .BackgroundQuery = False
           .CommandType = xlCmdTableCollection
           .Connection = _
           "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=" + Servername + ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=temp;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=" + DatabaseName
           .RefreshOnFileOpen = False
           .SavePassword = False
           .SourceConnectionFile = ""
           .SourceDataFile = ""
           .ServerCredentialsMethod = xlCredentialsMethodIntegrated
           .AlwaysUseConnectionFile = False
       End With
       'Refresh the workbook using the new connection
       ThisWorkbook.Connections("DWConnection").Refresh
    End If
End If
End Sub

That’s it .. now all we need to make sure of this gets executed when the workbook is started, we can do that with a Workbook_Open() function.

Private Sub Workbook_Open()
Call ChangeConn
End Sub

You have to make sure the code is running in the ThisWorkbook part of the Excel workbook to make sure the Workbook_Open actually gets run. Ow and also make sure you save the workbook as XLSM instead of XLSX. That’s all there is to it, as you can see the Excel macros make it very easy to manipulate the data model. You can download the sample workbook here.