Archive for the ‘VSTO’ Category

Automatically create Getter Setter in Visual Studio

Tuesday, January 5th, 2010

private string directory;

This is your member variable. Highlight and right click on ‘directory’, then choose Refactor/Encapsulate Field.

Produces the following code automatically:

public string Directory
{
get { return directory; }
set { directory = value; }
}

Closing Excel in C#: always one instance left in task manager

Tuesday, January 5th, 2010

There are loads of forum posts on trying to close Excel from within C#. In my case, when opening a number of workbooks, no matter how I tried, there was always one Excel instance left open in the Task Manager, even if I specificially used .quit or any other of the 10’s of methods I tried. This is how I finally solved the problem:

With just one excelApp instance:
Excel.Application excelApp = new Excel.Application();

Search through the Excel files and open the ones you want:

workbook = excelApp.Workbooks.Open(name, false, true, missing etc);

Make sure to close the workbook when finished with it:
//end the workbook session, checking if has been assigned
//a workbook object successfully first
if (workbook != null)
{
workbook.Close(false, Type.Missing, Type.Missing);
excelApp.Workbooks.Close();
Marshal.ReleaseComObject(workbook);
}

Finally, Kill the excel app. I dont use the word Kill lightly, because it was a
very annoying problem:

excelApp.Quit();
GC.Collect();
Marshal.FinalReleaseComObject(excelApp);

Done and dusted.

C# to Excel

Tuesday, November 17th, 2009

There are a number of ways to move data between C# and Excel. I recently came
accross the following tutorials.

The first using an ODBC connection to populate a DataSet.

The second using an OleDb connection without having to use the Excel COM Object.

A more complete list of ‘Excel to C#’ methods is available from the microsoft support site:

  • Transfer data cell by cell.
  • Transfer data in an array to a range of cells.
  • Transfer data in an ADO recordset to a range of cells by using the CopyFromRecordset method.
  • Create a QueryTable object on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data source.
  • Transfer data to the clipboard, and then paste the clipboard contents into an Excel worksheet
  • Named Ranges – research paper at EuSpRIG 2009

    Friday, September 11th, 2009

    Co authored a paper recently, “An Exploratory Analysis of the Impact of Named Ranges on the Debugging Performance of Novice Users” (http://arxiv.org/abs/0908.0935) which presents evidence that might call into question the unsupported contention that Named Ranges are a good idea.

    The paper is discussed in an industrial context by the good people at Financial Mechanics:
    http://www.fi-mech.com/the+swamp+fox/dispatches/?p=222

    Reading Named Ranges in Excel using C#

    Tuesday, September 1st, 2009

    Couldnt find any good examples of reading Named Ranges from a workbook, but eventually figured it out. Here some of the code.

    
    private Excel.Workbook m_workbook;
    object missing = Type.Missing;
    
            public void testNamedRangeFind()
            {
                m_workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
                int i = m_workbook.Names.Count;
                string address = "";
                string sheetName = "";
    
                if (i != 0)
                {
                    foreach (Excel.Name name in m_workbook.Names)
                    {
                        string value = name.Value;
                        //Sheet and Cell e.g. =Sheet1!$A$1 or =#REF!#REF! if refers to nothing
                        string linkName = name.Name;
                        //gives the name of the link e.g. sales
                        if (value != "=#REF!#REF!")
                        {
                            address = name.RefersToRange.Cells.get_Address(true, true, Excel.XlReferenceStyle.xlA1, missing, missing);
                            sheetName = name.RefersToRange.Cells.Worksheet.Name;
                        }
                        Debug.WriteLine("" + value + ", " + linkName + " ," + address + ", " + sheetName);
                    }
                }
    
            }
    
    

    The following references are included:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using System.Diagnostics;
    using System.Windows.Forms;
    using System.IO;

    Excel Find method example using C#/VSTO

    Tuesday, September 1st, 2009

    Found a good example of finding text or formula values in Excel using C#, can be viewed here.

    There is a simple change I would make to the example: the first item found is actually the
    last instance of the item in the worksheet being searched, so I used the following –

    // If Find doesn’t find anything, rgFound will be null
    if (rgFound != null)
    {
         // Save the address of the first found item –
        // it will be used in a loop terminating condition.
        sFirstFoundAddress = rgFound.get_Address(
                       true, true, Excel.XlReferenceStyle.xlA1, missing, missing);

       // Continue finding subsequent items using FindNext
        rgFound = ws.Cells.FindNext(rgFound);
        string sAddress = rgFound.get_Address(
                        true, true, Excel.XlReferenceStyle.xlA1, missing, missing);

        // Start a loop that calls FindNext until
       // the first found cell is found again
       while (!sAddress.Equals(sFirstFoundAddress))
       {

             //do something with each found item

       }

    //Finally, get the first found cell – which will be
    //the most bottom right found cell, and do something with it
    r1 = ws.get_Range(sFirstFoundAddress, missing) as Excel.Range;

    Visual Studio 2008 and SQL Server 2008 Express – needs SP1

    Thursday, August 27th, 2009

    Need to up install SP1 for VS 2008 to get support for SQL Server 2008

    Also, if using the web download tool from Microsoft, it may install the 64bit version (a problem if
    you use 32bit os!!). In which case, and error message of: Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly,
    appears. To work around this problem, uninstall the 64-bit version of SQL Server Express 2008, and then install the 32-bit version of SQL Server Express 2008
    (great advice from the kb on this this article

    Ambiguity using Worksheet Activate

    Wednesday, August 26th, 2009

    Activate is both a method and an event of Application.Excel, so the Activate keyword must be cast to either an event or method.

    Examples (based on a VSTO Excel 2007 Add-in):

    Method

    Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
    Excel.Worksheet worksheet = workbook.Sheets["Sheet2"] as Excel.Worksheet;
    //The next line will throw a warning
    //worksheet.Activate();
    //Need to cast as a method
    ((Excel._Worksheet)worksheet).Activate();
    //or
    ((Microsoft.Office.Interop.Excel._Worksheet)worksheet).Activate() ;
    

    Event

    ((Excel.DocEvents_Event)worksheet).Activate +=
                    new Microsoft.Office.Interop.Excel.DocEvents_ActivateEventHandler(
                        Worksheet_Activate);
    

    VSTO Power Tools – Control state between CTP and Ribbon

    Tuesday, August 25th, 2009

    Ver 1.0 available from here

    VSTO_PTRibbonIDs.exe gives access to the Office images that can be associated with Ribbon controls.

    VSTO_PT.exe – among other things, has reusable code to control the state between CTP (custom task pane) and Ribbon e.g. CTP visible on one open document, but not on another etc.

    Documentation and how to here

    Visual Studio Tools for Office 2007 Book, Eric Carter and Eric Lippert – Get the code

    Tuesday, August 18th, 2009

    This book, which is probably the best VSTO book for excel programmers (but not for beginners), does not come with a CD, but I only noticed yesterday that you can view the book online once you have purchased a print copy.

    On the last page there is an activation key which can be used at www.informit.com/safarifree. Very handy for copying the code.