The following goes to extremes to ensure objects are created and disposed of properly and will handle the majority of issues e.g. file does not exists, sheet does not exist, bad cell address. It does not cover all issues but it's a very good beginning and if you spent time to understand the code you can adapt and modify as needed.
The class below does assertion on file name and sheet name. From there try-catch are used to trap exceptions and release objects upon a runtime exception being raised along with no exceptions and properly releasing objects.
Most developers will code in a code module (I am guilty of demos with code modules) but a class is the better way to go. Read all the descriptions on properties and comments in the code instead of simply adapting said code.
The most important parts are done when an exception occurs, we can inspect the property ExcelInfo and note the overridden ToString.
Example
Sample usageCode:Option Strict On Option Infer Off Imports System.Runtime.InteropServices Imports Excel = Microsoft.Office.Interop.Excel Public Class ExcelOpenSimple ''' <summary> ''' Indicates exceptions were raised ''' </summary> ''' <returns></returns> Public Property HasErrors As Boolean = False ''' <summary> ''' Exception information that can be inspected once ''' we have exited this class. ''' </summary> ''' <returns></returns> ''' <remarks> ''' ExceptionInformation is at bottom of this file ''' </remarks> Public Property ExceptionInfo As New ExceptionInformation ''' <summary> ''' File name to work on ''' </summary> ''' <returns></returns> Public Property FileName As String ''' <summary> ''' Sheet to work on ''' </summary> ''' <returns></returns> Public Property SheetName As String ''' <summary> ''' Provides a way to return a simple list of cell data. ''' </summary> ''' <returns></returns> ''' <remarks> ''' For showing we can access cell data. ''' </remarks> Public Property Data As String Public Sub New() ExceptionInfo = New ExceptionInformation End Sub Public Sub Open() ' ' Used to remember used objects that upon an exception ' or successfully completely are work will dispose of objects ' Dim AnnihilationList As New List(Of Object) If IO.File.Exists(FileName) Then ' set to true once the sheet is located Dim Proceed As Boolean = False ' create empty objects to access Excel Dim xlApp As Excel.Application = Nothing Dim xlWorkBooks As Excel.Workbooks = Nothing Dim xlWorkBook As Excel.Workbook = Nothing Dim xlWorkSheet As Excel.Worksheet = Nothing Dim xlWorkSheets As Excel.Sheets = Nothing Dim xlCells As Excel.Range = Nothing xlApp = New Excel.Application AnnihilationList.Add(xlApp) xlApp.DisplayAlerts = False xlWorkBooks = xlApp.Workbooks AnnihilationList.Add(xlWorkBooks) xlWorkBook = xlWorkBooks.Open(FileName) AnnihilationList.Add(xlWorkBook) xlApp.Visible = False xlWorkSheets = xlWorkBook.Sheets AnnihilationList.Add(xlWorkSheets) ' ' Find sheet name user provided ' For x As Integer = 1 To xlWorkSheets.Count Try xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet) If xlWorkSheet.Name = SheetName Then Proceed = True Exit For Else AnnihilationList.Add(xlWorkSheet) End If Catch ex As Exception HasErrors = True ExceptionInfo.UnKnownException = True ExceptionInfo.Message = $"Error finding sheet: '{ex.Message}'" ExceptionInfo.FileNotFound = False ExceptionInfo.SheetNotFound = False Proceed = False ReleaseExcelObject(xlWorkSheet) End Try Next If Proceed Then ' ' Do something, in this case collect simple data ' AnnihilationList.Add(xlWorkSheet) Dim sb As New Text.StringBuilder ' ' in a real app we might pass in a range of cells ' Dim Cells As String() = {"A1", "B2", "B3", "B4"} For Each cell As String In Cells Try xlCells = xlWorkSheet.Range(cell) sb.AppendLine($"{cell} = '{xlCells.Value}'") ReleaseExcelObject(xlCells) Catch ex As Exception HasErrors = True ExceptionInfo.Message = $"Error reading cell [{cell}]: '{ex.Message}'" ExceptionInfo.FileNotFound = False ExceptionInfo.SheetNotFound = False ReleaseExcelObject(xlCells) xlWorkBook.Close() xlApp.UserControl = True xlApp.Quit() ReleaseObjects(AnnihilationList) Exit Sub End Try Next Data = sb.ToString Else HasErrors = True ExceptionInfo.SheetNotFound = True ExceptionInfo.FileNotFound = False End If xlWorkBook.Close() xlApp.UserControl = True xlApp.Quit() ReleaseObjects(AnnihilationList) Else HasErrors = True ExceptionInfo.FileNotFound = True ExceptionInfo.SheetNotFound = False End If End Sub ''' <summary> ''' Given a list of Excel objects, dispose of each object ''' and while doing so check to ensure an object is not ''' null or nothing. ''' </summary> ''' <param name="AnnihilationList"></param> <System.Diagnostics.DebuggerStepThrough()> Private Sub ReleaseObjects(ByVal AnnihilationList As List(Of Object)) For x As Integer = 0 To AnnihilationList.Count - 1 ReleaseExcelObject(AnnihilationList(x)) Next End Sub ''' <summary> ''' Release a Excel object while first checking to see if ''' the object is in a valid state. ''' </summary> ''' <param name="excelObject"></param> <System.Diagnostics.DebuggerStepThrough()> Private Sub ReleaseExcelObject(ByVal excelObject As Object) Try If excelObject IsNot Nothing Then Marshal.ReleaseComObject(excelObject) excelObject = Nothing Else Console.WriteLine() End If Catch ex As Exception excelObject = Nothing End Try End Sub End Class Public Class ExceptionInformation Public Property SheetNotFound As Boolean Public Property FileNotFound As Boolean Public Property UnKnownException As Boolean Public Property Message As String <System.Diagnostics.DebuggerStepThrough()> Public Overrides Function ToString() As String Return $"File not exist: {FileNotFound}{Environment.NewLine}Sheet not exists: {SheetNotFound}{Environment.NewLine}Message: '{Message}'" End Function End Class
Code:Dim fileName As String = IO.Path.Combine(Application.StartupPath, "Customers.xlsx") Dim demo As New ExcelOpenSimple With {.FileName = fileName, .SheetName = "Orders"} demo.Open() If demo.HasErrors Then ' in a real app it doesn't make sense to show all this but we are looking anyways MessageBox.Show(demo.ExceptionInfo.ToString) Else MessageBox.Show($"Data: [{demo.Data}]") End If MessageBox.Show("Done - inspect Task Manager processes, there will be no instances of Excel")




Reply With Quote
