Why does the path appears in some function formulas but not in others?

[schnurr]

Hi everyone,

I'm been lurking around the forum for a while but this is my first posting. There's an impressive group of skills in this group. I've looked at many sites but haven't seen this type of problem discussed anywhere. Apologies for the long post.

Thanks in advance for any help or direction.

================

We have an Excel product that has multiple functions that can be used in formulas in user's workbooks. It also has some api functions that can be called from user-written VBA code.

We allow our users to install multiple instances of the xla in "c:\program files\dev\XXXX.xla" and "c:\program files\prod\XXXX.xla". The xla's stored in separate folders may be different versions or the 2 xla's be exact copies of each other. Quite often, a user will create a workbook based on c:\program files\dev\XXXX.xla. This workbook may have links and/or references to their "dev\XXXX.xla". Once they are happy with the workbook, they'll distribute it to other users who do not have a "dev\XXXX.xla". These users will open their "prod\XXXX.xla" first and then open the workbook.

When the workbook is opened, we have WorkbookOpen code that does the following to the user's workbook:

Looks at each reference to see if there's a reference to any XXXX.xla
If the x-th reference refers to an XXXX.xla that is not the currently-running XXXX.xla, this reference is deleted with:
WB.VBProject.References.Remove WB.VBProject.References.Item(x)If no reference is found to the currently-running XXXX.xla, a reference is added (in this case, to "dev\XXXX.xla")


Similar logic is also performed to redirect links. If a link is found to another XXXX.xla, then we change that link to the currently-running XXXX.xla using:
WB.ChangeLink sLink, sCurrentXLAPath, xlExcelLinks
where sLink is a string containing the link of the x-th link and sCurrentXLAPath is a string containing the fully pathed name of the currently-running xla.



This approach seems to work reasonably well but....



Sometimes we have situations where some of the cell formulas that use MyFunction1 have the old path in them:

='C:\Program Files\dev\XXXX.xla'!MyFunction1("parm1","parm2")

and some other cells (same worksheet, same function) don't have the path in them. These look like:

=MyFunction1("parm1","parm2")

When I walk through the code, the ChangeLink line that should change the link from "dev\XXXX.xla" to "prod\XXXX.xla" returns an error code of 0. But, later when I check the links, the workbook now has 2 links (one to "dev\XXXX.xla" and one to "prod\XXXX.xla"). I would have thought/hoped that it would just contain a single link to the dev xla.

I'm running Excel 2007 but we've seen similar behavior for users running Excel 2003.

I can't figure out the pattern as to why some functions get "redirected" but others don't.

This problem has existed for a long time and I haven't been able to figure it out. It has aged me greatly.


Any thoughts are greatly appreciated.
Thanks in advance.

[Zack Barresse]

Hi there, and welcome to the board!

I guess my first question is the most obvious one. Why are you running two versions? Apparently one is a production version and one is a developer version. But having multiple versions can induce problems like this, and I'd recommend against this practice. Is there any way you can avoid this behavior? Seems like instead of trying to find a workaround for the problem, it would be better to just nip it in the bud and take care of this [byproduct] headache.

[schnurr]

Thanks for the quick response.

Having dev and prod folders is the most obvious case of when the users may have multiple instances (which may, or may not, be the exact same version of the xla). But some of the users have multiple systems running where the folders might really be c:\program files\system1 and c:\program files\system2 where both are production systems.

I suppose even if we only allowed a single instance to be installed, we would still have some issues if we allow them to specify their own installation folder. In this case, the workbook would have links or references to folder1 but another user would have the xla stored in folder2.

The decision to allow multiple instances is supported by other aspects of our products and (mostly) by the Excel component. This decision is cast in stone (unfortunately).

[Zack Barresse]

Well I'd like to have a few words with whoever cast that in stone! LOL!

There are ways around it though, but you may not like them. Jan Karel Pieterse has written about this and has (IMHO) the best online resource guide for it, and it's exactly about this very issue. Please, if you read anything, read this....

http://www.jkp-ads.com/Articles/FixLinks2UDF.asp

Of course there are other tools you could take a look at...

http://www.oaltd.co.uk/mvp/
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1064
http://office.microsoft.com/download/afile.aspx?AssetID=AM103385041033 (same link as above, but hosted on MSFT site)
http://www.dailydoseofexcel.com/archives/2009/05/04/finding-external-links-in-data-validation/

But really, the methodology of having two add-ins in actual post-production use just flat sucks. I can see it for development purposes only, or versioning, but swapping back and forth? Yuck. I wouldn't want to do that. Good luck. :)

[schnurr]

Thanks.

The FixLinks2UDF.asp article basically has 2 steps:

1. Redirects any link that is like "*" & ThisWorkbook.name to ThisWorkbook.name.

Our code attempts to redirect any link that looks like it points to another instance of our xla to our currently-running xla. But sometimes the ChangeLink command results in a new link being added but the old link remaining. I don't understand why the old link remains.

2. It searches the workbook and replaces "old path + UDF" with "new path + UDF".

I would've thought that it replace "old path + UDF" with just the UDF. We have about 15 UDF's so we would need to cycle through worksheets and UDF's. If the user is lucky enough to see the old path in the formula, we instruct them to do find and replace of the old path with an empty string... but that's hardly an acceptable approach.

I don't feel so bad about having been confused about all this for so long :dunno

[Zack Barresse]

The link to the KB above also shows code for searching through formulas and replacing the path.