This discussion is archived
1 2 3 Previous Next 35 Replies Latest reply: Sep 24, 2013 1:07 PM by mjw RSS

Excel automatically changes formula to smart view

DHolz Newbie
Currently Being Moderated
Hi,

I created a SmartView template in Excel03, which is meant to be used by our business units worldwide. The problem I have is that as soon as any external party opens my template the original commands like e. g. +=HsDescription("System92v2","Account#"&B9)+ is automatically adapted to +='V:\Hyperion\SmartView\bin\HsTbar.xla'!HsDescription("System92v2","Account#"&B9)+.

FYI: The external party opens SmartView Excel via a Citrix instance. I use a local installation of SmartView. Strange enough, when I open the file in the Citrix instance of Excel SmartView the formulas don't change. I have also tried to save it in the Citrix instance and sending the file out, hoping that the aspect won't change, but it didn't help.

Anyone has an idea how I can make sure, that the original formulas remain as I keyed them in? The emergency solution for now is that I ask our business units to manually find +='V:\Hyperion\SmartView\bin\HsTbar.xla'!+ and replace it with nothing. But this isn't really user-friendly for a template which is supposed to be used monthly.

Thanks for any ideas,
Daniel
  • 1. Re: Excel automatically changes formula to smart view
    MattMilella Journeyer
    Currently Being Moderated
    What version smart view are you running? It seems weird that this function is getting referenced from the toolbar xla file. Does this happen for all functions or just this one?
  • 2. Re: Excel automatically changes formula to smart view
    user9954238 Newbie
    Currently Being Moderated
    This happens three ways in our experience (with 9.3.1.5-6):

    - Smart View is installed in different folders on the machines. Therefore Excel does not find the addin in the same path as for the user that saved the file and thus writes the whole path name in front of HsGetValue. That's why we advise all our user to install SmartView in the default path.

    - You first save the file on the drive where SmartView is installed (e.g. under C:\Hyperion\SmartView) and then save/move it on/to a different drive (e.g. m:\). Excel (at least up to 2003) will assume that SmartView folder has moved as well and thus writes m:\hyperion\SmartView in front of the formulas. We try to avoid that.

    - Sometimes users ran in to runtime Error #53 with HsAddIn.dll and did not have a toolbar anymore. The suggested fix from the forum was to copy the respective .dlls to C:\Documents and Settings\<user>\Application Data\Microsoft\AddIns\and then reregister them from there. Hence, it is installed differently than for the user opening the file and thus the pathname in front of it. We haven't had a user with the runtime error for a while, so we could not yet test if the fix would do from the \SmartView\Bin folder as well.
  • 3. Re: Excel automatically changes formula to smart view
    SabrinaG Journeyer
    Currently Being Moderated
    We are having the same problem. I have a file with HsGetValue functions saved on my network drive. When I open it with my local install of Excel, it's fine. If I open it with the Citrix Excel, the path 'C:\Hyperion\SmartView\Bin\HsTbar.xla'! is placed in front of each HsGetValue functions. If I save this file in Workspace, anyone who opens gets it from workspace and opens it has the same issue. How can we prevent this from happening?

    Sabrina
  • 4. Re: Excel automatically changes formula to smart view
    MattMilella Journeyer
    Currently Being Moderated
    I have not seen this so I cannot be 100% sure but if you apply cell protection to the formula cells and protect the sheet does this prevent it from changing. Something to try...

    Also what version of the SV client are you using?
  • 5. Re: Excel automatically changes formula to smart view
    SabrinaG Journeyer
    Currently Being Moderated
    We're using 11.1.1.3. Applying cell protection doesn't help, sorry.

    Sabrina
  • 6. Re: Excel automatically changes formula to smart view
    MattMilella Journeyer
    Currently Being Moderated
    OK few more questions and things to try.

    What version of office?

    Can you make sure the add-in HsTbar.xla is loaded on the machine where you are having issues. Those functions exist in that file and if it cannot find that file it may try to path to it...
  • 7. Re: Excel automatically changes formula to smart view
    ShajiP Newbie
    Currently Being Moderated
    Like the previous post mentioned, this happens when the path to HsTbar.xla file is not accessible in the Citrix environment. You can check this file is accessible for the logged in Citrix user?
    Also, check HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options\OPEN key and see whether this is pointing to correct location of HsTbar.xla. (Note: check the office version no in the registry key path10,11,12 etc., this may be different for you depending on the office version).
  • 8. Re: Excel automatically changes formula to smart view
    SabrinaG Journeyer
    Currently Being Moderated
    On the Citrix box, the HsTbar.xla file is on its K: drive. On my computer, it's on my C: drive. I understand that this is what's most likely causing the problem (although other users whose HsTbarl.xla files should be installed in the same place as mine have the problem as well). However, this shouldn't really be something we have to deal with. The Citrix install of Smart View works fine. If I start a new file with HsGetValue functions, I wouldn't have any problems. It's only when sharing a file with the function that I have problems. This makes no sense to me. I should be able to give these reports to other people and they should be able to run them without having this problem.

    To answer the previous poster's question, I'm now on Excel 2007, but the issue has existed since our Excel 2003 install.

    Sabrina
  • 9. Re: Excel automatically changes formula to smart view
    MattMilella Journeyer
    Currently Being Moderated
    OK last thing for you to try.... In the workbook in question (the one without the path in the formulas) add a VBA module and add these declarations:

    Declare Function HsGetValue Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
    Declare Function HsSetValue Lib "HsAddin" (ByVal Value As Variant, ParamArray MemberList() As Variant) As Variant
    Declare Function HsGetText Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
    Declare Function HsLabel Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
    Declare Function HsDescription Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
    Declare Function HsCurrency Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
    Declare Function HsSetText Lib "HsAddin" (ByVal text As Variant, ParamArray MemberList() As Variant) As Variant

    These are the cell function declarations that SV is looking for. I am not sure this will work, it is a long shot, but I am thinking that if it can find these declarations locally then it will not try to path to the xla file where they are stored...

    Remember it is a long shot...
  • 10. Re: Excel automatically changes formula to smart view
    SabrinaG Journeyer
    Currently Being Moderated
    A long shot is better than no shot. Thank you, I'll give it a try.

    Sabrina
  • 11. Re: Excel automatically changes formula to smart view
    SabrinaG Journeyer
    Currently Being Moderated
    Unfortunately, it didn't work. Thanks anyway though.

    Sabrina
  • 12. Re: Excel automatically changes formula to smart view
    616578 Explorer
    Currently Being Moderated
    Whenever a Smart View function gets transformed into a formula with the path and HsTbar.xla in front of the function, this simply means that the Smart View add-in cannot find the HsTbar.xla file. It will show the path from the last time it was successfully executed and saved.

    The add-in looks for the xla file in the same location Smart View was installed. This is usually SmartView/Bin.

    Have you checked that the user has permsission to this XLA file on Citrix?

    Also, this is from the XLA file comments:

    ' Note: this assumes the HsAddin.dll is in the path of the Excel process.
    ' If it is not, these functions cannot be used.
  • 13. Re: Excel automatically changes formula to smart view
    MattMilella Journeyer
    Currently Being Moderated
    Sabrina,

    Have you been able to look at permissions. It has been over 10 years for me on citrix but I do remember permissions being tricky... See if you can access that xla file as the user logged into citrix. Maybe try to rename it or edit it and see what happens. If you do not have permission to that file this could be your issue as Mike states.
  • 14. Re: Excel automatically changes formula to smart view
    SabrinaG Journeyer
    Currently Being Moderated
    I can use Smart View and its functions with no problems on Citrix. It's only when opening a file containing these HsGetValue functions, that was created on another computer, that I have issues. I don't understand why the HsTbar file has to be in the same location as it was when the file was created - that just doesn't make sense to me, but apparently that's what has to be the case.

    Sabrina
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points