Viewing Excel worksheet in Webbrowser

[jwmaiden]

Have an application that opens up a webbrowser control to display data in an Excel spreadsheet (using VB.NET in VS 2005). No problem with opening the spreadsheet or displaying the data, but I'm having problems with repeating the process. I want to create an Excel spreadsheet, add some data to it, and display the results in a webbrowser control. I want to be able to then later delete the old Excel spreadsheet, create a new one, add some new data, and navigate the webbrowser to the new file. The errors I'm getting seem to imply that the webbrowser is not releasing the old Excel spreadsheet properly before I delete it and create a new one. Please take a look at the code below and tell me what I'm doing wrong.

  Private Sub GraphSpreadButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GraphSpreadButton.Click
 
        Dim ExcelApp As Microsoft.Office.Interop.Excel.Application = _
                           New Microsoft.Office.Interop.Excel.Application()
 
        Try
 
                  Dim sFileName As String = System.AppDomain.CurrentDomain.BaseDirectory & "SpreadGraph.xls"
            Dim fFile As New FileInfo(sFileName)
 
            Dim ExcelWorkbook As Microsoft.Office.Interop.Excel.Workbook
            Dim spreadChart As Microsoft.Office.Interop.Excel.Chart
            Dim spreadData As Microsoft.Office.Interop.Excel.Worksheet
 
' if the file exists, delete it so we can start fresh
            If fFile.Exists Then
                ExcelWorkbook = ExcelApp.Workbooks.Open(sFileName)
                ExcelWorkbook.Save()
                ExcelWorkbook.Close()
                fFile.Delete()
            End If
 
' create a new workbook
            ExcelWorkbook = ExcelApp.Workbooks.Add()
 
' remove two sheets and add a chart
            Dim removeSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(ExcelWorkbook.Sheets.Item(3), Microsoft.Office.Interop.Excel.Worksheet)
            removeSheet.Delete()
            removeSheet = CType(ExcelWorkbook.Sheets.Item(2), Microsoft.Office.Interop.Excel.Worksheet)
            removeSheet.Delete()
            spreadData = CType(ExcelWorkbook.Sheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
            spreadData.Name = "Data"
 
            spreadChart = CType(ExcelWorkbook.Charts.Add(), Microsoft.Office.Interop.Excel.Chart)
            spreadChart.Name = "Surface"
 
' this is where code to add data to sheet would be
 
' display data in a surface chart
             spreadChart.SetSourceData(spreadData.Range("A1", "U7"))
 
            spreadChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlSurface
 
            spreadData.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible
            spreadChart.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible
 
' save new workbook
            ExcelWorkbook.SaveAs(sFileName)
            ExcelWorkbook.Close()
            ExcelApp.Quit()
 
            SpreadExcelBrowse.Navigate(sFileName)
 
        Catch ex As Exception
            MsgBox(ex.ToString)
            ExcelApp.Quit()
        End Try
 
    End Sub