如何从Excel VBA释放进程COM服务器对象 [英] How to release inprocess COM Server object from Excel VBA

问题描述

如何强制Excel（2007）VBA发布对COM服务器对象的引用？

How do you force Excel (2007) VBA to release references to a COM server object?

我写了一个inprocess Foxpro 9 SP2从我的开发机器上的Excel 2007 VBA代码实例化。 Excel似乎持有对COM对象/ dll的引用，即使我设置它= Nothing。这阻止我重建DLL，因为文件访问被拒绝TestCOM.dll消息，直到我退出Excel，这是一个痛苦每次我想要更改和测试它。

I have written an inprocess (Single instance DLL) COM Server in Visual Foxpro 9 SP2 which is instantiated from Excel 2007 VBA code on my development machine. Excel seems to be holding a reference to the COM object/dll even though I set it = Nothing. This prevents me from rebuilding the DLL due to a "File access is denied TestCOM.dll" message until I quit Excel which is a pain everytime I want to make a change and test it.

我已经将代码下载到一个非常简单的测试设置：
VFP9项目（TestCOM）只有一个.prg文件，包含以下内容

I have boiled the code down to a very simple test setup: The VFP9 project (TestCOM) has just one .prg file with the following contents

DEFINE CLASS TestClass As Session OLEPUBLIC

ENDDEFINE

VBA代码如下：

Sub Test()

    Set objTest = CreateObject("TestCOM.TestClass")
    Set objTest = Nothing

End Sub

b $ b

我已经尝试删除对VBA项目中的COM服务器库的引用，但这没有什么区别。
我试过和没有DIMing对象变量，它没有什么区别。
我已经尝试创建一个新的VFP DLL项目，但问题仍然存在。

I have tried removing the reference to the COM server library in the VBA project but this does not make any difference. I have tried with and without DIMing the object variables and it makes no difference. I have tried creating a new VFP DLL project but the problem persists.

如果我将VFP应用程序/ dll构建为INPROCESS / DLL并运行VBA代码我得到这个问题，但如果我建立它作为一个OUTOFPROCESS / EXE和运行VBA代码我不会得到这个问题。

If I build the VFP application/dll as an INPROCESS/DLL and run the VBA code I get this problem but if I build it as an OUTOFPROCESS/EXE and run the VBA code I do NOT get this problem.

我发现了一个非常类似的问题， a href =http://stackoverflow.com/questions/4170330/vba-com-object-cleanup> vba com对象清理，除了我的COM服务器是写在Visual Foxpro 9 SP2，而涉及到C＃ OP没有详细解释他们如何解决问题，所以我不知道如何解决它;如果这是可能的。

I found a very similar problem in vba com object cleanup except that my COM Server is written in Visual Foxpro 9 SP2 whereas that relates to C# and the OP has not explained in detail how they resolved the problem so I don't know how to get around it; if that is even possible.

推荐答案

用于从DLL中的代码实例化COM类的过程是，Excel调用COM库层，以使用ProgID或ClassID查找您的实现。当你有一个inproc服务器这意味着它找到一个路径到你的DLL，并使用LoadLibrary将其加载到客户端进程，然后创建类工厂，并调用DLL中的方法。所以最终的结果是Excel调用你的DLL上的LoadLibrary，这将锁定文件，直到Excel调用FreeLibrary的句柄。

The procedure used to instantiate your COM class from the code in your DLL is that Excel calls the COM library layer to find your implementation using either the ProgID or the ClassID. When you have an inproc server this means it finds a path to your DLL and uses LoadLibrary to load it into your client process and then creates the class factory and calls methods in the DLL. So the end result is that Excel calls LoadLibrary on your DLL and this locks the file until Excel calls FreeLibrary on the handle.

使用COM接口，你没有控制这个。你调用CoCreateInstance（）（或从VBA中创建的对象使用New或CreateObject，它调用此Win32 API）。这个实现处理LoadLibrary和一切，直到你得到一个接口指针处理。某些应用程序将定期调用CoFreeUnusedLibraries（）尝试释放当前未使用的加载的COM DLL。默认类工厂实现维护创建的对象的计数器，可以用于确定是否使用DLL - 但是这并不总是可靠的，因为COM类作者可能不服从规则。退出Excel显然会释放文件上的锁。

Using COM interfaces you don't have control of this. You call CoCreateInstance() (or from VBA you create the object with New or CreateObject which calls this Win32 API underneath). The implementation of this handles the LoadLibrary and everything else until you get handed an interface pointer to work with. Some applications will periodically call CoFreeUnusedLibraries() to attempt to release loaded COM dlls that are not currently in use. The default class factory implementation maintains a counter of objects created that can be used to determine if a DLL is in use or not - but this is not always reliable as COM class writers may not obey the rules. Exiting Excel obviously releases the lock on the file.

当您将COM类创建为进程外服务器时，它会驻留在单独的可执行文件或DLL中，管理不同。

When you create your COM class as an out-of-process server - it lives in a separate executable or DLL whose lifetime is managed differently. Excel no longer holds a lock on the DLL and releasing the COM instance may well allow the hosting process to exit.

您可以将一个DLL转换为一个localserver（）函数， out-of-process）通过安排它由DllHost托管。如果使用OleView实用程序并找到您的类ProgId，那么您可以在代理进程（dllhost）中启用托管。这是一段时间，因为我这样做，但应该有在网上有关使用代理主机的信息。显然托管一个COM对象脱离进程使一切都变慢，并引入各种编组问题的潜力。如果你保持oleautomation兼容的接口，应该很好。

You can convert a DLL to be used as a localserver (out-of-process) by arranging to have it hosted by DllHost. If you use the OleView utility and find your classes ProgId then you can enable hosting in a surrogate process (dllhost). It's been a while since I did that but there should be information on the web about using surrogate hosting. Obviously hosting a COM object out-of-process makes everything slower and introduces the potential for various marshalling issues. Provided you keep oleautomation compatible interfaces it should be fine.

这篇关于如何从Excel VBA释放进程COM服务器对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！