We have a large user community that have created a number of MS EXCEL
spreadsheets using MS OLAP Cubes as their input. Every month, once the
cubes have been updated, the users can "refresh" their spreadsheets.
These spreadsheets range from simple to complex, depending on the
user's skill set and task to be performed.

Here is the problem... We are in the process of moving the cubes to a
new server and the cube names are changing due to new Naming
Standards. What can we do to minimize the impact to our user
community?

Thank you all for your input

The following kb articles may be useful for you.

http://support.microsoft.com/default...b;en-us;269619

In addition, here's some additional code that might help you.

Private Sub Workbook_Open()

Call ChangeServer
End Sub



Sub ChangeServer()


Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Dim strOld As String, strNew As String

' Replace the following paths with the original path or
' server name where your database resided, and the new
' path or server name where your database now resides.


If MsgBox("Do you need to change the Analysis Server from: " &
CurrentOLAPstring, vbYesNo) = vbYes Then


NewPath = InputBox("Please enter the name of the Analysis
Server")

For Each sh In ActiveWorkbook.Sheets

For Each pt In sh.PivotTables

strOld = pt.PivotCache.Connection
strNew = Replace(strOld, OldPath, NewPath)
pt.PivotCache.Connection = strNew
pt.PivotCache.Refresh

Next pt

Next sh

End If

End Sub

Function CurrentOLAPstring() As String
Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim strOld As String
Dim intDataSourceBegin As String
Dim intDataSourceEnd As String

For Each sh In ActiveWorkbook.Sheets

For Each pt In sh.PivotTables

strOld = pt.PivotCache.Connection
Exit For


Next pt

Exit For

Next sh

intDataSourceBegin = InStr(1, strOld, "Data Source=")+ 12
intDataSourceEnd = InStr(intDataSourceBegin, strOld, ";")



CurrentOLAPstring = Mid(strOld, intDataSourceBegin, intDataSourceEnd -
intDataSourceBegin)



End Function


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Mike" <mike.major (AT) marriott (DOT) com> wrote

Mike

This issue can be overcome by using other approaches as well. If you want to
see how we do this then please feel free to contact me.

Howard.Taylor@ Domain below
www.o2olap.com

"Mike" <mike.major (AT) marriott (DOT) com> wrote