Automation of Excel Macros

There is still a huge value of reporting and using Excel for end users.  Excel is simple to work with, allows data access and allows end users to program with minimal learning.  As users develop their skills they inevitably look at automation through macros.  So if we want to turn this into something a little more ‘supported’ (by the corporate IT department) so that its scheduled on some manner, we’ll have to turn to a bit of programming.  So, lets look at how we can do this.  Firstly we’ll look at some rudimentary code and then we’ll look at scheduling.

Code

So here is a snippet to do that (including the reference).  You’ll notice that there are a couple of key features.  There are 2 parameters passed (the file name and the macro), then (in code) we simply open excel, then the workbook, execute the macro and save and close the workbook (and application)

using xls = Microsoft.Office.Interop.Excel;

static void Main(string[] args)
 {

    xls.Application _x = null;
     xls.Workbook _wb = null;

    string _filename = args[0];
     string _macro = args[1];

    _x = new xls.Application();
     _x.Visible = false;
     _wb = _x.Workbooks.Open(_filename);
     _x.Run(_macro);
     _wb.Save();
     _wb.Close();
     _wb = null;
     _x.Quit();
     _x = null;

}

So, if I want to run my code from the command console, you’d just run the following (note the macro is called main).

ExcelManager.exe “this-is-the-excel-workbook-name” “main”

or as a SQL Command, wrap it in a batch and then execute;

EXEC master..xp_CMDShell ‘c:\temp\excel_manager.bat’

A Gotcha

In debug mode, this all worked and the task could be scheduled and executed in windows scheduler.  However, when the the tasked was scheduled (SQL Server agent) or scheduled to run when a user was not logged in there would be be errors.  This related to file not found errors and other (generally other unhelpful and unreliable messages).  It turns out, that you’ll need to ensure that there are some folders required (don’t ask me why).  You may want to check if the following folders exist.  If not, try adding them;

C:\Windows\System32\config\systemprofile\Desktop
C:\Windows\SysWOW64\config\systemprofile\Desktop

An example of one of the type of errors you may get is;