8 Replies Latest reply: Feb 27, 2012 11:59 AM by JDN RSS

    Migrating application of a worksheet/book

    JTF85
      We recently went through an HFM application upgrade, and in the process we changed the name of the application. We have some workbooks that we just can't seem to get completely migrated over. The situation is where a refresh is used on each individual sheet, it prompts only for the new application, when a refresh all is used it prompts for the ID and password of the old application. The workbook has multiple sheets, but they all utilize the hsgetvalue function, and do not utilize any ad-hoc functions. I have explored the following steps, but to no resolution.

      1.) Find/Replace in Excel (Formulas, and Values entire workbook) find the old name and replace with the new name

      2.) Create a vb macro to go through every single cell in each sheet (Hidden, VeryHidden, and visible) the workbook and do a replace in the range.formula

      3.) Select Hyperion->Activate->New Application in each sheet (Hidden, VeryHidden, and visible)

      4.) Used the member select option and selected a member in each sheet in case some sort of ad-hoc function was there.

      Any other ideas would be greatly appreciated!

      Thanks
      JTF
        • 1. Re: Migrating application of a worksheet/book
          JDN
          When we upgraded to a new version, we had a problem where the old connections somehow carried over to the new version of Smartview but were pointed to incorrect server names, etc. and were causing problems. Once we deleted out those old connections from the Private Connections, everything worked fine. I'm not sure this is what you are describing below, but thought I would throw it out there as a possibility.
          • 2. Re: Migrating application of a worksheet/book
            JTF85
            While that may work, and I understand the logic there, we need to maintain the old connection for historical purposes, they both are private connections. I'm thinking there's got to be some sort of hidden object I just can seem to find.
            • 3. Re: Migrating application of a worksheet/book
              JDN
              What version did you go from and to and what version of SV are you on?

              Thanks.
              • 4. Re: Migrating application of a worksheet/book
                JTF85
                We went from 9.3 to 11.1.1.3 w/ the 500 patch.

                JTF
                • 5. Re: Migrating application of a worksheet/book
                  JDN
                  I guess the only thing I would try is moving one or groups of sheets to a new workbook until you can narrow it down to the sheet(s) causing the problem. Sorry I can't be of any more help on this one.

                  Regards,
                  Jason
                  • 6. Re: Migrating application of a worksheet/book
                    JTF85
                    Tried that, and should have put it in the initial question, if I copy the cells of the worksheets to a new book, it resolves the issue, if I copy the sheets it does not, so it's something stored in the sheet. We have quite a few workbooks with this issue, and some relatively complex VBA code that make copying the cells out to a new workbook sound easier then it really is. I can on occassion narrow it down to specific sheets, but still no resolution there.

                    JTF
                    • 7. Re: Migrating application of a worksheet/book
                      JTF85
                      Thanks for your help JDN, I finally found it in case anyone else happens to come across this. It was in the Custom Properties in the sheets. Below is the main part of the code I used to correct the issue. Of course there are other parts of my Macro, but this should get the idea across.
                      'ws is a reference to the current worksheet
                      'OrigApp is a reference to the OLD application
                      
                          For x = 1 To ws.CustomProperties.Count
                                  CPName = ws.CustomProperties.Item(x).Name
                                  CPValue = ws.CustomProperties.Item(x).Value
                                  
                                  If InStr(UCase(CPName), OrigApp) Then
                                      Resp = MsgBox("Would you like to Delete the Control Property Named " & CPName & vbCrLf & _
                                      "With a value of " & CPValue & vbCrLf & _
                                      "On Worksheet " & ws.Name, vbYesNo)
                                      If Resp = vbYes Then
                                          ws.CustomProperties.Item(x).Delete
                                      End If
                                  End If
                                  
                                              
                                  If InStr(UCase(CPValue), OrigApp) Then
                                      Resp = MsgBox("Would you like to Delete the Control Property Named " & CPName & vbCrLf & _
                                      "With a value of " & CPValue & vbCrLf & _
                                      "On Worksheet " & ws.Name, vbYesNo)
                                      If Resp = vbYes Then
                                          ws.CustomProperties.Item(x).Delete
                                      End If
                                  End If
                          Next x
                      I hope this helps anyone that may be facing this issue.

                      Regards
                      JTF
                      • 8. Re: Migrating application of a worksheet/book
                        JDN
                        Excellent! Thank you for posting your findings.