Excel and Interop not getting along

[habydlg]

I am using Interop to read and write to excel but I'm having some problems writing to excel... When I enter the information I need to be written on the excel in an inputbox, this pops up "COM object that has been separated from its underlying RCW cannot be used."
Heres the Code:

 Private Sub editmodelbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles editmodelbtn.Click
        Dim message, title, defaultValue As String
        message = Enter New Model"
        title = "Editar Modelo"
        defaultValue = ""
        edit = InputBox(message, title, defaultValue)
        If edit = "" Then
            MsgBox("No change Made", MsgBoxStyle.Exclamation)
        Else
            xlWorkBook = xlApp.Workbooks.Open("C:\Users\DF\Desktop\testing.xls")
            xlWorkSheet = xlWorkBook.Sheets("Sheet1")
            xlApp.Visible = False
            xlWorkSheet.Cells(product, 3) = editar
            xlWorkBook.Save()
            xlWorkBook.Close()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        End If

    End Sub

product is a string from a textbox, thats another problem but this error appears on the xlApp.workbook.open. Any ideas?

[habydlg]

anybody?

[kareninstructor]

The following example opens a Excel file, writes to A1 and B1 cells, saves the file. In this example the file exists already in the same folder as the executable. The file is an Excel 2007 file but could be any version (versions below 2007 you need to tweak the SaveAs second parameter).

I've hard coded the values for cells in A1 and B1 but you can easily use values for an InputBox, variable etc.

Important note, there is a lot of code to do the above which ensures that all automation objects are properly released. Generally speaking developers will take the easy way out by a) ignorance b) invoke cleaning up of objects by calling the GC. The code below cleans up all objects properly.

Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Public Class Form1
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      DemoRead(IO.Path.Combine(Application.StartupPath, "Book1.xlsx"))
      MessageBox.Show("Done")
   End Sub
   Private Sub DemoRead(ByVal FileName As String)
      Dim xlApp As Excel.Application = Nothing
      Dim xlWorkBooks As Excel.Workbooks = Nothing
      Dim xlWorkBook As Excel.Workbook = Nothing
      Dim xlWorkSheet As Excel.Worksheet = Nothing

      xlApp = New Excel.Application
      xlApp.DisplayAlerts = False
      xlWorkBooks = xlApp.Workbooks
      xlWorkBook = xlWorkBooks.Open(FileName)
      xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)

      '
      ' Pay attention to the fact I set the range (to A1), set a value
      ' then release the range. Next set the range to B1 but wait until
      ' the Quit method is executed to release the range.
      '
      Dim xlSingleCell = xlWorkSheet.Range("A1")
      xlSingleCell.Value = "1"
      Marshal.FinalReleaseComObject(xlSingleCell)
      xlSingleCell = Nothing

      xlSingleCell = xlWorkSheet.Range("B1")
      xlSingleCell.Value = "2"

      xlWorkBook.SaveAs(FileName, Excel.XlFileFormat.xlOpenXMLWorkbook)

      xlWorkBook.Close()
      xlApp.UserControl = True

      xlApp.Quit()

      If Not xlSingleCell Is Nothing Then
         Marshal.FinalReleaseComObject(xlSingleCell)
         xlSingleCell = Nothing
      End If

      If Not xlWorkSheet Is Nothing Then
         Marshal.FinalReleaseComObject(xlWorkSheet)
         xlWorkSheet = Nothing
      End If

      If Not xlWorkBook Is Nothing Then
         Marshal.FinalReleaseComObject(xlWorkBook)
         xlWorkBook = Nothing
      End If

      If Not xlWorkBooks Is Nothing Then
         Marshal.FinalReleaseComObject(xlWorkBooks)
         xlWorkBooks = Nothing
      End If

      If Not xlApp Is Nothing Then
         Marshal.FinalReleaseComObject(xlApp)
         xlApp = Nothing
      End If
   End Sub
End Class

[habydlg]

Thanks! this solves many inqueries! but I found out that my problem was that I was releasing the objects too soon and where not needed. Thanks!