通过C＃运行Excel宏：从一个工作簿上的另一个运行宏？ [英] Running an Excel Macro via C#: Run a macro from one workbook on another?
问题描述
我期待运行宏，姑且称之为Macro01从WorkSheet01上WorkSheet02。
使用的的Microsoft.Office.Interop.Excel命名空间我已经开了一个WorkSheet01。
公共无效Main_CodedStep（）
{
//对象失踪（或可选）参数。
对象oMissing = System.Reflection.Missing.Value;
//创建Microsoft Excel中
Excel.ApplicationClass oExcel =新Excel.ApplicationClass的一个实例（）;
//使其可见
oExcel.Visible = TRUE;
//打开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浏览器的下载提示打开，而不是互操作。
问题是当我尝试运行通过C＃宏（我又有了新的Excel.ApplicationClass（） ;只有这样它编译，我相信这是我的失误之一）
公共无效FirstMacro_CodedStep（）
{
//创建Microsoft Excel中
Excel.ApplicationClass oExcel =新Excel.ApplicationClass的一个实例（）;
Console.WriteLine（ApplicationClass：+ oExcel）;
//运行宏，First_Macro
RunMacro（oExcel，新的对象[] {Worksheet01.xlsm First_Macro！}）;
//垃圾收集
GC.Collect的（）;
}
私人无效RunMacro（对象oApp，对象[] oRunArgs）
{
oApp.GetType（）。InvokeMember（运行中的System.Reflection。 BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod，空，oApp，oRunArgs）;
}
在此方法运行它运行从Worksheet01上Worksheet01而不是Worksheet02宏。 。此外，它一直在寻找我的文档工作表让我感动过来看看会发生什么。
回顾：
- 开启Worksheet01
- 通过脚本获取并打开MSIE报告（Worksheet02）
- 运行Macro01从Worksheet01上Worksheet02
资源：
http://support.microsoft.com/kb/306683
http://msdn.microsoft.com/ EN-US /库/ microsoft.office.interop.excel.aspx
对于那些想尝试它添加到您的使用指令谁：
使用的System.Reflection;
使用Microsoft.Office.Core; //添加到从C项目设置参考文献：\Program文件（x86）\Microsoft的Visual Studio 10.0\Visual Studio工具Office\PIA\Office14 - 使用Excel =微软的办公室
。 Office.Interop.Excel; //添加到项目设置引用从C：\Program文件（x86）\Microsoft的Visual Studio 10.0\Visual Studio工具Office\PIA\Office14 - 的Microsoft.Office.Interop.Excel
我发现，我想与大家分享的解决方案。
首先，我删除，我打开Worksheet01位。然后，我有我的自动化的脚本将.csv保存到我的文档。然后，我使用的代码，我不得不打开Worksheet01打开下载的文件。此时的关键是Worksheet01是与Worksheet02文档文件夹。最后我用代码来运行Worksheet01，它运行在Worksheet02宏。
公共无效WebTest_CodedStep（）
{
//对象缺失（或可选）参数。
对象oMissing = System.Reflection.Missing.Value;
//创建Microsoft Excel中
Excel.ApplicationClass oExcel =新Excel.ApplicationClass的一个实例（）;
//使其可见
oExcel.Visible = TRUE;
//定义工作簿
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = NULL;
//获取文件路径
路径字符串= System.Environment.GetFolderPath（Environment.SpecialFolder.MyDocuments）;
路径=路径+\\Worksheet02.csv
//打开文件时，使用'路径'变量
oBook = oBooks.Open（路径，oMissing，oMissing，oMissing，oMissing，oMissing，oMissing，oMissing，oMissing，oMissing， oMissing，oMissing，oMissing，oMissing，oMissing）;
//运行宏，First_Macro
RunMacro（oExcel，新的对象[] {Worksheet01.xlsm First_Macro！}）;
//退出Excel和清理。
oBook.Close（假，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;
//垃圾收集
GC.Collect的（）;
}
私人无效RunMacro（对象oApp，对象[] oRunArgs）
{
oApp.GetType（）。InvokeMember（运行中的System.Reflection。 BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod，空，oApp，oRunArgs）;
}
I am looking to run a macro, let's call it Macro01 from WorkSheet01 on WorkSheet02.
Using Microsoft.Office.Interop.Excel Namespace I have opened a 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);
}
I then use an automated script to pull a report. This report is opened via IE's download prompt and not the Interop.
The problem comes when I try to run the macro via C# (I made another new Excel.ApplicationClass(); only so it compiled, I believe this is one of my missteps.)
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);
}
When this method runs it runs the macro from Worksheet01 on Worksheet01 instead of Worksheet02. Also it was looking for the worksheet in My Documents so I moved it over to see what would happen.
Recap:
- Open Worksheet01
- Via scripting get and open a report (Worksheet02) from MSIE
- Run Macro01 from Worksheet01 on Worksheet02
Resources:
http://support.microsoft.com/kb/306683
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx
For those who would like to try it add this to your using directives:
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"
I found a solution that I'd like to share. First, I removed the bit where I opened Worksheet01. I then had my automated script save the .CSV to My Documents. I then used the code I had to open Worksheet01 to open the downloaded file. The key thing at this point is that Worksheet01 is in the Documents folder with Worksheet02. Lastly I used the code to run the macro from Worksheet01, which runs on 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＃运行Excel宏：从一个工作簿上的另一个运行宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
