通过C#运行Excel宏:在另一个工作簿上运行宏?
我正在运行一个宏,让我们从WorkSheet02上的WorkSheet01中将它称为Macro01。通过C#运行Excel宏:在另一个工作簿上运行宏?
使用Microsoft.Office.Interop.Excel命名空间我打开了一个WorkSheet01。
public void Main_CodedStep()
{
// Object for missing (or optional) arguments.
object oMissing = System.Reflection.Missing.Value;
// Create an instance of Microsoft Excel
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
// Make it visible
oExcel.Visible = true;
// Open Worksheet01.xlsm
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
oBook = oBooks.Open("C:\\Users\\Admin\\Documents\\Worksheet01.xlsm", oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
}
然后,我使用自动脚本来拉取报告。此报告通过IE的下载提示而不是Interop打开。
问题是当我尝试运行通过C#宏(我又有了新的Excel.ApplicationClass();只有这样它编译,我相信这是我的失误之一。)
public void FirstMacro_CodedStep()
{
// Create an instance of Microsoft Excel
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Console.WriteLine("ApplicationClass: " + oExcel);
// Run the macro, "First_Macro"
RunMacro(oExcel, new Object[]{"Worksheet01.xlsm!First_Macro"});
//Garbage collection
GC.Collect();
}
private void RunMacro(object oApp, object[] oRunArgs)
{
oApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, oApp, oRunArgs);
}
当此方法运行它从Worksheet01而不是Worksheet02 Works5运行宏。此外,它正在寻找我的文档中的工作表,所以我移动它看看会发生什么。
回顾:
- 打开Worksheet01
- 通过脚本获取和MSIE打开报告(Worksheet02)
- 运行Macro01从Worksheet01上Worksheet02
资源:
http://support.microsoft.com/kb/306683
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx
对于那些想尝试它添加到您的使用指示谁:
using System.Reflection;
using Microsoft.Office.Core; //Added to Project Settings' References from C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14 - "office"
using Excel = Microsoft.Office.Interop.Excel; //Added to Project Settings' References from C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14 - "Microsoft.Office.Interop.Excel"
我发现,我想与大家分享的解决方案。 首先,我删除了打开Worksheet01的位。然后,我的自动脚本将.CSV保存到“我的文档”中。然后,我使用我必须打开Worksheet01打开下载的文件的代码。此时关键在于Worksheet01位于Worksheet02的“文档”文件夹中。最后,我使用代码从Worksheet02上运行Worksheet02运行宏。
public void WebTest_CodedStep()
{
// Object for missing (or optional) arguments.
object oMissing = System.Reflection.Missing.Value;
// Create an instance of Microsoft Excel
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
// Make it visible
oExcel.Visible = true;
// Define Workbooks
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
// Get the file path
string path = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
path = path + "\\Worksheet02.csv";
//Open the file, using the 'path' variable
oBook = oBooks.Open(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
// Run the macro, "First_Macro"
RunMacro(oExcel, new Object[]{"Worksheet01.xlsm!First_Macro"});
// Quit Excel and clean up.
oBook.Close(false, 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;
//Garbage collection
GC.Collect();
}
private void RunMacro(object oApp, object[] oRunArgs)
{
oApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, oApp, oRunArgs);
}
我跑这个C#VSTO代码来调用一个VBA宏,这是语法我使用:
this.Application.Run("mymacro");
编辑:
个宏是工作簿宽,也许你需要在运行宏之前,使Sheet2中活动工作表,例如:
foreach (Worksheet worksheet in workbook.Sheets.ComLinq<Worksheet>())
{
if (worksheet.Name == "Sheet2") worksheet.Activate();
}
对不起,我不知道我的命名约定有点误导。我所谓的Worksheet01和Worksheet02是两个不同的文件(一个是xlsm,另一个是csv)。 – kirbycope
请检查这一项 http://*.com/questions/27166341/run-microsoft-office-macro-using-c-sharp –