从C＃调用某些Excel VBA宏时出错 [英] Errors when calling certain Excel VBA macros from C#
问题描述
我一直在努力和寻找这个问题无法使用...
我有几个测试宏，从Excel运行时工作完美，但是失败或不工作从C＃调用时，使用互操作...
我使用MS Visual Studio 2012（在64位Windows 7上）创建一个控制台应用程序，将调用VBA宏TestMacro Excel 2007.
这里是调用宏的C＃代码：（我基本上复制了这里做的使用Visual C＃.NET中的自动化运行Office宏）
使用系统;
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;
使用Microsoft.Office.Core;
命名空间C＃_Macro_Wrapper
{
class Program
{
static void Main（string [] args）
{
RunVBATest（）;
}
public static void RunVBATest（）
{
System.Globalization.CultureInfo oldCI = System.Threading.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，拒绝，拒绝，
拒绝，拒绝，拒绝，拒绝，拒绝，拒绝，拒绝，拒绝，拒绝，拒绝，拒绝
//运行宏。
RunMacro（oExcel，new Object [] {TestMacro（）}）;
//退出Excel并清理。
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 ，
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod，
null，oApp，oRunArgs）;
}
}
}
Sub TestMacro（）
工作表（Sheet1）。ClearContents
End Sub
此宏无法通过Visual Studio的ComException - >HRESULT：0x800A03EC异常
Sub TestMacro（）
范围（TestRange）ClearContents
End Sub
不返回异常，但不清除TestRange的内容
Sub TestMacro（）
x = Range（TestRange）。Count（）
Cells（5，5）.Value = x
End Sub
但是这样工作，因此范围名称可以被识别，并且计数操作可以完成...
Sub TestMacro（）
工作表（Sheet1）选择
x = Range（TestRange）Count $ b Worksheets（Sheet2）。选择
Cells（5，5）.Value = x
End Sub
这和上一种情况一样，但是不能切换到工作表2，而是将结果写入工作表1 ...
早期所有这些宏工作完美，当运行从excel，但失败时调用从C＃。没有工作表受保护，我已在宏安全中设置启用所有宏，信任访问VBA项目模型。我觉得有一些访问/权限问题，但我不知道如何解决它....任何帮助将赞赏..
感谢提前!!
很明显，有一个很容易的解决方案。而不是使用RunMacro函数...使用应用程序方法运行适用于所有宏。我使用的行
oExcel.Run（TestMacro）;
而不是致电
private static void RunMacro（object oApp，object [] oRunArgs）
{
oApp.GetType（）。InvokeMember（Run，
System.Reflection.BindingFlags。默认|
System.Reflection.BindingFlags.InvokeMethod，
null，oApp，oRunArgs）;
}
与
RunMacro（oExcel，new Object [] {TestMsg}）;
Arghh ...这个问题偷了我的生命的两天！
I have been struggling and searching on this problem to no avail... I have a few test macros that work perfectly when run from Excel, but either fail or don't work when called from C#, using the interop...
I am using MS Visual Studio 2012 (on 64 bit Windows 7) to create a console application that will call the VBA macro TestMacro() written in Excel 2007.
Here is the C# code to call the macro: (I basically copied what was done here 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);
}
}
}
The Macros are...
Sub TestMacro()
Worksheets("Sheet1").ClearContents
End Sub
This macro fails with ComException to Visual Studio -> "Exception from HRESULT: 0x800A03EC"
Sub TestMacro()
Range("TestRange").ClearContents
End Sub
Does not return exception, but does not clear contents of TestRange
Sub TestMacro()
x = Range("TestRange").Count()
Cells(5, 5).Value = x
End Sub
But this works, thus the range name can be recognized, and the count operation can be done...
Sub TestMacro()
Worksheets("Sheet1").Select
x = Range("TestRange").Count()
Worksheets("Sheet2").Select
Cells(5, 5).Value = x
End Sub
This works like last case, but fails to switch to sheet 2, and instead writes result to sheet 1...
As said earlier all these macros work perfectly when run from excel, but fail when called from C#. None of the works sheets are protected, and I have set "enable all macros", "trust access to VBA project model" in Macro Security. I feel there is some access/permissions issue, but I have no idea how to fix it....any help would appreciated..
Thanks in advance!!
Well apparently there was a very easy solution. Instead of using RunMacro function...using the application method "Run" works for all macros. I used the line
oExcel.Run("TestMacro");
instead of calling
private static void RunMacro(object oApp, object[] oRunArgs)
{
oApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod,
null, oApp, oRunArgs);
}
with
RunMacro(oExcel, new Object[] { "TestMsg" });
Arghh..this issue stole 2 days of my life!
这篇关于从C＃调用某些Excel VBA宏时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
