Run Excel Macros from C#
C# Console Application to run an Excel Macro
This tutorial shows how to call an Excel VBA Macro from a C# Console application. The tasks to be performed are to:
- Open the Excel File and Run a Macro that populates the workbook
- Copy the Excel File to a "Copy" Excel file
- Open the "Copy" Excel file and Run a Macro that destroys all macro code within the file including itself
- Throws a custom exception if either file is "locked" during processing.
Create a Console Application in Visual Studio
Open Visual Studio, select C# Console Application and the following code will show up in the file named Program.cs
Add C# References to Console Application
Add references to Excel, Office and a Class that will perform the tasks: RunMacro, CopyThis and IsFileLocked.
The Excel references are located on the COM tab as Microsoft Excel 12.0 Object Library and Microsoft Office 12.0 Object Library. The DLL was compiled separately and copied to a folder in the project and can be added as a reference by going to Browse.
Update Main in the C# Console Application
Add code to Main in Program.cs to create the objects and point to the appropriate references.
Add these using statements to the top.
using Excel = Microsoft.Office.Interop.Excel;
using ExcelMacro;
using System.Text.RegularExpressions;
Add this code to Main. This creates the Excel objects and points to the location of the Excel files which have the macros GenRept (to attach to a SQL database and import data to a worksheet) and SelfDestructCall (a macro using VBE to remove all macro code and itself after the file is copied). The process will not be visible as the setting is set to false at the beginning of the code.
static void Main(string[] args)
{
object oMissing = System.Reflection.Missing.Value;
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
oExcel.Visible = false;
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
ExcelMac exc = new ExcelMac();
string fileEx = @"C:\Data\Book1.xls";
string fileCopy = @"C:\Data\Book1Copy.xls";
bool isLocked;
try
{
Console.WriteLine("XLS - " + fileEx);
Console.WriteLine("Copy - " + fileCopy);
isLocked = exc.IsFileLocked(fileEx);
if (isLocked == true)
{
throw new MyException("Report is locked.");
}
oBook = oBooks.Open(fileEx, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
Console.WriteLine("Opening file for Macro");
// Run the macro.
exc.RunMacro(oExcel, new Object[] { "ThisWorkbook.GenRept" });
oBook.Close(true, oMissing, oMissing);
isLocked = exc.IsFileLocked(fileCopy);
if (isLocked == true)
{
throw new MyException("Rept Copy is locked.");
}
Console.WriteLine("Copying File...");
exc.CopyThis(fileEx, fileCopy);
//copy the macro wkbk to the copy
Console.WriteLine("Open Copy File");
oBook = oBooks.Open(fileCopy, oMissing, oMissing,oMissing, oMissing, oMissing,oMissing,
oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
Console.WriteLine("Run Self Destruct Macro...");
//execute a macro removal from the copy file
exc.RunMacro(oExcel, new Object[] { "ThisWorkbook.SelfDestructCall" });
oBook.Close(true, oMissing, oMissing);
Console.WriteLine("Complete ");
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;
}
catch (MyException myEx)
{
string localerr = Regex.Replace(myEx.myMessage.ToString(), @"[^\w-]+", " ");
Console.WriteLine(myEx.myMessage.ToString());
oExcel.Quit();
}
catch (Exception ex)
{
string exerr = Regex.Replace(ex.Message.ToString(), @"[^\w-]+", " ");
Console.WriteLine(ex.Message.ToString());
oExcel.Quit();
}
finally
{
GC.Collect();
}
}
There is a custom class included in the code which is thrown when either file is "Locked"
public class MyException : Exception
{
private string _myMsg;
public string myMessage
{
get {
return _myMsg;
}
set {
_myMsg = value;
}
}
public MyException(string _str)
{
myMessage = _str;
}
public override string Message
{
get {
return base.Message;
}
}
}
The ExcelMac class is instantiated at the beginning of the code (from ExcelMacro.dll) and Runs the Macro code and performs the Copy and IsFileLocked detection.
public void RunMacro(object oApp, object[] oRunArgs)
{
lock (this)
{
oApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod,
null, oApp, oRunArgs);
}
}
public void CopyThis(string sIn, string sOut)
{
lock (this)
{
FileInfo fi = new FileInfo(sIn);
fi.CopyTo(sOut, true);
}
}
public bool IsFileLocked(string fileName)
{
FileInfo file = new FileInfo(fileName);
FileStream stream = null;
try
{
stream = file.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
}
catch (IOException)
{
//the file is unavailable
return true;
}
finally
{
if(stream != null)
stream.Close();
}
//file is not locked
return false;
}
When the code is run without issues, the console window will appear with the following messages to the user.
When Book1.xls is open and the console application tries to run, the custom exception is thrown.
Automating the Excel report with macros and using a Console application to execute them saves a great deal of time. The executable file that is created can easily be run as a Task in the Task Scheduler daily, weekly, or any frequency that is required.