c# - 从 C# 调用某些 Excel VBA 宏时出错

Question

我一直在努力寻找这个问题，但无济于事......我有一些测试宏，在从 Excel 运行时可以完美运行，但在使用互操作从 C# 调用时要么失败，要么不起作用...

我正在使用 MS Visual Studio 2012(在 64 位 Windows 7 上)创建一个控制台应用程序，该应用程序将调用用 Excel 2007 编写的 VBA 宏 TestMacro()。

这是调用宏的 C# 代码:(我基本上复制了此处所做的操作 Run Office Macros by Using Automation from Visual C# .NET )

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;

namespace C#_Macro_Wrapper
{
    class Program
    {
        static void Main(string[] args)
        {
            RunVBATest();
        }


        public static void RunVBATest()
        {
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            object oMissing = System.Reflection.Missing.Value;
            Excel.Application oExcel = new Excel.Application();
            oExcel.Visible = true;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel._Workbook oBook = null;
            oBook = oBooks.Open("C:\\Users\\ssampath\\Documents\\RMS Projects\\Beta 3 Testing\\TestMacroForVSTO.xlsm", oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

            // Run the macro.
            RunMacro(oExcel, new Object[] { "TestMacro()" });


            // Quit Excel and clean up.
            oBook.Close(true, oMissing, oMissing);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
            oBook = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
            oBooks = null;
            oExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
            oExcel = null;
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
        }


        private static void RunMacro(object oApp, object[] oRunArgs)
        {
            oApp.GetType().InvokeMember("Run",
                System.Reflection.BindingFlags.Default |
                System.Reflection.BindingFlags.InvokeMethod,
                null, oApp, oRunArgs);
        }
    }
}

宏是...

Sub TestMacro()  
Worksheets("Sheet1").ClearContents
End Sub

此宏失败，Visual Studio 出现 ComException ->“来自 HRESULT 的异常:0x800A03EC”

Sub TestMacro()
Range("TestRange").ClearContents
End Sub

不返回异常，但不清除TestRange的内容

Sub TestMacro()
x = Range("TestRange").Count()
Cells(5, 5).Value = x
End Sub

但是这样可以，因此可以识别范围名称，并且可以进行计数操作...

Sub TestMacro()
Worksheets("Sheet1").Select
x = Range("TestRange").Count()
Worksheets("Sheet2").Select
Cells(5, 5).Value = x
End Sub

这与上一种情况类似，但无法切换到工作表 2，而是将结果写入工作表 1...

如前所述，所有这些宏在从 Excel 运行时都可以完美运行，但在从 C# 调用时会失败。所有工作表都没有受到保护，并且我在宏安全性中设置了“启用所有宏”、“信任对VBA项目模型的访问”。我觉得存在一些访问/权限问题，但我不知道如何解决它....任何帮助将不胜感激..

提前致谢!!

Answer 1

您可能缺少一些东西。首先，这是我用来让它工作的代码，使用 .Net 引用 Microsoft.Office.Interop.Excel v14(适用于 Office 2010):

using System;
using Microsoft.Office.Interop.Excel;

namespace ConsoleApplication5
{
class Program
{
static void Main(string[] args)
{
    RunVBATest();
}

public static void RunVBATest()
{
    //System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
    //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
    //object oMissing = System.Reflection.Missing.Value;
    Application oExcel = new Application();
    oExcel.Visible = true;
    Workbooks oBooks = oExcel.Workbooks;
    _Workbook oBook = null;
    oBook = oBooks.Open("C:\\temp\\Book1.xlsm");

    // Run the macro.
    RunMacro(oExcel, new Object[] { "TestMsg" });

    // Quit Excel and clean up.
    oBook.Saved = true;
    oBook.Close(false);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
    oBook = null;
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
    oBooks = null;
    oExcel.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
    oExcel = null;
    //System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}

private static void RunMacro(object oApp, object[] oRunArgs)
{
    oApp.GetType().InvokeMember("Run",
        System.Reflection.BindingFlags.Default |
        System.Reflection.BindingFlags.InvokeMethod,
        null, oApp, oRunArgs);
}
}
}

其次，确保将宏代码放入模块(全局 BAS 文件)中。

Public Sub TestMsg()

MsgBox ("Hello Stackoverflow")

End Sub

第三，确保启用对 VBA 项目对象模型的宏安全性和信任访问:

Also does anyone know if for this case there is a way to debug Excel VBA from Visual Studio 2012, the Com Exceptions are pretty unhelpful... – Sunny Sampath

当您克服 Com 异常时，单步执行代码将从 VS 跨进程进入 Excel 的 VBE，然后返回 VS。

编辑:

以下是清除内容的 VBA 代码:

Dim activeSheet As Worksheet
Set activeSheet = Excel.activeSheet
activeSheet.UsedRange.Clear

或者，如果您想删除名为 TestRange 的 NameRange:

Dim range As range
Set range = activeSheet.range("TestRange")
range.Clear

其他 TestMacro 对您的工作来说太具体了，我无法理解。祝你好运!