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"

'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") _
           .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
    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.

  • greg kramer

    Thanks Kasper. Look forward to putting this to good use. Just need my company to move from Excel 2003 to make this happen 🙂

  • Hi Kasper,
    I know this is an old post but I run into this problem now and this is a fantastic solution. How do I apply that to an Access connection that takes the file path from a cell in the workbook or a text file I sent to the client?
    Thank you

  • Hey Kasper,

    Thanks for your post!

    I had an initial connection with MS Access data source which I plan to move to sql server. Is there any way I can do it without rebuilding the model?



    • Kasper de Jonge

      Unfortunately this is not possible 🙁

  • Brett Cable

    Hi, I know this is pretty old, but hoping someone comes across it. Your method works perfectly for a data model with only one query. However, if I go to the data model, choose Home > Get External Data > Existing Connections and choose the DWConnection, I can add another query to my data model, but then the macro no longer is able to update the connection string. Do you have a suggestion for how to handle complex data models that require many queries from the same connection?