C
Crashnburn5590
I am trying to open an Excel spreadsheet in VB 2008 using automation. Once
the spreadsheet is opened, I want to modify it, and force a recalculation.
The auto calc function is disabled.
I can opene the spreadsheet and modify it successfully. When I try to get it
to recalculate, all the cells with formulas show this: #Name? Thanks for your
help.
My sample code follows:
Imports Excel = Microsoft.Office.Interop.Excel
Public Class frmExcel
Dim objExcel As Excel.Application
Dim objBook As Excel._Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel._Worksheet
Dim strText As String = "C:\Link Budget Calculator\JitBudTxEyeMask
Baseline 091222t.xls"
Dim strTab As String = "BaseA"
Dim strDataRateCellAddr As String = "C4"
Dim strValue As String = "10313.5"
Private Sub btnOpenSpreadsheet_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnOpenSpreadsheet.Click
If objExcel Is Nothing Then
objExcel = New Excel.Application
objBooks = objExcel.Workbooks
objBook = objBooks.Open(Filename:=strText)
objExcel.Visible = True
objSheets = objBook.Worksheets
objSheet = objSheets(strTab)
End If
End Sub
Private Sub btnQuit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnQuit.Click
btnClose_Click(sender, e)
Me.Close()
End Sub
Private Sub btnCalculate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCalculate.Click
If Not (objExcel Is Nothing) Then
'objExcel.Calculate()'Each gives the same error
objSheet.Calculate()
End If
End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnClose.Click
If Not (objExcel Is Nothing) Then
objBook.Close()
objExcel.Quit()
releaseObject(objSheet)
releaseObject(objSheets)
releaseObject(objBook)
releaseObject(objBooks)
releaseObject(objExcel)
End If
End Sub
Private Sub btnModify_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnModify.Click
If Not (objSheet Is Nothing) Then
Try
objSheet.Range(strDataRateCellAddr).Value = strValue
Catch ex As Exception
End Try
End If
End Sub
Private Sub releaseObject(ByRef obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Catch ex As Exception
Finally
obj = Nothing
GC.Collect()
End Try
End Sub
End Class
the spreadsheet is opened, I want to modify it, and force a recalculation.
The auto calc function is disabled.
I can opene the spreadsheet and modify it successfully. When I try to get it
to recalculate, all the cells with formulas show this: #Name? Thanks for your
help.
My sample code follows:
Imports Excel = Microsoft.Office.Interop.Excel
Public Class frmExcel
Dim objExcel As Excel.Application
Dim objBook As Excel._Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel._Worksheet
Dim strText As String = "C:\Link Budget Calculator\JitBudTxEyeMask
Baseline 091222t.xls"
Dim strTab As String = "BaseA"
Dim strDataRateCellAddr As String = "C4"
Dim strValue As String = "10313.5"
Private Sub btnOpenSpreadsheet_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnOpenSpreadsheet.Click
If objExcel Is Nothing Then
objExcel = New Excel.Application
objBooks = objExcel.Workbooks
objBook = objBooks.Open(Filename:=strText)
objExcel.Visible = True
objSheets = objBook.Worksheets
objSheet = objSheets(strTab)
End If
End Sub
Private Sub btnQuit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnQuit.Click
btnClose_Click(sender, e)
Me.Close()
End Sub
Private Sub btnCalculate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCalculate.Click
If Not (objExcel Is Nothing) Then
'objExcel.Calculate()'Each gives the same error
objSheet.Calculate()
End If
End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnClose.Click
If Not (objExcel Is Nothing) Then
objBook.Close()
objExcel.Quit()
releaseObject(objSheet)
releaseObject(objSheets)
releaseObject(objBook)
releaseObject(objBooks)
releaseObject(objExcel)
End If
End Sub
Private Sub btnModify_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnModify.Click
If Not (objSheet Is Nothing) Then
Try
objSheet.Range(strDataRateCellAddr).Value = strValue
Catch ex As Exception
End Try
End If
End Sub
Private Sub releaseObject(ByRef obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Catch ex As Exception
Finally
obj = Nothing
GC.Collect()
End Try
End Sub
End Class