从vba调用.net库方法 [英] Calling a .net library method from vba
问题描述
我在ASP.net中开发了一个Web服务，c＃，并托管在IIS上，由vba客户端使用。下载了Office 2003 Web Services 2.01工具包后，我在成功创建所需的代理类（如许多用户在线文档中所述）时遇到问题，并决定创建.net dll库。我已经创建了库，它引用Web服务并将其中的一个方法暴露给c＃中的公共函数。
我现在有三个问题：
-
VBA中的dll类？我试图去工具 - >参考和浏览到dll位置，但我得到错误无法添加引用指定的文件。在磁盘上有特定的位置我必须复制.dll？
-
我还可以复制dll文件旁边的dll.config文件，
-
由于调用的方法是接受一个对象（由多个成员和一对List<>成员组成），如何在VBA代码中实现这些呢？
您需要为程序集（DLL）创建一个COM-可调用的包装器（CCW）。 .NET互操作性是一个相当深入的话题，但是相对容易得到一些东西。
首先，你需要确保你的整个程序集注册COM互操作，你可以在Visual Studio的Build选项卡上通过选中注册COM Interop来做到这一点第二，你应该在你的所有类中包括System.Runtime.InteropServices：
使用System.Runtime.InteropServices;
接下来，您应该使用 [Serializable（），ClassInterface（ClassInterfaceType.AutoDual），ComVisible（true）] 属性来装饰所有要暴露的类。这将使你可以正确地访问类成员和在VBA编辑器内使用intellisense。
你需要一个入口点 - 即一个主类，并且该类应该有一个没有参数的公共构造函数。从该类，可以调用返回其他类的实例的方法。这里有一个简单的例子：
使用System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Linq;
using System.Text;
using System.Windows.Forms;
命名空间MyCCWTest
{
[Serializable（），ClassInterface（ClassInterfaceType.AutoDual），ComVisible（true）]
public class Main
{
public Widget GetWidget（）
{
return new Widget（）;
}
}
[Serializable（），ClassInterface（ClassInterfaceType.AutoDual），ComVisible（true）]
public class Widget
{
public void SayMyName（）
{
MessageBox.Show（Widget 123）;
}
}
}
编译程序集后，您应该可以通过转到工具>参考来包含对VBA的引用：
然后你应该可以访问你的主类和任何其他类，像这样：
Sub Test（）
Dim main As MyCCWTest.main
设置main = New MyCCWTest.main
Dim myWidget作为MyCCWTest.Widget
设置myWidget = main.GetWidget
myWidget.SayMyName
End Sub
你的问题关于List<>：COM不知道关于泛型的任何东西，所以他们不被支持。事实上，在CCW中使用数组甚至是一个棘手的问题。在我的经验中，我发现最简单的事情是创建自己的集合类。使用上面的例子，我可以创建一个WidgetCollection类。下面是包含WidgetCollection类的稍微修改的项目：
使用System;
using System.Collections;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Linq;
using System.Text;
using System.Windows.Forms;
命名空间MyCCWTest
{
[Serializable（），ClassInterface（ClassInterfaceType.AutoDual），ComVisible（true）]
public class Main
{
private WidgetCollection myWidgets = new WidgetCollection（）;
public Main（）
{
myWidgets.Add（new Widget（Bob））;
myWidgets.Add（new Widget（John））;
myWidgets.Add（new Widget（Mary））;
}
public WidgetCollection MyWidgets
{
get
{
return myWidgets;
}
}
}
[Serializable（），ClassInterface（ClassInterfaceType.AutoDual），ComVisible（true）]
public class Widget
{
private string myName;
public Widget（string myName）
{
this.myName = myName;
}
public void SayMyName（）
{
MessageBox.Show（myName）;
}
}
[Serializable（），ClassInterface（ClassInterfaceType.AutoDual），ComVisible（true）]
public class WidgetCollection：IEnumerable
{
private List< Widget> widgets = new List< Widget>（）;
public IEnumerator GetEnumerator（）
{
return widgets.GetEnumerator（）;
}
public Widget this [int index]
{
get
{
return widgets [index]
}
}
public int Count
{
get
{
return widgets.Count;
}
}
public void Add（Widget item）
{
widgets.Add（item）;
}
public void Remove（Widget item）
{
widgets.Remove（item）;
}
}
}
这在VBA：
Sub Test（）
Dim main As MyCCWTest.main
Set main = New MyCCWTest.main
Dim singleWidget作为MyCCWTest.Widget
对于每个singleWidget在main.myWidgets
singleWidget.SayMyName
下一个
结束子
注意：我在新版本中包含了 System.Collections; 项目，所以我的WidgetCollection类可以实现IEnumerable。
I have developed a web service in ASP.net, c#, and hosted on IIS, which is to be consumed by a vba client. Having downloaded the Office 2003 Web Services 2.01 Toolkit, I encountered a problem in successfully creating the proxy classes required (as documented by many users online), and decided to create a .net dll library instead. I have created the library, which references the web service and exposes one of its methods to a public function in c#.
I now have three questions:
How do I reference the dll class in VBA? I tried to go to Tools->References and browsed to the dll location, but I get the error "can't add reference to the file specified". Is there a specific location on the disk I have to have the .dll copied?
Can I also copy the dll.config file next to the dll file, so as to have the endpoint url there?
Since the method to call is accepting an object (consisting of various members and a couple of List<> members, how are these to be implemented in VBA code?
You will need to make a COM-callable wrapper (CCW) for your assembly (DLL). .NET interoperability is a fairly in-depth topic, but it's relatively easy to get something off the ground.
First of all, you need to make sure your entire assembly is registered for COM interop. You can do this on the "Build" tab in Visual Studio by checking "Register for COM Interop". Secondly, you should include the System.Runtime.InteropServices in all your classes:
using System.Runtime.InteropServices;
Next, you should decorate all the classes you want to be exposed with the [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)] attributes. This will make it so you can access the class members properly and using intellisense from within the VBA editor.
You need to have an entry point -- i.e. a main class, and that class should have a public constructor with no arguments. From that class, you can call methods which return instances of your other classes. Here is a simple example:
using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace MyCCWTest
{
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Main
{
public Widget GetWidget()
{
return new Widget();
}
}
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Widget
{
public void SayMyName()
{
MessageBox.Show("Widget 123");
}
}
}
Once you compile your assembly, you should be able to include a reference to it within VBA by going to "Tools > References":
Then you should be able to access your main class and any other classes like this:
Sub Test()
Dim main As MyCCWTest.main
Set main = New MyCCWTest.main
Dim myWidget As MyCCWTest.Widget
Set myWidget = main.GetWidget
myWidget.SayMyName
End Sub
To answer your question about List<>: COM doesn't know anything about generics, so they're not supported. In fact, using arrays in CCW's is even a tricky subject. In my experience, I've found the easiest thing to do is to create my own collection classes. Using the example above, I could create a WidgetCollection class. Here is a slightly-modified project with the WidgetCollection class included:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace MyCCWTest
{
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Main
{
private WidgetCollection myWidgets = new WidgetCollection();
public Main()
{
myWidgets.Add(new Widget("Bob"));
myWidgets.Add(new Widget("John"));
myWidgets.Add(new Widget("Mary"));
}
public WidgetCollection MyWidgets
{
get
{
return myWidgets;
}
}
}
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Widget
{
private string myName;
public Widget(string myName)
{
this.myName = myName;
}
public void SayMyName()
{
MessageBox.Show(myName);
}
}
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class WidgetCollection : IEnumerable
{
private List<Widget> widgets = new List<Widget>();
public IEnumerator GetEnumerator()
{
return widgets.GetEnumerator();
}
public Widget this[int index]
{
get
{
return widgets[index];
}
}
public int Count
{
get
{
return widgets.Count;
}
}
public void Add(Widget item)
{
widgets.Add(item);
}
public void Remove(Widget item)
{
widgets.Remove(item);
}
}
}
And you can use it like this in VBA:
Sub Test()
Dim main As MyCCWTest.main
Set main = New MyCCWTest.main
Dim singleWidget As MyCCWTest.Widget
For Each singleWidget In main.myWidgets
singleWidget.SayMyName
Next
End Sub
NOTE: I have included System.Collections; in the new project so my WidgetCollection class can implement IEnumerable.
这篇关于从vba调用.net库方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
