How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Question

Hi All,

I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.

Ok, let me give the background before I tell the actual problem.

We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).

Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:
" An error was encountered in the transport layer." and "Errors in the OLE DB provider. An error occured while loading the connection dialog box component for prompting."
This is obvious because, there is no trusted relationship between domain ABC and XYZ. So this error is ok.

My query is, how we can change the connection string of a pivot using excel 2003 so the user C can use the ABC domain userid and password and use the "save password" option such that he can browse the cube data. He can start from a scratch by creating a new excel and he can use "save password" option and browse the cube, but he wants to use the excel file sent by user A.

I know, this can be done in Excel 2007 by using "connection properties" under Data tab. I also know that we can change the cube name, SSAS database name, server name etc by using Microsoft Script Editior (ALT+SHIFT+F11) and use "search & replace" but we can not change the password. 

Finally my queries are:
1. So is there a way to modify the existing pivot table's connection from excel 2003 to use a diff userid and password? 
2. And ideally, if the trusted authentication returns an error while refreshing/modifying pivot table, a login prompt could have been provided in excel 2003. Is this available in any patch/hotfix/release?

Thanks in advance for your valuable feedback.

~Niteen

---

Niteen Muley

Answer 1

No great answer. I think you'll need to write a macro. The following should do it. You can add that to the individual workbook you're trying to change, or I'm sure there's some way to add it as a global macro that's available to all workbooks. (I'm not an Excel guru, but copying the workbook with the macro to C:\Documents and Settings\YOUR_USERNAME\Application Data\Microsoft\Excel\XLSTART might do the trick.)

Sub ChangeConnections()
     Dim sh As Worksheet, qy As QueryTable
     Dim pt As PivotTable, pc As PivotCache
     Dim rng As Range
     Dim sNewServer As String
     sNewServer = InputBox("New Server Name For All Connections:", "Change Connections")
     Dim sNewDatabase As String
     sNewDatabase = InputBox("New Database Name For All Connections:", "Change Connections")
     Dim sNewCube As String
     sNewCube = InputBox("New Cube Name For All Connections:", "Change Connections")
   
   
     For Each ws In ActiveWorkbook.Sheets
       For Each qy In ws.QueryTables
            qy.Connection = ReplaceConnectionString(qy.Connection, sNewServer, sNewDatabase)
            qy.CommandText = sNewCube
            On Error Resume Next
            qy.Refresh
            If Err.Number <> 0 Then MsgBox "Problem refreshing QueryTable: " & Err.Description
            On Error GoTo 0
       Next qy

       For Each pt In ws.PivotTables
            pt.PivotCache.Connection = ReplaceConnectionString(pt.PivotCache.Connection, sNewServer, sNewDatabase)
            On Error Resume Next
            pt.PivotCache.CommandText = sNewCube
            If Err.Number <> 0 Then
                Err.Clear
                On Error GoTo 0
                Application.ScreenUpdating = False
                Set rng = pt.TableRange2
                pt.TableRange2.Copy Workbooks.Add(xlWorksheet).Worksheets(1) _
                           .Range("A1")
                ActiveCell.PivotTable.PivotCache.CommandText = sNewCube
                ActiveCell.PivotTable.TableRange2.Copy pt.TableRange2
                ActiveWorkbook.Close False
                Set pt = rng.PivotTable
                Application.ScreenUpdating = True
            End If
            On Error Resume Next
            pt.RefreshTable
            If Err.Number <> 0 Then MsgBox "Problem refreshing PivotTable: " & Err.Description
            On Error GoTo 0
        Next pt
   
   
    Next ws
End Sub

Private Function ReplaceConnectionString(ByVal ConnectionString As String, Server As String, Database As String) As String
    Dim iPos1 As Integer, iPos2 As Integer
    iPos1 = InStr(ConnectionString, "Data Source=")
    If iPos1 > 0 Then
        iPos2 = InStr(iPos1, ConnectionString, ";")
        If iPos2 > 0 Then
            ConnectionString = Application.Substitute(ConnectionString, Mid(ConnectionString, iPos1, iPos2 - iPos1 + 1), "Data Source=" & Server & ";")
        End If
    End If
    iPos1 = InStr(ConnectionString, "Location=")
    If iPos1 > 0 Then
        iPos2 = InStr(iPos1, ConnectionString, ";")
        If iPos2 > 0 Then
            ConnectionString = Application.Substitute(ConnectionString, Mid(ConnectionString, iPos1, iPos2 - iPos1 + 1), "Location=" & Server & ";")
        End If
    End If
    iPos1 = InStr(ConnectionString, "Initial Catalog=")
    If iPos1 > 0 Then
        iPos2 = InStr(iPos1, ConnectionString, ";")
        If iPos2 > 0 Then
            ConnectionString = Application.Substitute(ConnectionString, Mid(ConnectionString, iPos1, iPos2 - iPos1 + 1), "Initial Catalog=" & Database & ";")
        End If
    End If
    ReplaceConnectionString = ConnectionString
End Function

Private Function StringToArray(Query As String) As Variant

   Const StrLen = 127
   Dim NumElems As Integer
   Dim Temp() As String

   NumElems = (Len(Query) / StrLen) + 1
   ReDim Temp(1 To NumElems) As String

   For i = 1 To NumElems
      Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
   Next i

   StringToArray = Temp

End Function

 

 

 

---

http://artisconsulting.com/Blog/GregGalloway