Here are some code pieces which is helpful in VSTO development. (Office Excel Development with Visual Studio .Net)

• To get ActiveWorkbook

Excel.Workbook NewActiveWorkbook = Application.ActiveWorkbook;

• To get ActiveSheet

Excel.Worksheet NewActiveWorksheet = NewActiveWorkbook.ActiveSheet;

• To Show message in status bar

Application.StatusBar = "Report Generated.";

• To check whether a particular Add-In is installed

if (CheckAddIn("BabelFishHelper.xlam"))

{
     // show message
}

private bool CheckAddIn(string Name)

{

// check whether the babelfish add in exists
foreach (Excel.AddIn ai in Application.AddIns)

{

if (ai.Name == Name)

{

return ai.Installed;

}

}

return false;

}


• Set a value to range

Excel.Range YearMonthTitle = NewActiveWorkbook.Sheets[1].Range["year_month_title"];
YearMonthTitle.Value2 = Title;

• Delete a sheet without confirmation

NewActiveWorkbook.Sheets[1].Delete();

• Delete a sheet without confirmation

Application.DisplayAlerts = false;
NewActiveWorkbook.Sheets[1].Delete();
Application.DisplayAlerts = true;

• Hide a sheet

Globals.Sheet1.Visible = Excel.XlSheetVisibility.xlSheetHidden;

• Set a name range read-only

Globals.Sheet9.Cells.Locked = false;
Excel.Range year_month_title = Globals.Sheet9.Range["year_month_title"];
year_month_title.Locked = true;
Globals.Sheet9.Protect(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

• Set a sheet read-only

Globals.Sheet3.Protect(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
NewActiveWorkbook.Sheets[1].Protect(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

• Clear clip board

Clipboard.Clear();
Sometimes you get bellow exception when you try to clear the clipboard. In that case use bellow code.
private void ClearClipBoard()

{

try

{

System.Threading.Thread.Sleep(10000);

Clipboard.Clear();

}

catch (Exception ex)

{

mLoggingMessage = "ClearClipBoard : Clipboard Clear Error";

Logging.ErrorLogging(mLoggingMessage, ex);

ClearClipBoard();

}

}

• Get used range of a sheet

Excel.Range settings_NamedRange = Globals.Sheet9.UsedRange;

• Get rows of used range

foreach (Excel.Range range in NamedRange.Rows)

{

// do process

}

• Get a sheet from a workbook

Excel.Worksheet newSheet = NewActiveWorkbook.Sheets[2];

• Setting a value to a cell

newSheet.Range[“A1”, missing].Value2 = “new value”;

• Setting a value to a cell without flicker

Application.ScreenUpdating = true;
newSheet.Range[“A1”, missing].Value2 = “new value”;
Application.ScreenUpdating = false;

• When a cell contains value and formula, remove the formula and retain only the value

Application.ScreenUpdating = true;
string Function = “=GetSingleValue("1/1/2011 00:00:00 AM", "1/1/2011 00:00:00 AM", "30s", "Raw")”;
newSheet.Range[CellId, missing].Value2 = Function.ToString();
Application.ScreenUpdating = false;

// remove the formula
Excel.Range CopyRange = (Excel.Range) newSheet.Range[CellId, missing];
CopyRange.Copy(System.Type.Missing);
Excel.Range PasteRange = (Excel.Range) newSheet.Range[CellId, missing];
PasteRange.PasteSpecial(Excel.XlPasteType.xlPasteValues, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

• Get Row/Column id of a name range

Excel.Range NamedRange = newSheet.Range["year_month_title"];
int NamedRangeRow = NamedRange.Row;
int NamedRangeColumn = NamedRange.Column;

• Insert a new row (with formatting)

Excel.Range WholeRange = ((Excel.Range) newSheet.Rows[NamedRangeRow, System.Type.Missing]).EntireRow;
WholeRange.Copy(System.Type.Missing);
Excel.Range PasteRange = ((Excel.Range) newSheet.Rows[NamedRangeRow, System.Type.Missing]).EntireRow;
PasteRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);

• Setting a value to a range

Excel.Range range1 = newSheet.Cells[NamedRangeRow, NamedRangeColumn];
range1.WrapText = true;
range1.Value = “new value”;

• Copy the whole workbook to a new workbook

Globals.ThisWorkbook.Worksheets.Copy();
Excel.Workbook NewActiveWorkbook = Application.ActiveWorkbook;

• Copy a worksheet to a new workbook

Globals.Sheet1.Copy();
Excel.Workbook NewActiveWorkbook = Application.ActiveWorkbook;
Excel.Worksheet NewActiveWorksheet = NewActiveWorkbook.ActiveSheet;

• Setting a formula array to a cell

string StartCellId = “A1”;
string EndCellId = “B1”;
newSheet.Range[StartCellId, EndCellId].FormulaArray = Function.ToString();

• Break external links

NewActiveWorkbook .BreakLink("name_of _the_link", Excel.XlLinkType.xlLinkTypeExcelLinks);