vba 如何停止 Excel 存储 XLA 的绝对路径？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3308450/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to stop Excel storing the absolute path to an XLA?
提问by John Pickup
I have an XLA file that is to be deployed to a number of users in the organisation as an Excel add-in. My intention is to deploy it to a directory in the user's "documents and settings" folder in "Application Data\MyCompany". (In fact this is all working through a wrapper that copies the latest version of the XLA locally and installs it as an Excel add-in).
我有一个 XLA 文件，该文件将作为 Excel 加载项部署到组织中的许多用户。我的目的是将它部署到“Application Data\MyCompany”中用户的“documents and settings”文件夹中的目录中。（事实上，这一切都是通过一个包装器在本地复制最新版本的 XLA 并将其安装为 Excel 插件）。
However, if a user creates a sheet that references a function defined in this XLA then Excel appears to store the absolute path of the XLA in the function call. Thus, if the user sends the sheet to a colleague Excel fails to resolve the function as their copy of the XLA resides at a different absolute path (as their username is part of the absolute path).
但是，如果用户创建引用此 XLA 中定义的函数的工作表，则 Excel 似乎将 XLA 的绝对路径存储在函数调用中。因此，如果用户将工作表发送给同事，Excel 将无法解析该函数，因为他们的 XLA 副本位于不同的绝对路径（因为他们的用户名是绝对路径的一部分）。
My belief up until now was the Excel "just coped" with this as long as the XLA was installed as an add-in but this does not appear to be the case.
到目前为止，我的信念是，只要 XLA 是作为加载项安装的，Excel“就可以解决”这个问题，但情况似乎并非如此。
Is it really the case that I need to enforce an identical absolute path for my add-in for all users? This is possible within a single organisation but I honestly can't believe this is true as it seriously impedes sharing of XLS files.
我真的需要为所有用户为我的加载项强制执行相同的绝对路径吗？这在单个组织内是可能的，但老实说，我无法相信这是真的，因为它严重阻碍了 XLS 文件的共享。
Thanks.
谢谢。
采纳答案by Dick Kusleika
There's no good way to do this. I put my xla files on a network share rather than locally and install them via the UNC path. That only works for me because everyone has access to the share, which may not be the case for you. Here's some other alternatives
没有好的方法可以做到这一点。我将我的 xla 文件放在网络共享上而不是本地，并通过 UNC 路径安装它们。这仅对我有用，因为每个人都可以访问共享，而您可能并非如此。这里有一些其他的选择
http://www.dailydoseofexcel.com/archives/2008/06/02/fixing-links-to-udfs-in-addins/
http://www.dailydoseofexcel.com/archives/2008/06/02/fixing-links-to-udfs-in-addins/
回答by gerdami
I simply remove the path with a sub like this one:
我只是用这样的子删除路径：
Sub RemoveXlaPath()
'
' Goal: delete the path reference to the add-in, i.e. everything before and including the '!'
' ='C:\Program Files (x86)\Microsoft Office\Office14\LIBRARY\populator.xlam'!famedata(...)
'
Cells.Replace What:="'C:\*xla*'!", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End Sub
回答by ozmike
What I have done is if I give a user a XLS , it has in its on open event some code which - installs the the XLA as part of its on open event. It also uninstalls old version if any (delete and command bars). This self distributes. In theory it could clean up any paths. This assumes there is some shared drive which everyone can access, this prevents them copying the XLA to local drive. Alternatively email them a shortcut to the XLA with XLA on the shared drive. If possible you don't want an XLA on a local drive.
我所做的是如果我给用户一个 XLS ，它在它的 on open 事件中有一些代码 - 安装 XLA 作为其 on open 事件的一部分。如果有的话，它还会卸载旧版本（删除和命令栏）。这个自我分布。理论上它可以清理任何路径。这假设有一些每个人都可以访问的共享驱动器，这可以防止他们将 XLA 复制到本地驱动器。或者，将共享驱动器上带有 XLA 的 XLA 快捷方式通过电子邮件发送给他们。如果可能，您不希望本地驱动器上有 XLA。
If the XLA must be on a local drive - not sure if this would work but an XLS on open event could check an fix any paths and install /install a xla - if it knows where its is. But if you were emailing a XLS over the internet, the on open event of the XLS could check if the XLA is available and put up a message box telling the user what to do - install this xla, which would be a separate attachment. The XLA could clean up any paths as part of its on open event - just some ideas.
如果 XLA 必须在本地驱动器上 - 不确定这是否可行，但打开事件上的 XLS 可以检查修复任何路径并安装/安装 xla - 如果它知道它在哪里。但是，如果您通过 Internet 通过电子邮件发送 XLS，则 XLS 的打开事件可以检查 XLA 是否可用并放置一个消息框告诉用户该做什么 - 安装此 xla，这将是一个单独的附件。XLA 可以清理任何路径作为其公开活动的一部分 - 只是一些想法。
another possibility is the XLA on open event can modify the XLSs on open event so that if that XLS is distributed the XLS will be able to check if the XLA is available. Tricky.
另一种可能性是 XLA on open event 可以修改 open event 时的 XLS，这样如果该 XLS 已分发，则 XLS 将能够检查 XLA 是否可用。棘手。
回答by Harry
It's a poor oversight that makes add-ins barely manageable for shared use. Other than this, using .XLAM add-ins is a good way to avoid having to have macro-enabled spreadsheets (the shared spreadsheet can be distributed without macros and the macros can reside in an .XLAM)
这是一个糟糕的疏忽，使加载项几乎无法管理以供共享使用。除此之外，使用 .XLAM 加载项是避免必须拥有启用宏的电子表格的好方法（共享电子表格可以在没有宏的情况下分发，宏可以驻留在 .XLAM 中）
回答by Mark Hurd
Can you specify an environment variable-based path? E.g. %APPDATA%\MyCompany
你能指定一个基于环境变量的路径吗？例如%APPDATA%\MyCompany
