Macro to Change PowerPivot Data Connection

Macro to Change PowerPivot Data Connection

Source: http://dmoffat.wordpress.com/2013/08/21/how-you-can-change-to-another-connection-type-in-powerpivot/

I have noticed that it has been noted in a couple of places that once you choose to connect to data in a particular type of data source (i.e. an Access ACCDB file or a SQL Server or MySQL database) you are then subsequently unable to change the type of data source for PowerPivot data (say if you migrate your data from Access to SQL Server after building a big PowerPivot solution).  This could have major implications on a complex PowerPivot solution with lots of DAX functionality and Pivot Tables based on the existing tables. This would be a painful process no doubt :-(

I have found, however, that  there IS a way to set up a Connection so the source CAN be changed.

Once again, to do that, you have to create the Connection from inside the Excel UI and then add it to the Data Model rather than from inside PowerPivot’s UI.

The secret is to use the generic OLEDB Connection from the “Other Sources” choice on the Data Tab (rather than the specific Access or SQL Server ones):

Connection Wizard

When you choose this option you can then choose the type of data you wish to connect to from a list of installed drivers on your machine (if for example you want to connect to something like MySQL you can just go to the MySQL site and download and install the necessary driver).

ConnectionType

If you choose, for example, SQL Server you can then step through a unique series of dialogs to find the server, database and table or view you are looking for.  Once selected remember that you have to choose to connect as a SQL statement rather than as a table (critical)

The secret is at this point is to:

1. Select the Command String text

2. Copy it to the clipboard (or you come back afterward and copy it once it is created)

3. Once you have clicked ok through and created your Connection you just copy what’s in the Clipboard to a cell in your spreadsheet .. like this:

Conenction Strings

Notice I did the same thing for a connection to an Access database using the ACE 12.0 provider.  I can have as many of these as I want or need.   I could also convert these strings to formulas (formulae ?) so I can type my variables (server, database, table) into other cells and concatenate them back together to create a functioning Command String (Cool eh?).  This way you can change your variables as easily as typing the path to the new Access database or the name of the new Server they moved your app to and the next time you run the code you’ll see below you’re connected up. Sweet !

Thankfully Excel generates these Connection Strings  for you for free so you don’t need to figure out all the parameters in there.  There are probably some that show up that you really don’t need because they’re just reflecting the default anyway.

But there is one I REALLY like to change – I always replace “Share Deny Write” with “Read” (not that it matters actually but it’s good form and MAY prevent collisions with other users of the source solution or PowerPivot – better safe than sorry).

So now I have a cell named ConnectionSQL and another ConnectionAccess (if you don’t know about or use Names now you’d better do it right now or you’ll be sorry some day – trust me :-) ).

I can then create 2 very simple pieces of code (please forgive the formatting but I want you to be able to copy it):

Sub ConnectSQL()

With ThisWorkbook.Connections(“<<Your Connection Name>>”).OLEDBConnection

.Connection = Range(“ConnectionSQL”).Value

.Refresh

End With

End Sub

Sub ConnectAccess()

With ThisWorkbook.Connections(“<<Your Connection Name>>”).OLEDBConnection

.Connection = Range(“ConnectionAccess”).Value

.Refresh

End With

End Sub

So you can switch connections just by running one or the other Procedure (of course you could make it sexier but this is just to show you the functionality :-) ).

After running one or the other sub check to see the change in the Connection String and also look at the change in the data in PowerPivot (if the tables are in fact different in some way – like more or less records – in the two sources).