Naveen's Weblog

Bridge to future

Posts Tagged ‘Excel’

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

Posted in C# | Tagged: , , | 6 Comments »

Get All formula from excel cell

Posted by codingsense on March 1, 2009

Hi Friends,

In this topic we shall see how to get the formula from an excel cell. This program lists only the field in which user has entered a formula in excel sheet.
Here let us explore a good feature of exed known as “UsedRange”. Used Range gives only the cells those have been used, this helps us to avoid searcing in whole excel sheet. We can search only the cells in the UsedRange and fetch if the user has given any formula to cells or not, if he has given a formula lets fetch the formula and display the row, col and formula.

Download Project : 9KB

To access the excel file we shall use Late binding method using System.Reflection which helps to invoke any version of excel instance that is installed on my machine.

//Get Instance of excel on the machine
                objClassType = Type.GetTypeFromProgID("Excel.Application");
//Create new instance of Excel
                objExcelApp = Activator.CreateInstance(objClassType);

To get the UsedRange we can run a macro and get the RowCount and ColCount of the UsedRange and loop within the boundary.

//Get the used Rows and Cols from excel                
object UsedRange = objSheet.GetType().InvokeMember("UsedRange",BindingFlags.GetProperty,null,objSheet,null);
object Rows = UsedRange.GetType().InvokeMember("Rows", BindingFlags.GetProperty, null, UsedRange, null);
object objRowCount = Rows.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, Rows, null);
object Cols = UsedRange.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, UsedRange, null);
object objColCount = Cols.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, Cols, null);

Next we will loop throught each cells within the UsedRange boundary and check if the Cells[i,j].Formula is starting with “=”. If yes then its a formula, Add the row, col and formula into the list and proceed to next cell.

//Get cells object                
object Cells = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty,
null, objSheet, null);
for (int RowIndex = 1; RowIndex <= RowCount; RowIndex++)
{
for (int ColIndex = 1; ColIndex <= ColCount; ColIndex++)
{
object objCellText = Cells.GetType().InvokeMember("Item", BindingFlags.GetProperty, 
null, Cells, new object[] { RowIndex, ColIndex });
string CellText = Convert.ToString(objCellText.GetType().InvokeMember("Formula", 
BindingFlags.GetProperty,null, objCellText, null));
if (CellText.StartsWith("="))
{
FormulaCell cell = new FormulaCell(RowIndex,ColIndex,CellText);
ListOfFormulae.Add(cell);
}
}

After searching each cell within the used range we get the collection of FormulaCells. After finishing our task, the most important is to disposed the Unmanaged object. For disposing let us use Marshal class.

//Invoke the close method of WorkBooks
objBooks.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, objBooks, null);
//Release the COM Excel application using Marshal class                
Marshal.ReleaseComObject(objExcelApp);

Now lets display it.

if (ListOfFormulae != null)
{
for (int Index = 0; Index < ListOfFormulae.Count; Index++)
{
Console.WriteLine("Row = " + ListOfFormulae[Index].Row +
", Col = " + ListOfFormulae[Index].Col +
" has the formula " + ListOfFormulae[Index].Value);
}
}

Hope you have understood the following,

  • How Late binding is done in C#.
  • How to use a macro through C#.
  • How Properties of another application can be accessed using a GetType().InvokeMember() and BindingFlags.GetProperty.
  • How Functions of another application are invoked using a GetType().InvokeMember() and BindingFlags.InvokeMethod.
  • The important one is how to dispose the unmanaged object.

If you have any doubts, please comment.

Happy learing 🙂

Posted in C# | Tagged: , | 3 Comments »