Naveen's Weblog

Bridge to future

Create, Execute and delete Macro in excel using C#

Posted by codingsense on May 11, 2009


When we need any specific task to be done in excel through our program, we first do the same task manually in excel and record the macro to have a look how the function should be written in our code.

There are some functions those are hard to find in the Interop.Excel dll, for such things microsoft has given helpful functions which will help us to add the macro in the sheet and execute it. This will help us to reuse the same macro that we generate in excel with record macro.

And later on if the macro is still present in generated excel then while opening it prompts enable/disable macro, so to avoid it, we will see how the macro can be deleted through C#.

Here let me explain you with a sample.

Read Only Or Locked Cell In Excel

Lets imagine a case , we have to generate an excel of employees with their name and salary. We want no employee should alter the salary that is enterd in the sheet i.e we want it to locked or to make it read only.

For the above case we will try to acheive executing a macro.

First we will try to see how a cell can be locked in excel and what macro is generated by excel when we record a new macro.

1) Select a cell
2) Enter some data ( that is to be locked for modification)
3) select Data Menu -> Validation
4) In Allow select custom
5) In formula enter “” and click ok
6) Now check altering the data in that cell, it gives a message and does not let the user change the value.

Wow this is what we need. Fine, since we have recorded the macro we will open the visual basic editor and see what macro has been generated, ok here is what i got.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/05/2009 by naveen
'
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=""""""
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

now this is for the selection what we have done in the excel, to make more better when executing from external application we will pass a range parameter to the function, so that we can lock the input range of cells, let us also change the macro name to LockRange and change the message that is displayed to the user.

The new macro would look like.

Sub LockRange(oRange As String)
'
' Macro1 Macro
' Macro recorded 11/05/2009 by naveen
'
    Range(oRange).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=""""""
        .ErrorTitle = "Locked Cell"
        .ErrorMessage = "This cell is locked for modification."
        .ShowError = True
    End With
End Sub

Now since we know what macro to use to make the requirement possible, we just need to implement the same in our C# code, Here comes the code.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Vbe.Interop;

namespace LockRangeInExcel
{
class Program    
{
static void Main(string[] args)
{
Excel.Application oXL = null;
Excel._Workbook oWB = null;
Excel._Worksheet oSheet = null;
Excel.Range oRng = null;
_VBComponent oModule = null; // VBA Module            
try            
{
//Start Microsoft.Office.Interop.Excel and get Application object.
                oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;

//Get a new workbook.
                oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

//Add table headers going cell by cell.
                oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Salary(Read Only)";

//Format A1:D1 as bold, vertical alignment = center.
                oSheet.get_Range("A1", "B1").Font.Bold = true;
oSheet.get_Range("A1", "B1").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

// Create an array to multiple values at once.                
string[,] saNames = new string[5, 2];

saNames[0, 0] = "Naveen";
saNames[0, 1] = "10000";
saNames[1, 0] = "Girish";
saNames[1, 1] = "20000";
saNames[2, 0] = "Nandish";
saNames[2, 1] = "15000";
saNames[3, 0] = "Rashmi";
saNames[3, 1] = "40000";

//Fill A2:B6 with an array of values (First and Salary).
                oSheet.get_Range("A2", "B6").Value2 = saNames;

//AutoFit columns A:D.
                oRng = oSheet.get_Range("A1", "B1");
oRng.EntireColumn.AutoFit();

//Create macro in excel
                CreateMacro(oWB, oModule);

// Run VBA macro "MakeCellReadOnly" on a range                
string oRange = "B2:B6";
LockRange(oWB, oRange);

//For single cell you can use the function as follows                
//oRange = "C7";                
//MakeCellReadOnly(oWB, oRange);                

//Delete the created Macro
                DeleteMacro(oWB, oModule);

//Make sure Microsoft.Office.Interop.Excel is visible and give the user control                
//of Microsoft Microsoft.Office.Interop.Excel's lifetime.
                oXL.Visible = true;
oXL.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);

MessageBox.Show(errorMessage, "Error");
}

ReleaseComObj(oSheet);
ReleaseComObj(oWB);
ReleaseComObj(oXL);
}

private static void ReleaseComObj(object o)
{
try            
{
Marshal.ReleaseComObject(o);
}
catch            
{ }
finally            
{
o = null;
}
}

private static void DeleteMacro(Microsoft.Office.Interop.Excel._Workbook oWB, _VBComponent oModule )
{
Object oMissing = System.Reflection.Missing.Value;
oWB.Application.Run("DeleteThisModule"
, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
}

public static void LockRange(Excel._Workbook oWB, string oRange)
{
Object oMissing = System.Reflection.Missing.Value;
//The following command executes the macro by passing the range to the macro
            oWB.Application.Run("LockRange"
, oRange, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
}

public static void CreateMacro(Excel._Workbook oWB,_VBComponent oModule)
{
//Create a macro
            oModule = oWB.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);

//Create macro for locking the cell and delete the module            
string sCode =
"Sub LockRange(oRange As String)\r\n" +
"Range(oRange).Select\r\n" +
"With Selection.Validation \r\n" +
".Delete\r\n" +
".Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _\r\n" +
"xlBetween, Formula1:=\"\"\"\"\"\"\r\n" +
".ErrorTitle = \"Locked Cell\"\r\n" +
".ErrorMessage = \"This cell is locked for modification.\"\r\n" +
".ShowError = True\r\n" +
"End With\r\n" +
"End Sub\r\n" +
"Sub DeleteThisModule()\r\n" +
"Dim vbCom As Object\r\n" +
"Set vbCom = Application.VBE.ActiveVBProject.VBComponents\r\n" +
"vbCom.Remove VBComponent:= vbCom.Item(\"Module1\")\r\n" +
"End Sub\r\n";

//Add the macro in excel workbook that we have created
            oModule.CodeModule.AddFromString(sCode);

}
}
}

In VB.Net the same implementation can be done as

Module Module1
Sub Main()
Dim oXL As New Microsoft.Office.Interop.Excel.Application
Dim oWB As Microsoft.Office.Interop.Excel.Workbook
Dim OSheet As New Microsoft.Office.Interop.Excel.Worksheet
Dim oModule As Microsoft.Vbe.Interop.VBComponent

Try            

'Start Microsoft.Office.Interop.Excel and get Application object.
            oXL.Visible = True            
'Get a new workbook.
            oWB = oXL.Workbooks.Add()
OSheet = oWB.ActiveSheet

'Add table headers going cell by cell.
            OSheet.Cells(1, 1) = "FirstName"
            OSheet.Cells(1, 2) = "Salary(Read Only)"            

'Format A1:D1 as bold, vertical alignment = center.
            OSheet.Range("A1", "B1").Font.Bold = True
            OSheet.Range("A1", "B1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter

'Create an array to multiple values at once.            
Dim saNames(5, 2) As String
            saNames(0, 0) = "Naveen"
            saNames(0, 1) = "10000"
            saNames(1, 0) = "Girish"
            saNames(1, 1) = "20000"
            saNames(2, 0) = "Nandish"
            saNames(2, 1) = "15000"
            saNames(3, 0) = "Rashmi"
            saNames(3, 1) = "40000"            

'Fill A2:B6 with an array of values (First and Salary).
            OSheet.Range("A2", "B6").Value2 = saNames

'AutoFit columns A:D.
            OSheet.Range("A1", "B1").EntireColumn.AutoFit()

'Create macro in excel
            CreateMacro(oWB, oModule)

' Run VBA macro "MakeCellReadOnly" on a range            
Dim oRange As String = "B2:B6"
            LockRange(oWB, oRange)

'For single cell you can use the function as follows            
'oRange = "C7";            
'MakeCellReadOnly(oWB, oRange);            

'Delete the created Macro
            DeleteMacro(oWB)

'Make sure Microsoft.Office.Interop.Excel is visible and give the user control            
'of Microsoft Microsoft.Office.Interop.Excel's lifetime.
            oXL.Visible = True
            oXL.UserControl = True            

Catch        


End Try        

End Sub    

Private Sub DeleteMacro(ByVal oWB As Microsoft.Office.Interop.Excel.Workbook)
oWB.Application.Run("DeleteThisModule")
End Sub    

Private Sub LockRange(ByVal oWb As Microsoft.Office.Interop.Excel.Workbook, ByVal oRange As String)
'The following command executes the macro by passing the range to the macro
        oWb.Application.Run("LockRange", oRange)
End Sub    

Public Sub CreateMacro(ByVal oWB As Microsoft.Office.Interop.Excel.Workbook, ByVal oModule 
As Microsoft.Vbe.Interop.VBComponent)
'Create a macro
        oModule = oWB.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule)
'Create macro for locking the cell and delete the module        
Dim sCode As String = "Sub LockRange(oRange As String)" + Environment.NewLine + _
"Range(oRange).Select" + Environment.NewLine + _
"With Selection.Validation " + Environment.NewLine + _
".Delete" + Environment.NewLine + _
".Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=" + _
"xlBetween, Formula1:=""""""""""" + Environment.NewLine + _
".ErrorTitle = ""Locked Cell""" + Environment.NewLine + _
".ErrorMessage = ""This cell is locked for modification.""" + Environment.NewLine + _
".ShowError = True" + Environment.NewLine + _
"End With" + Environment.NewLine + _
"End Sub" + Environment.NewLine + _
"Sub DeleteThisModule()" + Environment.NewLine + _
"Dim vbCom As Object" + Environment.NewLine + _
"Set vbCom = Application.VBE.ActiveVBProject.VBComponents" + Environment.NewLine + _
"vbCom.Remove VBComponent:= vbCom.Item(""Module1"")" + Environment.NewLine + _
"End Sub"        

'Add the macro in excel workbook that we have created
        oModule.CodeModule.AddFromString(sCode)

End Sub
End Module

To execute the above code, copy paste the whole code in your application. You need to add 2 dll’s to make the compilation successful they are,

  • Microsoft.Office.Interop.Excel
  • Microsoft.Vbe.Interop

And make sure you have given access to external application to access Excel. For more details visit

6 Responses to “Create, Execute and delete Macro in excel using C#”

  1. Bas said

    Hello,

    I would like to know how can you make single cell read only,
    no method available for MakeCellReadOnly in your above code.

    Please help me.

    Thanks
    Bas

    • codingsense said

      Hi,

      In the code above you need to change in only one parameter, for example if you want only a cell “B2” to be read only then instead of
      string oRange = “B2:B6”;
      you need to give
      string oRange = “B2:B2”;

      for any other clarification please revert back.

      Thanks,
      Naveen Prabhu

  2. Hi, Naveen Prabhu.
    My Desktop: W7 64bit Ultimate, Ultimate Visual Studio 2010 and Excel 2010.
    I tried to make your code work, but gave the errors below.

    Please help me.
    I need very much that the my application C# run my macro after opening the file in Excel.

    Error 1 The namespace ‘WindowsFormsApplication1’ already contains a definition for ‘Program’ F:\USUARIOS\dtp2\xxx AQUI xxx\WindowsFormsApplication1\WindowsFormsApplication1\Program.cs WindowsFormsApplication1

    Error 2 The best overloaded method match for ‘System.Windows.Forms.Application.Run(System.Windows.Forms.ApplicationContext)’ has some invalid arguments F:\USUARIOS\dtp2\xxx AQUI xxx\WindowsFormsApplication1\WindowsFormsApplication1\Program.cs 18 13 WindowsFormsApplication1

    Error 3 Argument 1: cannot convert from ‘WindowsFormsApplication1.Form1’ to ‘System.Windows.Forms.ApplicationContext’ F:\USUARIOS\dtp2\xxx AQUI xxx\WindowsFormsApplication1\WindowsFormsApplication1\Program.cs 18 29 WindowsFormsApplication1

    Best Regards,
    silvio pontes

  3. Rehan said

    Hi,
    I am uploading na excel sheet, that contains a macro,but macro is disabled.How can i enable the macro after uploading my sheet.A
    Any suggestion is appreciated.

  4. Bhagyashree Chaudhari said

    hello,
    i have used Epplu library , now i have create macro and that macro was written in .xlsm file , but macro is disabled .
    and i have used
    ContentType = “application/vnd.ms-excel.sheet.macroEnabled.12”

    but its not working .

    do reply .
    Thank you

  5. nbhadradiya said

    I did tried this code for word document to add macro in word but after executing code it is not adding Macro to my word document. Can you guide me ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s