Excel from VB 2010

[Erik Wiggins]

Okay I had a heck of a time figureing out how to do this. I searched the forums and found lots of questions but no real answers. So after hours of reading through MSDN I have some working code to share with everyone. I put lots of notes in it so that you all will be able to use this to implement in your own programs.

'First you most add the Excel Library Referance Project -> Add Library -> COM -> Microsoft Excel xx.x Object Library -> OK
Imports Microsoft.Office.Interop 'Lets us use the Object in the Excel Library

Module ExcelInterface ' A module to wrap up our interaction with excel
    Private mExcelApp As Excel.Application 'This is the actual excel program
    Public Sub OpenWorkbook(ByVal FileName as String) 'Starts our interface
        mExcelApp = New Excel.Application 'Some stupid line that VB makes us use anytime we use a class that it doesn't know natively
        mExcelApp.Workbooks.Open(FileName) 'Obvious we open our file here
    End Sub
    Public Sub CloseWorkbook() 'Cleans up our mess If you don't do this you will have an instance of excel runnign on your task list you just can't see it.        mExcelApp.ActiveWorkbook.Close(True) 'This is needed so you don't get a do you wanna save message        mExcelApp.Workbooks.Close() 'This closes all workbooks in our application
        mExcelApp = Nothing 'This stops running excell
    End Sub
    'A Couple of ver simple functions Read/Write to/from excel spreadsheet
    Public Function GetCell(ByVal SheetName As String, ByVal Column As Integer, ByVal Row As Integer) As String
        GetCell = mExcelApp.Sheets(SheetName).cells(Column, Row).value
    End Function
    Public Sub SetCell(ByVal SheetName As String, ByVal Column As Integer, ByVal Row As Integer, ByVal value As String)
        mExcelApp.Sheets(SheetName).cells(Column, Row).value = value
    End Sub
End Module

[edolf8]

Hello Erik,

This code is great! The only problem I'm having is when I copy your code into the program, it shows an error on these lines:

Private mExcelApp As Excel.Application

mExcelApp = New Excel.Application

The error says "Type "Excel.Application" is not defined". I made sure that I added the Excel library reference, and I have the import statement. Do you know why this error is occuring? Thanks!

[Erik Wiggins]

Sorry for the late responce but yes error is caused by not having the referance added.

[mohamedbasheerkp]

The problem is with this line "Imports Excel = Microsoft.Office.Interop". It needs excel at the end.
The complete line is "Imports Excel = Microsoft.Office.Interop.Excel"

[mohamedbasheerkp]

And one more problem is there.. The excel instance in the memory is not exiting using
mExcelApp = Nothing command.
Instead use
"mExcelApp.Workbooks.Close()
mExcelApp.Quit() "