schnurr
11-11-2009, 11:38 AM
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.
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.