Reading/writing Excel files in VB.NET

Now there seem to be a few examples of how to do this in C# but few comprehensive ones in VB.NET. So here's how to let the user choose an excel file and then write to cell A1 and read from cell A2. Don't forget to add a reference to the Excel COM library.



Also add Imports Microsoft.Office.Interop to the first line of your class/form

        Dim xcFileInfo As IO.FileInfo
        Dim xcFileDialog As New OpenFileDialog()
        xcFileDialog.Filter = "Excel Spreadsheet Files!*.xls"
        xcFileDialog.Title = "Select estimate in excel spreadsheet file!"

        If xcFileDialog.ShowDialog = DialogResult.OK Then
            xcFileInfo = New IO.FileInfo(xcFileDialog.FileName)
        End If

        Dim myExcel As Excel.Application ' Interface to Excel
        Dim myWorkBookCollection As Excel.Workbooks ' Workbook-collection (note the 's' at the end)
        Dim myWorkBook As Excel.Workbook ' Single Workbook (spreadsheet-collection)
        Dim myWorkSheet As Excel.Worksheet ' Single spreadsheet

        ' Initialize the interface to Excel.exe
        myExcel = New Excel.Application

        If myExcel Is Nothing Then
            MessageBox.Show("Could not load Excel.exe")
            Exit Sub
        End If

        ' initialise access to Excel's workbook collection
        myWorkBookCollection = myExcel.Workbooks

        'open spreadsheet from disk
        myWorkBook = myWorkBookCollection.Open(xcFileInfo.FullName, , False)

        'get 1st sheet from workbook
        myWorkSheet = myWorkBook.Sheets.Item(1)

        'alter contents of 1st cell
        Dim myCell As Object = myWorkSheet.Range("A1", _ System.Reflection.Missing.Value)
        myCell.Value2 = "I did it again!!!"

        'display the spreadsheet
        'myExcel.Visible = True
        'Read Cell A2
        Dim myCell2 As Object = myWorkSheet.Range("A2", _ System.Reflection.Missing.Value)

        Me.Text = myCell.Value2

        'save and get out
        myWorkBook.Save()
        myExcel.Quit()