In a previous post (Integrating with Excel) I gave a code example of how RE can integrate with Excel. The code was relatively simple. One of the problems with working with Excel is if you embed references to Excel in your project but you are not sure of the version of Excel you will be working with.

The following code sets up the Excel objects so that they can be used throughout the code with convenient intellisense.

    Dim objExcel As Excel.Application     ' Excel application  
    Dim objBook As Excel.Workbook      ' Excel workbook 
    Dim objSheet As Excel.Worksheet     ' Excel Worksheet  
    Dim oConstit As New CRecord   

    oConstit.Init REApplication.SessionContext 
    Set objExcel = CreateObject("excel.application") 'Starts the Excel Session 
    Set objBook = objExcel.Workbooks.Open("C:test.xls")

The problem is though in order for this to compile you have to put a reference to Excel in the references. This version will vary depending on the version you are using and the application will crash if your target user has a different version. What can be done about this?

The most obvious choice is to remove the reference to Excel and do everything using late binding. All the objects have to be of type Object and there will be no intellisense. What is more there will be no compile time errors either. The best way around this is to test your code with the references and then remove them before going live.

    Dim objExcel As Object  
    Dim objBook As Object 
    Dim objSheet As Object      

    On Error Resume Next  
    Set objExcel = GetObject(, "excel.application")   

    If Err.Number = 429 Then 
        Set objExcel = CreateObject("excel.application") 
    End If   

On Error GoTo 0   

    Set objBook = objExcel.Workbooks.Open("C:test.xls")

We define our Excel objects as simply Object. We then use the GetObject function to get an existing instance of Excel (you may want to jump to creating a new one so as not to interfere with other ones). If there is not existing instance we create a new one. We then use the Excel object as we did previously (but have to remember the syntax as we are not prompted now).

So far we have seen how we can use Excel from VB6 code but more and more we want to use RE and .NET. How does this work? The Excel object model is COM based so we have to have an interop to use. Luckily these are available but need to be downloaded and installed (see MSDN article). Even then we still have the same problem with versions. That aside there are some pointers we should remember.

When working with Excel from .NET remember that setting the reference to Nothing may not be enough for it to be cleaned up. Instead I always call the following method in order to release the objects

    Private Sub Kill(ByVal o As Object)  
       Try   

            System.Runtime.InteropServices.Marshal.ReleaseComObject(o) 
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o) 
            GC.Collect() 
        Catch 
        Finally 
            o = Nothing 
        End Try 
    End Sub

Also according to one article you should never implicitly reference Excel objects. For example instead of doing the following:

       XLSheet.Cells(row, column).BorderAround(1, ColorIndex:=16, Weight:=2)

Do this:

        Dim cells As Excel.Range 
        Dim cell As Excel.Range   

        cells = _XLSheet.Cells 
        cell = cells.Item(row, column) 
        cell.BorderAround(1, ColorIndex:=16, Weight:=2)   

        Kill(cell) 
        Kill(cells) 

Of course many would say that all this work is simple not worth it. One solution is to do away with COM altogether and generate the Excel file in XML. When you open it up it will open up correctly and then you can save it back in native Excel format. See this article for the code