Adding row with data into existing excel file

[kartikguha]

I am working on a project in which i do some calculations for products and produce some end results then browse an excel file containing reference value for the products, but now i require that if a particular product is not found, that product should be automatically added to the reference file so that end user need to fill only the reference value (not via tool but offline)

The reference file contains multiple sheets for various testing scenarious, when testing for one scenario is done, only te corresponding sheet should be updated.

I am stuck and dont know how to proceed. (I am newbie in vb.net and this is my first project)

[Reverend Jim]

You need to include the following

Imports Microsoft.Office.Interop

Then you create a few objects

'create objects to interface to Excel

Dim xls As New Excel.Application
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet

You can open the Excel file by (use your own filename)

xls.Workbooks.Open("D:\temp\myfile.xls")

'get references to first workbook and worksheet

book = xls.ActiveWorkbook
sheet = book.ActiveSheet

You can access the rows and columns by

sheet.Cells(row, col) = 23   'or whatever value you want

'save the workbook and clean up

book.Save()
xls.Workbooks.Close()
xls.Quit()

releaseObject(sheet)
releaseObject(book)
releaseObject(xls)

Add the following Sub for cleanup.

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try

End Sub

You may wonder why I create a variable to reference xls.ActiveWorkbook when I could just as well do

sheet = xls.ActiveWorkbook.ActiveSheet

The short answer is that if you do it as in the above line you will end up with Excel.exe tasks cluttering up your system (one for every time you run the app). I don't fully understand the reasoning. Trust me though.

[kartikguha]

thanks reverend jim, I will try dis out :)