Microsoft Excel 2003 interop + COM object releasing

G'day,

I recieved an unusual error message from a VB.net 2005 application that automates excel and wanted to confirm the correct way to shut down excel automation components.

The user got a new PC the other day and started to get the following error messages. 'Creating an instance of the COM component with CLSID {00020820-0000-0000-c000-000000000046} from the IClassFactory failed due to the following error: 80010105'. So I assume this is an Microsoft Excel 2003 automation error.

I reviewed the code and found a cross post http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=344206&SiteID=1 suggesting that GC.Collect() should be Marshal.ReleaseCOMObject(objExcelApp). Is that correct for a Microsoft Office PIA component

Here is the code.

Public Function GetDataSet(ByVal dataFilePath As String, ByVal excelSheetName As String) As System.Data.DataSet

Dim objExcelApp As New Microsoft.Office.Interop.Excel.Application

Dim objExcelWorkSheeet As New Microsoft.Office.Interop.Excel.Worksheet

Dim ds As DataSet = New DataSet

Dim dsRow As System.Data.DataRow

Dim intWorkBookIndex As Integer = 1 ' Note: Excel Worksheets are 1 base.

Dim intMaxRow As Integer = 1

Dim intMaxColumn As Integer = 1

' Check to see if the file exists

If System.IO.File.Exists(dataFilePath) = False Then

Throw New System.IO.FileNotFoundException(System.String.Format(System.Globalization.CultureInfo.InvariantCulture, My.Resources.FileNotFoundException, dataFilePath))

End If

' Open the file

objExcelApp.Workbooks.Open(dataFilePath, False, True)

' Check to see that worksheets exist in the document.

If objExcelApp.Worksheets.Count = 0 Then

Throw New System.ArgumentOutOfRangeException(dataFilePath, System.String.Format(System.Globalization.CultureInfo.InvariantCulture, My.Resources.ExcelWorksheetException, dataFilePath))

End If

' Locate the appropriate worksheet.

' ASSUMUMPTION: Load the first one just in case.

objExcelWorkSheeet = CType(objExcelApp.Worksheets.Item(intWorkBookIndex), Worksheet)

If excelSheetName.Trim <> "" Then

' Try to load a specifically named sheet.

For intLoop As Integer = 1 To objExcelApp.Worksheets.Count

If CType(objExcelApp.Worksheets.Item(intLoop), Worksheet).Name = excelSheetName Then

objExcelWorkSheeet = CType(objExcelApp.Worksheets.Item(intWorkBookIndex), Worksheet)

Exit For

End If

Next

If objExcelWorkSheeet.Name <> excelSheetName Then

Throw New System.ArgumentOutOfRangeException(excelSheetName, System.String.Format(System.Globalization.CultureInfo.InvariantCulture, My.Resources.ExcelWorksheetNameException, dataFilePath, excelSheetName))

End If

End If

' Convert the worksheet to a dataset.

Debug.WriteLine(dataFilePath & ": MaxColumn " & intMaxColumn.ToString & " MaxRow " & intMaxRow)

' Create a table with the columns, preset as strings

For intLoopColumn As Integer = 1 To objExcelWorkSheeet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Column

If intMaxRow > 0 Then

ds.Tables.Item(0).Columns.Add(objExcelWorkSheeet.Cells.Range(Chr(64 + intLoopColumn) & "1").Text.ToString(), GetType(System.String))

Else

' Blank sheet, so add the letters of the alphabet for the columns that were in the worksheet.

' Chr(65) = A

ds.Tables.Item(0).Columns.Add(Chr(64 + intLoopColumn), GetType(System.String))

End If

Next

' Loop throught the spreasheet and add all the data to the dataset.

For intLoopRow As Integer = 2 To objExcelWorkSheeet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row

For intLoopColumn As Integer = 1 To objExcelWorkSheeet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Column

'Debug.WriteLine("DataRow: " & Chr(64 + intLoopColumn) & intLoopRow.ToString)

' TODO: This might slow down the performance of the solution

If intLoopRow Mod 250 = 0 Then

End If

Next

Next

' Return the results.

' Excel Automation cleanup

objExcelApp = Nothing

End Function