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,
This happens three ways in our experience (with 126.96.36.199-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.
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?
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?
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...
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).
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.
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...
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.
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.
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.