Excel with Macros

Currently I am using Excel 2010. So we are going to create simple macros here.

What is Macro ?
A very simple answer is,
Through macros you don’t need to perform same task or action on same type of data. Macros are the built-in feature of Excel that enables user to record different tasks and apply them over on some another portion of the data. It works on the back-end, recording each and every mouse click, keystrokes that you do while performing the task. Down the line if you want to done with the task in a same way, you will just have to play the recorded Macro back.


Suppose I have a worksheet in excel and I want to display product "Grade" and "Remark" according to product "Quality" column. If product quality greater than or equal to 70 then Grade should be "A" and Remark should be "Better" automatically, as smart macros fired in background.



 Exploring step by step :

 Step1 : Go to Start -> Microsoft Excel 2010 , blank worksheet will be open for you.

 Step2 : Click on View -> Micros , See below image

 


Step3 : Click on Record Macros, pop up window will be open, you can set your macro name,
shortcut key , store macro in dropdown option and description and click Ok. See below image

Step4 : Now set fx function on Grade column as =IF(B2>=70,"A","B") and set Remark column as
 =IF(C2="A","Better","Average").

Step5 : Click on IF function and click OK button to set condition for Grade column , See below Image.

Step5 : Click on IF function and click OK button to set condition for Remark column , See below Image.

   Save Excel file Type as Excel - Macro Enabled Workbook (*.xlsm).

  Step6 : Get Grade and Remark column value by giving input to Quality column through UI.
  -> Go to Start open  Microsoft Visual Studio 2010.
  -> Click on New Project you can choose Windows Or Web base application. I am exploring with windows base. See below Image :

Step7 : Go to Solution Explorer -> Right Click On References -> Add Microsoft Excel Lib reference from COM, See below Image :

Step8 : Adding Dropdown list (selecting input for Excel) , and two Text Boxes (to getting output from Grade and Remark column from Excel) See below Form1 Image :

Step8 : Adding Dropdown list collection value.
Right click on DropdownList -> Click on Properties -> Items property and add Items as,
See below Image :

Step8 : Double click on Dropdownlist. Add below code to Dropdown selected index changed event.

Code:

private void cboInputBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            ExcelApplicationClass excelApp = new ExcelApplicationClass();
            ExcelWorkbook workbook = (Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);
            ExcelWorksheet worksheet;

            // Opening excel file

            //workbook = excelApp.Workbooks.Open(@"C:\Book1.xslx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            workbook = excelApp.Workbooks.Open(@"C:\testing proj\Book3.xlsm", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            
            // Getting first Worksheet

            worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);

            // Setting cell values


            ((Excel.Range)worksheet.Cells["2", "B"]).Value2 = cboInputBox.SelectedItem.ToString();
            txtGrade.Text = ((Excel.Range)worksheet.Cells["2", "C"]).Value2.ToString();
            txtRemark.Text = ((Excel.Range)worksheet.Cells["2", "D"]).Value2.ToString();
            
            workbook.Save();
            workbook.Close(0, 0, 0);
            excelApp.Quit();
        }

Run The Application and select Input value from Dropdownlist you will get output at Grade and Remark column from excel. Also open an excel to see all values are getting reflect.

Copy Full Code :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExcelOperation
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           
        }

        private void cboInputBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            ExcelApplicationClass excelApp = new ExcelApplicationClass();
            ExcelWorkbook workbook = (Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);
            ExcelWorksheet worksheet;

            // Opening excel file

            //workbook = excelApp.Workbooks.Open(@"C:\Book1.xslx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            workbook = excelApp.Workbooks.Open(@"C:\testing proj\Book3.xlsm", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            
            // Getting first Worksheet

            worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);

            // Setting cell values


            ((Excel.Range)worksheet.Cells["2", "B"]).Value2 = cboInputBox.SelectedItem.ToString();
            txtGrade.Text = ((Excel.Range)worksheet.Cells["2", "C"]).Value2.ToString();
            txtRemark.Text = ((Excel.Range)worksheet.Cells["2", "D"]).Value2.ToString();
            
            workbook.Save();
            workbook.Close(0, 0, 0);
            excelApp.Quit();
        }


    }
}