Sunday, June 17, 2007

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

1 comment:

Marwan Nader said...

Thanks for this!

Newbie question: what is the point of specifying System.Reflection.Missing.Value in place of "Cell2 As Object"? If I delete it completely, everything still works.