Distributing sheets and local cubes

  • Thread starter Thread starter Frederic
  • Start date Start date
F

Frederic

What is the best way to distribute an Excel sheet based on
a local cube through the enterprise?

I am creating a local cube, I am able to define a Pivot
Table report based on this cube, but as far as I can tell
the location of the cube (say c:\Cubes\testcube.cub) is
hardwired in the Excel pivot table report.

When I want to send the sheet and the cube file per email,
the recipient saves the cube locally, and consequently has
to start over creating the pivot table report, just to
specify the location of the cube.

Is there a way to do this where the recipient doesn't have
to redefine the pivot report?



tia
 
Hello Frederic,

You are correct that the full path of the cube file is saved with the
workbook. One approach to prevent from having to reconnect the cube file
manually is to always put the cube file in the same folder as the workbook.
Then you can use a macro in the workbook that runs in the Workbook_Open
event procedure. This is found in the ThisWorkbook code module. For
example:

Option Explicit ' <- very top line of the code module

Private Sub Workbook_Open()
Dim sDocPath as String
sDocPath = ThisWorkbook.Path
MsgBox "The path to this workbook file is: " & sDocPath
End Sub

The Workbook_Open procedure above will automatically run every time the
user opens this workbook. As you can see, you can obtain the path of the
workbook using the ThisWorkbook.Path. Knowing the path of the workbook,
then you can change the LocalConnection property of the PivotCache to
modify the path to the .cub file. Also, the macro can set the
UseLocalConnection property to True. Here is an example. This is similar
to the example found in the Excel VBA help topic for the UseLocalConnection
property:

With ThisWorkbook.PivotCaches(1)
.LocalConnection = _
"OLEDB;Provider=MSOLAP;Data Source=" & ThisWorkbook.Path &
"\MyCube.cub"
.UseLocalConnection = True
End With

A "not confirmed" option for this scenario is when the Multi-Dimensional
Data Source dialog appears, asking for a data source, try clicking OLAP
Server (even though you are actually using a cube file) and then enter the
cube file's name with no path. This might "trick" the dialog into
supporting the relative path and will then look for the cube file in the
same folder as the .xls file. I have not tested this scenario, but you may
want to try it before exploring the macro option.

Best regards,
Greg Ellison
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

--------------------
 
Back
Top