Why does this Using() give me an error?

Question

I am trying to open an (hundreds actually) excel file(s). I open the application but want to use the Using() functionality around each of the workbooks I open. Why is this resulting in an error?

using (Excel.Workbook wbXL = appXL.Workbooks.Open(_sourceFullPath, Type.Missing, Excel.XlFileAccess.xlReadOnly))
{
    //stuff with wbXL
}

using gets the red underline and says "'Microsoft.Office.Interop.excel.Workbook':Type used in a using statement must be implicitly convertible to 'System.IDisposable'.

How to make this work?

Answer 1

Pretty much what it says - you can only use using with classes that implement IDisposable, so that under the covers the compiler knows which function to call on finalisation - yourclass.Dispose(). The Excel interop classes don't implement this.

So you've got two choices:

1. Write your own wrapper class for Excel.Workbook that implements IDispose and either exposes the object itself to call methods on, or wraps those methods too, e.g.

   public class DisposableWorkbook : IDisposable
   {
       private Excel.Workbook _workbook = null;
   
       public DisposableWorkbook(Excel.Application appXL, String path,
                                 NotSureOfType otherArgument,
                                 Excel.XlFileAccess access)
       {
           _workbook = appXL.Workbooks.Open(path, otherArgument, access);
       }
   
       public Excel.Workbook Workbook
       {
           get { return _workbook; }
       }
   
       public void Dispose()
       {
           if (workbook != null)
           {
               workbook.Close(Excel.XlSaveAction.xlDoNotSaveChanges,
                              workbookToClose);
               workbook = null;
           }
       }
   }
   
   using (DisposableWorkbook dwbXL = new DisposableWorkbook(appXL,
             _sourceFullPath, Type.Missing, Excel.XlFileAccess.xlReadOnly))
   {
        Excel.Workbook wbXL = dwbXL.Workbook;
        // stuff with wbXL
   }
   

2. Implement using yourself, e.g.

   Excel.Workbook wbXL = null;
   try
   {
       wbxl = appXL.Workbooks.Open(_sourceFullPath, Type.Missing,
                                   Excel.XlFileAccess.xlReadOnly);
       //stuff with wbXL
   }
   finally
   {
       if (wbxl != null) wbxl.Close();
   }