Delpoying Excel Localcube

[tomgao]

Hi friends,

Whenever I have created a local cube and have created an excel report from the local cube and after I send this report + the local cube to a client. I find that they often save the report + local cube to a different directory.

The problem is that the excel actually stores an absolute location that points to the cube. So when I've created the cube on my computer it could be c:\my.cub but when the client has unziped the report it could be in d:\reports\jan\my.cub . as a result Excel often complains of not being able to find the cube.

What can I do about this ?

Thanks
Tom

 

[toddl]

Hi Tom,


You could have them edit the .oqy file with notepad. They would just have to replace the location of the cube. I don't have a suggestion on how to automate this though.


Todd

 

[tomgao]

yes the people who are viewing them are actually clients who are not IT savy...

 

[blom0344]

I'd like to jump onboard this issue. I have tried to edit .oqy files as well, but I feel very unsure how to solve problems similar to the one tomgao brought up.

Each pivottable in the excel-sheet seems to carry it's own connection cache. With VBA you can show the pivotcache.connection for all pivottables.

What I am still missing is the relationship between the .oqy file , the sheet and the pivottables.

I would be VERY thankful If someone could shed some light on it...

Ties Blom
Information analyst

 

[blom0344]

I think I finally got to the bottom of this:

1. .oqy is created when establishing the connection from Excel to the local cube, but is redundant when distributing Excel reports + cubes to clients.
2. Each pivot table caches its own connection as a link to the directory where the cube was, when the report was created.
3. I add an additional worksheet to the Excel report. With a VBA module I show the pivottablenames,worksheetnames and pivotcacheconnections in this sheet and make sure that the focus is on this sheet when the workbook is opened.
4. The last step will give the client the clue where to save the cube locally, so no problem arises in the connection.

All of this works like a charm in Excel2003, Excel2000 is not very well suited for working with local cubes...

Ties Blom
Information analyst