This content has been marked as final. Show 35 replies
Thats the thing you should be able to have 2 machines with different install paths sharing the same workbook; we have tested this and it works. With that people are seeing issues so we want to try to get to the bottom of it.
Can you tell me what functions you are using and what the data source is (HFM, Essbase, etc.)
I'm not sure I understood what you wrote correctly because you first write that you tested it with diffrent install paths on different machines and it worked, but then you say that users are seeing issues: Did you get it to work?
I would really be interested in how this was achieved because I think the bevaviour standard Excel behaviour.
Test: Try saving a HsGetValue-file on the drive where SmartView is installed, close Excel, move the saved file to a network drive and you should have a wrong addin-path in front of each formula, even on the same machine (as in point 2 of my earlier post).
The only way I can imagine Sabrina's environment to work is to have the AddIn installed on ALL local machines AND the Citrix server in the same location (ideally the default path on all machine's c:\-drive, I'd imagine, because not everybody will have named the network drives equally).
If that can't be ensured, someone will always have to live with having to replace the whole '\\.xls'!-thing with nothing for all sheets.
But I'd be glad to hear of a workaround,
I am not able to replicate this behavior. I tried the same file on machines with Smart View installed on different drives. I tried the same file on various drive letters and on a network drive.
It is true that Excel stores the path with any XLA-based functions like HsGetValue. But I am observing that if Excel is set to "Disable Automatic Update of Workbook Links" under Excel Settings|Trust Center, the inproper path is ignored and the function works properly so long as the HsTBar.xla file is accessible by Excel.
Unfortunately, I do not have access to a Citrix environment. We need to be able to replicate the behavior before we can address the issue any further.
Thats the thing you should be able to have 2 machines with different install paths sharing the same workbook; we have tested this and it works. With that people are seeing issues so we want to try to get to the bottom of it.Can you tell me what functions you are using and what the data source is (HFM, Essbase, etc.)>
Matt, I'm using the HsGetValue function with Essbase. We are on version 188.8.131.52.
Matt Milella wrote:This does work, you need to put "Public" in front of "Declare Function", like "Public Declare Function". Also unload the Hstbar.xla addin and move it our of the smartview bin folder, then go in Excel and only load the COM addin. The problem with the .xla is that Excel treats the UDFs as normal file links so when you move the file to a different drive it breaks the links. Using only the COM addin fixes the problem. The COM addin has both Excel 2003 and 2007 toolbars so there is no reason to use the .xla at all.
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...
Just tested moving an updated file from C:\ to D:\, no broken link issues.
The other benefit is that UDFs in .xla run far slower than those in COM addins, so templates should work much better using the COM addin only.
We had convinced users to live with the Find and Replace method, but now it seems with Excel 2007 it places a tilda in the filepath name because it is too long. So a formula looks like this:
Find and Replace does not work now. Currently, our suggested workaround around the workaround, is to change it in one cell and copy to others (but you have to be super careful to make sure all your cell references stay correct). Not a great solution.
Any other ideas or suggestions to fix this? None of the recommendations in this thread have helped. We are on 184.108.40.206 and the issue happens with Office 2003 and 2007, and both on local and Citrix Excel.