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):
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).
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:
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).
Conclusion
I have been switching Connections around like this in Excel for probably 15 years. It has allowed me to say “Yeah – no probs.” When a worried client says “Help — IT moved our server !!!” …. If it can change it will …
Now I know I can do the same with my PowerPivot data cache I am getting to the point where I wonder whether there’s anything we can’t do in Excel now (?) Nice thought eh?
Dick
Dick Moffat's Spreadsheet and BI Blog 
I discovered some time ago that you can also store DAX queries in worksheet cells and change a query in a table similar to the process you show here.
Yep … That and linking a single cell range as a Linked table to be used as a variable in your DAX shows how you can drive this whole thing from the spreadsheet itself customizing results at run-time. Then you also have all the power of VBA to drive that process and to deal with the results (printing, requerying, etc….)
Dick
Hi, we have a working solution leveraging odata api from azure appplication. But we had to rename most of the matadata (tables and fields), because api provided just technical names not useble for end users. Then we have to option to change the connection, which is pretty annoying, because in this scenario the user credentials are sent in url (hash). We are providing this solution to literally thousands of user and all of them have to change connection manually. Do you have any idea if we could overcome this somehow?
Sure … the same technqiue seems to work with DATAFEED Connections.
1. I created an atom file called “TEST.atom” with this in it:
Default
Categories
CustomerDemographics
Customers
Employees
Order_Details
2. I created a new Connection IN EXCEL using the OData Connection or the Data Connection Wizard (which I kinda prefer for its flexibility – but it’s probably six of one half dozen of the other) IN EXCEL NOT PowerPivot .
3. I checked in PowerPivot and saw that there are 5 Tables added using the Data Feed.
3. I opened the Data Connection IN EXCEL and copied the Connection String out and pasted it into a cell I named “ConnectionOData”
4. Just for fun I also created a cell named “CommandtextOData” and typed just the word “Categories” into it.
5. Then I created the following VBA Subroutine:
Sub ConnectOData()
With ThisWorkbook.Connections(“<>”).DataFeedConnection
.Connection = Range(“ConnectionOData”).Value
.CommandText = Range(“CommandTextOData”).Value
.Refresh
End With
End Sub
6. When I run the macro now I have only 1 table “Categories”.
If I wanted to use a different .ATOM file I could just change it in the Connection String cell and rerun the macro or I could ask for different tables as well by vhanging the Command Text cell – or both.
Remember – EVERYTHING YOU DO REGARDING THE CONNECTION ITSELF YOU MUST DO IT IN EXCEL.
Sure you can add Measures and Calculated Columns and such to the tables and if you source a table with the same name from another source then they will be preserved.
There ya go.
Dick
p.s. Where do I send the bill 😉 Just kidding
I haven’t checked it but you might even be able to manage your user connections by pushing their URL into the Connection string on the spreadsheet (I hope). Don’t know how that works but it strikes me that it should.
Dick
Thank you for your effort. Did you try to change the imported tables/columns? I know that if could work but the trouble is when you rename them – Excel seems to refuse change the connection string (even visually you can see it – the connection properties dialog becomes disabled). When we started our solution, I was excited that this could be done, then we continued with development, renamed the tables and at the end of the day we realized that it has to be changed manually!
If you’re saying that you want to rename the tables once the Connection is changed then yes you are screwed. This is because if you change anything about table names or field names inside the PowerPivot UI then it locks them out from the Excel Object Model.
That’s just the way it is:-). You are kind of pushing the envelope with this.
Not sure if you could use the SQL oledb provider instead of Odata because then if you used the techniqie I wrote about you could redefine any connection, change field names on your Sql as the data cimes in and each table would a name lije Query, Query1 etc…. So no need to rename tables.
Sounds like a major rewrite but it would work I would think.
Hi, I’m working on a similar issue but the powerpivot is now linked to a CSV file (Model OLE DB Query), and I want to move it to an Access (and, in the future, to an SQL) connection.
I created the new connection as in your example (ACE 12.0) but when I’m trying to run the macro, this is not working. Now I’m wondering if is possible to change also this kind of connection and if yes, how. Thanks !
I’m afraid you need to give me more info 😦 ..
Maybe you can post you code here and I can have a look ?
Thanks for your reply 😉 and yes, you’re right, I provide not enought info !
The actual connection is called “Text history” and it is a text file (csv), and I want to change in an Access connection (ACE 12.0, which is working perfectly)
Here below the code I used:
————————————————————
Sub ConnectAccess()
With ThisWorkbook.Connections(“Text history”).TextConnection
.Connection = Range(“ConnectionAccess”).Value ‘connection string copied from Excel
.CommandType = xlCmdTable ‘new command type (previously Table Collection)
.CommandText = Range(“CommandTextOData”).Value ‘table to look for
.Refresh
End With
End Sub
————————————————————
It returns the following error:
Runtime Error ‘1004’:
Application-defined or object-defined error
Hope this is enought to let you help me.
Thanks !
I am getting the same error with similar code. I think that this is because you initially created your connection inside the PowerPivot interface instead of first creating it from Excel and then reference it from PowerPivot.
This is what we did and we now have probably 50 pivot tables and graphs using that model. We are trying to move it from SQL Server to an ODBC source. I really wish that I knew that trick before I started.
If anyone has a workaround for this, it would be appreciated. Is there a way to unlock the properties when the connection was first created in PowerPivot?
Pingback: Excel 2013: Power Query OmniQueries. Dynamic And Refreshable Connections To The Data Model | Excel For You
Hy Dick,
I made my connection based by your description, but for me looks like the connection string cannot be updated. I am getting error for the simple code below
Sub updateconnection()
a = ThisWorkbook.Connections(“Con4Araz1”).OLEDBConnection.Connection
With ThisWorkbook.Connections(“Con4Araz1”).OLEDBConnection
.Connection = a
.Refresh
End With
End sub
Any suggestion would be great
Thanks
Not sure what you’re tryingbto accomplish with this code 😉 ? Can you send me some more around it ?
When I try this I get an error message when I run the VBA saying Run-time error ‘1004’ Method ‘Range’ of object ‘_Global’ failed. When I select Debug it highlights the .Connection line.
Any ideas what I’m doing wrong?
Can you send me your code in that proc and the Connection string you want to define?
Dick
Hi Dick,
We have PowerPivot data model connection set to “Oracle Provide for OLE DB” . Would it be possible (and if yes – How?), to change the data connection of the same model to target oData Data Feed ?
Our goal is that the data feed completely reflects the current model structure and relationships, because otherwise it would not work. However, we wonder if it would be possible to preserve current rather complex models (contains different calculated fields, relationships etc) and change the connection type only?
Thanks in advance!
That’s a tricky question because in some cases you CAN change the source and in others you can’t.
First of all ANY possibility of changing CommandText or Connection source would only be possible IF YOU CREATED THE CONNECTION IN EXCEL RATHER THAN POWERPIVOT.
And in Excel I have found switching from Access to SQL (if you used an OLEDB Connection) works fine. It does though appear to me that it is based on the Connection “Type” and it appears as though the Type cannot be changed from VBA. So while SQL and Access connections are “Type” 1 OData is “Type” 6 and I personally can’t see a way to change that.
Sorry but that’s all I can provide at this point. Not sure if that helps you though 😦 ..
Dick
Further …. I would bet anything that this is exactly the same if your Connection is to Oracle and you want to switch it to OData.
Dick
Pingback: How To Change Connection Type In Power Pivot | Dick Moffat's Spreadsheet Blog
@Dick
A good practice from Excel 2013 onwards is always use Power Query to fetch the Data and load it to the Data model
Its pretty easy to change the connection to a different data source in Power Query using a function that looks at the value of a cell
Hey Sam …
I agree that you can use Powerquery/Get and Transform for this but I do not think it is so much as case of “a good practice” but rather “another way to do this” 🙂 … I love me some PowerQuery and use it all the time where I have not got control over my data sources.
I like this technique using Connection strings and VBA in that I tend to do most of my “transformations” in the source of my data (i.e. Access, SQL Server or SQL Azure) and then just bring the results of queries or functions directly into my tables inside the “Data Model”. Then I can drive changes to this from a table of Connections, Sources and SQL Statements. This way I recycle the same code and table and just set up my list and away we go.
I find PowerQuery VERY useful for importing text reports and for transformations where I have no control over the source data.
I do not agree that using PowerQuery is the only “good practice” and kind of wish you hadn’t put it that way. Let’s just say that there is another way that is equally complex but that also works 🙂 .. ok?
Dick
Transformations in Access / SQL Server are limited to what can be achieved via SQL
Eg Try doing a Unpivot Other Columns in SQL – not easy
I did a project recently where the Database was Access (for Testing) and SQL Server (for Production)
In Power Query you could create a simple function
let fGetDataDB=(QryNm)=>
if fGetCellVal(“VBA_DB_TYPE”) = “SQLSERVER” then
let
Source = Sql.Database(fGetCellVal(“VBA_DBQ”), fGetCellVal(“VBA_PROVIDER”)),
DATA= Source{[Schema=”dbo”,Item=QryNm]}[Data]
in
DATA
else
let
Source = Access.Database(File.Contents(fGetCellVal(“VBA_FPN1″))),
DATA = Source{[Schema=””,Item=QryNm]}[Data]
in
DATA
in
fGetDataDB
The User could choose the Database in a cell drop-down and Power Query would pull data from that source
Exactly .. If you have no control of your data source and recieve it in a format that you need to UnPivot then that’s where I use PowerQuery …
Hi Dick,
thanks for your posting, very interesting.
I have actually the following problem: I have a Power Pivot where originally was set up to connect to an Access db. This connection setting was done in the Power Pivot Window, and after have been created lots of Formulas Pivot Table etc. Now we have a MS SQL db that contain the same tables: If i follow your step i can see that i have a connection named “PowerPivot Data” in the Excel UI which is the one created to retrieve data from Access db. If i create one pointing to a SQL Server in the Excel UI and develop the macro you suggest i receive an error after the refresh.
Is this scenario right to not work? How can we ultimately change the connection?
Thanks in advance
Luca
Not sure from what you told me whether you changed the Connection string to support connection to SQL Server (?) If you did then it should work.
Did you do a manual connection to your SQL source and get data back..? If so then you can steal the Connection string from that Connection and use that in your new VBA-driven Connection 🙂
Dick
Hi Dick
Can the method you describe above also be used to change your connection that you have in a pivot table from the existing Power Pivot model to a Power BI Service based datamodel?
Or is that a completely different approach, that I need to take to that?
best regards
Jørgen
Can you explain this a little more ? Are you suggesting inside Excel or converting from Excel to PBID ?
Hi again,
originally we had some very huge Excel files with Power Pivot fact and dim tables. As these data should be used for several new reports in Power BI, as well as we wanted to be able to send very thin Excel reports to colleagues,
we took the Excelfiles and imported them in Power BI Desktop
All Tables and measures were converted to Power BI
From Power BI Desktop we published to Power BI Service
– we get a scheduled update in this way
– we can use an on premise gateway to update our data
Afterwards We Analyze with Excel to get a spreadsheet, that have a pivot table, which we convert to formulas.
We import all sheets, that is using cubeformulas, and replaces the old connection “ThisWorkbookDatamodel” to our new PowerBI datamodel
We rebuild the Pivot tables and refers them to our PowerBI datamodel and deletes the old Pivot tables that is build on the power pivot datamodel
Finally we remove all connections, named ranges etc, that refers to the old Power pivot model.
In this way we creates a model that is 1 mb large instead of 50 – 100 mb large. It is lightening fast compared to the old models.
The only problem is, that it takes hours to recreate those pivottables and we probably have 100 excel files, that are build based on power Pivot. So If we could have a bit of code, like the one you showed above, then converting these models will be a lot easier.
As far as I can see on the internet, it seems like an increasing issue, that finance people is stuck with their old power pivot models, and not being able to use the best from both the Excel/Power Pivot world and the Power BI world.
I hope that described my problem in more detail.
regards
Jørgen
Yes I see Jurgen ..
Your scenario is EXACTLY what we built our new addin (XLPublish – http://www.xlpublish.com ) to solve….
That’s pretty exciting the effort you put in to getting all this working .. amazing… But with our addin you will be able to simplify it massively and maintain a small footprint in the Cloud at the same time.
I would be glad to arrange a Join.me session for you to show it to you later this week .. any time (looks like you might be in Northern Europe somewhere 🙂 ?? ) …
YOU are our target market my new friend … the pain-points we knew had to be resolved.
Dick
Hi Dick,
well I am not that new to you – I enjoyed your company for the summit in Amsterdam a couple of times :-). I am in Denmark, so yes Northern Europe
If you can help change the connection string for the existing models then I will be ready to see xlpublish ASAP. I only have a meeting at 14.00 Copenhagen time today, otherwise I will be waiting for a demonstration.
If you can see my mailadress, please contact me, otherwise I will send you a mail as well.
I remember you now … hello again.
11:00 my time (which is 17:00 your time) works well for me?
Hi Dick
Can we make it 2 pm your time. I am on a boat and afterwards I have a short ride home. Internet connection is bad here.
Regards Jørgen
Hi Dick,
Do you send a link for a webinar, or how do we get in contact?
I just sent you a link to your email ..
I have not received it yet, but I wíll get online as soon as I get it
can you confirm your email ?
jwr@exceldoktor.dk
That’s what I sent it to …