Using the Create CubeFile Method

If you are in need of something a bit more automated, you can use the CreateCubeFile method. This method creates a cube file from a PivotTable report that is connected to an OLAP data source:

Sub CreateCubeFile()

ActiveSheet.PivotTables("PivotTable1").CreateCubeFile File:="C:\CustomCubeFile.cub" End Sub

The benefit of using this method is that the data in the offline cube file will consist of the exact data that existed in the pivot table at the time you executed the procedure. For example, if your pivot table contains a page field that is filtered to show data for only the United States, then the offline cube that is created by the CreateCubeFile method will contain data only for the United States. This is because the CreateCubeFile method essentially runs the MDX query behind the pivot table and outputs the results to a local .cub file. So if you have the need to create several offline cubes, each containing a different set of data, you can simply automate the rearranging of the data fields with the pivot table (using the VBA techniques covered in Chapter 7), and then employ the CreatCubeFile method.

Note that you may get a security warning when trying to open a cube file. This is a normal security feature that Excel employs in order to protect you from malicious datasets. Simply click the Enable button on the Security notice to allow connection to the cube file.

0 0

Post a comment