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)
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