Using VSTO Add-In To Automate Frequent Excel 2007 Tasks

In this article, we will use Visual Studio Tools for Office (VSTO) to create an Excel Add-In that implements some of my frequent used tasks in Excel 2007. This automation of tasks has the advantage to make us more productive as users.

Introduction
In this article, we will use Visual Studio 2008 SP1 C# to create an Excel Add-In that implements each of the tasks listed below for Excel 2007:
- Task 1: Copy sheet contents to a new sheet.
- Task 2: Reverse string in current cell.
- Task 3: Calculate the maximum value of a numerical range selection.
- Task 4: Save as xls file
- Task 5: Save as csv file

For example, if you want to Save as csv file, the following steps are required in Excel 2007:
- Click the office button.
- Click Save As to display the Save As dialog box.
- Select CSV (comma delimited) in the "Save as type" drop-down list.
- Click Save.

We will program the steps above in code so that we can perform this task with just a selection and button click on the ribbon tab; thus, our productivity increases if we need to do this task often.
We will create a custom ribbon as shown in figure 1. This ribbon is made up of two groups: The first group will contain the first 3 tasks as buttons; the second group will contain a drop down for selecting the file format and a Save button. Finally we will implement all this functionality using VSTO and Excel automation objects.


Fig 1 Excel custom ribbon to build.

Target audience
This tutorial is targeted at the reader who is familiar with the basics of Excel automation using VBA or COM Add-In, Excel 2007 ribbon and they would like a practical introduction
on how to build a VSTO Add-In. I found that most tutorials are either too time consuming to follow or trying to do many things at once to show the power of VSTO and .NET.

Create an Excel Add-In
To create the Excel AddIn project
1. Open Microsoft Visual Studio 2008.
2. On the File menu, expand New and click Project to create a new Visual Studio project.
3. In the New Project dialog box, in the Project types pane, expand Visual C# | Office | 2007.
4. In the Templates pane, select Excel 2007 Add-in.
5. In the Name box, type ExcelAddIn1.
6. In the Solution name box, type ExcelAddIn1.
7. Select Create Directory for Solution, and click OK to create the new Excel 2007 add-in.
The Solution Explorer displays the opened solution, and shows the main code file. (ThisAddIn.cs)


Fig 2 Excel AddIn new project dialog.

Add Custom Ribbon (Visual Designer)
1. Select Project and right click Add New Item context menu.
2. In the Add New Item dialog box, select the Ribbon (Visual Designer) item template and click Add.
Visual Studio opens the Ribbon Designer (see Figure 3) and creates "Group1" by default.
3. In the Properties window, perform the following tasks:
  a. Set the value of
(Name) to groupCommon.
  b. Set the value of
Label to "Common Tasks".


Fig 3 Default ribbon designer.

Add common task group
To Create buttons:
1. In the ribbon designer. select Group1 and change the Label property to Common Tasks.
2. From the Toolbox, drag three Button controls into the Common Tasks group.

To set first task button:
1. In the ribbon designer, select first button.
2. In the Properties window, perform the following tasks:
  a. Set the value of
(Name) to btnCopySheet.
  b. Set the value of
Label to "Copy Sheet".
  c. Change the
ControlSize property to RibbonControlSizeLarge.
  d. Set the value of
OfficeImageId to TableExcelSpreadsheetInsert. (My article in [4] provides the details on how to assign this value)
  e. Set the value of
SuperTip to "Copy current sheet to a new sheet".
  f. Leave other values unchanged.
3. Add button event handler:
  a. In the ribbon designer, double click the selected button. The btnCopySheet_Click event handler is created.
  b. Add the code below to implement the handler.
   //Get the application object
    Excel.
Application lApp = Globals.ThisAddIn.Application;
    
//Get the current Sheet object
    Excel.Worksheet currentSheet = lApp.ActiveSheet
as Excel.Worksheet;
    
//Create new sheet before currentSheet
    Excel.Worksheet newSheet = (Excel.Worksheet)lApp.ActiveWorkbook.Sheets.
Add(Type.Missing, currentSheet, Type.Missing, Excel.XlSheetType.xlWorksheet);
    currentSheet.
Cells.Copy(newSheet.Cells);

To set second task button:
1. In the ribbon designer, select second button.
2. In the Properties window, perform the following tasks:
  a. Set the value of
(Name) to btnReverseCell.
  b. Set the value of
Label to "Reverse Cell contents".
  c. Change the
ControlSize property to RibbonControlSizeLarge.
  d. Set the value of
OfficeImageId to FormatCellsMenu.
  e. Set the value of
SuperTip to "Reverse Cell contents.".
  f. Leave other values unchanged.
3. Add button event handler:
  a. In the ribbon designer, double click the selected button. The btnCopySheet_Click event handler is created.
  b. Add the code below to implement the handler.
    //Get the application object
    Excel.
Application lApp = Globals.ThisAddIn.Application;
    
//Get the active cell object of the current sheet
    Excel.
Range myCell = lApp.ActiveCell as Excel.Range;
    
if (myCell.Value2 != null)
    {
    myCell.Value2 = ReverseString(myCell.Value2.ToString());
    }

  c. Add the following method to
reverse the string.
        public static string ReverseString(string s)
        {
             
char[] arr = s.ToCharArray();
          
Array.Reverse(arr);
            
return new string(arr);
        }

To set third task button:
1. In the ribbon designer, select third button.
2. In the Properties window, perform the following tasks:
  a. Set the value of
(Name) to btnMaxRange.
  b. Set the value of
Label to "Max value".
  c. Change the
ControlSize property to RibbonControlSizeLarge.
  d. Set the value of
OfficeImageId to GroupFunctionLibrary.
  e. Set the value of
SuperTip to "Maximum value of all cells in the selected range.".
  f. Leave other values unchanged.
3. Add button event handler:
  a. In the ribbon designer, double click the selected button. The btnCopySheet_Click event handler is created.
  b. Add the code below to implement the handler.
            //Get the application object
            Excel.
Application lApp = Globals.ThisAddIn.Application;
            
try
            {
                 
//Get the current selection  of the current sheet
                 Excel.
Range selection = lApp.Selection as Excel.Range;
                 
//Get the maximum values using the function Max
                 object maxValue = lApp.WorksheetFunction.Max(selection, 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,
                  
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, Type.Missing);
                 MessageBox.
Show("Maximum value is " + maxValue.ToString());
             }
             
catch (Exception ex)
             {
                 MessageBox.
Show(ex.Message);
            }

Add common save tasks group
To add new group:
1. From the Toolbox, drag a new group to the right of the existing group into the ribbon designer.
2. In the ribbon designer. select the new group.
3. In the Properties window, perform the following tasks:
  a. Set the value of
(Name) to groupSave.
  b. Set the value of
Label to "Common Save Tasks".

To add Drop down
1. From the Toolbox, drag a DropDown control into the new group.
2. In the Properties window, perform the following tasks:
  a. Set the value of
(Name) to ddSaveAs.
  b. Set the value of
Label to "Save as".
  c. In the Items property, click the ellipsis.
     c.1 Click Add to add a new item.
     c.2 Set the Label property of the first item to CSV.
     c.3 Click Add to add a new item.
     c.4 Set the Label property of the first item to XLS.
     c.5 Click OK to close the dialog.

To add Save As Button
1. From the Toolbox, drag a new button into the new group.
2. In the Properties window, perform the following tasks:
  a. Set the value of
(Name) to btnSaveAs.
  b. Set the value of
Label to "Save As".
  c. Change the
ControlSize property to RibbonControlSizeLarge.
  d. Set the value of
OfficeImageId to FileSaveAs.
  e. Set the value of
SuperTip to "Copy current sheet to a new sheet".
  f. Leave other values unchanged.
3. Add button event handler:
  a. In the ribbon designer, double click the selected button. The saveButton_Click event handler is created.
  b. Add the code below to implement the handler.
SaveWorkbook(ddSaveAs.SelectedItem.Label);
  c. Add the following method to save the workbook as XLS or CVS format.
SaveWorkbook(ddSaveAs.SelectedItem.Label);  
  d. Add the following methods to save the workbook for the book and given format.
        private void SaveWorkbook(Excel.Workbook book, string path, string format)
        {
          
Debug.Assert(!String.IsNullOrEmpty(format), "Invalid format");
          
Debug.Assert(!String.IsNullOrEmpty(path), "Invalid path");
            path =
Path.ChangeExtension(path, format.ToLower());
            
if (format == "XLS")
            {
                 
//XlFileFormat.xlExcel8 will save the book as Excel 97-2003 format.
                 book.
SaveAs(path, Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing,
                  
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            
else if (format == "CSV")
            {
                 
//XlFileFormat.xlCSV will save the book as CSV format.
                 book.
SaveAs(path, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing,
                  
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
        }

        private void SaveWorkbook(string format)
        {
            Excel.Workbook lBook = Globals.ThisAddIn.
Application.ActiveWorkbook;
             
//If book has not been saved (eg. book 1) used the current name else use full path
            
if (String.IsNullOrEmpty(lBook.Path))
            {
                SaveWorkbook(lBook, lBook.
Name, format);
             }
             
else
            {
                SaveWorkbook(lBook, lBook.
FullName, format);
             }
        }



Fig 4 Ribbon designer showing both groups.

Run your code
To run
the above code in Excel, press F5. A new excel workbook will be opened and our custom ribbon will be displayed in the Add-Ins tab as shown in figure 1. then start testing each task:
Task 1: On the current sheet click the copy sheet button.It copies the contents of the current sheet to a new sheet.
Task 2: Type a string in cell A5 and click the reverse string button. It reverses the contents of cell A5.
Task 3: Type numbers on cells A5 to C6 as follows 3, 4, 5, 7, 23, 24 and click the maximum value button. It shows a message "Maximum value is 24".
Task 4: Open one of your Excel 2007 workbooks and choose XLS and click the "Save as" button. The file with extension xls will be saved where your existing file is.
Task 5: Open one of your Excel 2007 workbooks and choose CSV and click the "Save as" button. The file with extension csv will be saved where your existing file is.

Conclusion
We build an Excel 2007 Add-In that provides shortcuts to perform frequent tasks without going through painful intermediate steps.
We learn how to create and customize a ribbon using VSTO and C#.
We
provide good sample code to start building your own Add-In automation tasks.

References
[1] MSDN,
Excel Object Model Overview
[2] Robert Green, Create an Application-Level Add-In to Automate Common Office Tasks (PowerPoint 2007)
[3] MSDN,  VSTO Team blog
[4] mas_oz2003, How to assign built-in Office icons to your VSTO Ribbon controls

By Miguel Santos   Popularity  (10624 Views)