Distributing sheets and local cubes
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 |
Distributing sheets and local cubes
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. -------------------- From: "Frederic" Subject: Distributing sheets and local cubes Date: Tue, 29 Jul 2003 01:20:44 -0700 Newsgroups: microsoft.public.excel.programming 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? |
All times are GMT +1. The time now is 02:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com