Saving and Re-using a Data Model based on other Excel Worksheets

danrofohio

New Member
Joined
Mar 14, 2012
Messages
4
Hello,

I want to create a data model based using two Excel workbooks, then I want to save the data model and use it in other workbooks. Basically, I want to be able to add measures, columns, tables, relationships, etc. in the future and have all my workbooks using that data model reflect the changes I make. I think I need to create the data model in "Get and Transform Data" by clicking on Get Data/From File/From Workbook. Then in the pop up window, I select the first worksheet (tab) that the data is on, then click "Load To" button. On the next pop up window, I select "Only Create Connection" and check "Add this data to the Data Model".

Then I am going to the Power Pivot Window where I add the second workbook by clicking on "From Other Data" in the "Get External Data" section, selecting "Excel Files" at the bottom and going through the steps to select the correct table. However, I am never sure that Excel is adding that second file to the data model. Then in Power Pivot, I am adding columns, measures, and relationships between the files. Next, I click out of Power Pivot, and I never feel confident that my changes are being saved or added to the model since there is no message or dialogue to indicate that.

After that, I go back to my worksheet where I am creating the data model and bring up Queries and Connections. There I only see the first worksheet I imported listed under queries. Under Connections, I see listed ThisWorkBookDataModel and the second worksheet that I imported. I right click on ThisWorkBookDataModel, Properties, Definition, Export Connection File. When the Explorer windows pops up, I change the file name to "TestNewModel" and hit save.

After that, I create the new workbook where I want to use the saved datamodel, and I go to Get and Transform Data. From there, I click on "Get Data Using Existing Connections". In the pop up screen, I see "ThisWorkBookDataModel" under Connections on this Computer, but nothing with the name of TestNewModel, I saved earlier. (After repeating the test three times, I saw three "ThisWorkBookDataModel" datamodels listed with identical names.) After I click on that, I always get the same error message:
Unable to open or read this connection file. Either the file has been damaged or the format is not valid.

What am I doing wrong? Is there a better approach to this that I should be using?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top