VB.NET Updating Excel

I would like to have a simple program which would update a spreadsheet from a simple VB.NET GUI.

I would like to submit a value from my VB.NET GUI, to the spreadsheet. THe spreadsheet would then recalculate any associated fields/records.

SO A B C
2 2 =sum(A+B) 4

Updated column A via VB.NET and then C would be recalculated by teh spreadsheet to = 5.

to A B C
3 2 5

Not the most technically demanding challenge ever, but I have not programmed for a while and am starting off again! So if anyone could help me with an example of the source code to update Excel Spreadsheets that would be most appreciated. Thanks a lot. :wave:

First, include 'Excel 9.0' in your References. Then...

'Declare objects

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

xlApp = CreateObject("Excel.Application")

'Open file

xlBook = xlApp.Workbooks.Open(spreadsheetfilename)
xlSheet = xlBook.Worksheets(worksheetnumber)

'Then you can refer to any cell using...

myvalue = xlSheet.Cells(rowindex, columnindex).Value

'...and read/write cells like that

'If you want to save changes to the workbook use:

xlBook.Save()

'When you've finished:

xlApp.Quit()

Here is an example on how to write to the cells and also enter a formula. Then update the cell with "3" and recalculate the formula.

If your running Excel 2000 or earlier then you can not use the .Interop. ;)

VB Code:

'Excel 2002 (XP) or 2003
Option Explicit On 
Option Strict On
'Add a reference to MS Excel xx.0 Object Library
Imports Microsoft.Office.Interop
 
Public Class Form1
 
    Inherits System.Windows.Forms.Form
 
#Region " Windows Form Designer generated code "
 
    Public Sub New()
        MyBase.New()
 
        'This call is required by the Windows Form Designer.
        InitializeComponent()
 
        'Add any initialization after the InitializeComponent() call
 
    End Sub
 
    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(192, 88)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Button1"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(284, 122)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)
 
    End Sub
 
#End Region
 
    Private moApp As Excel.Application
 
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        moApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)
        moApp.Visible = False
    End Sub
 
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If Not moApp Is Nothing Then
            moApp.Quit()
        End If
        moApp = Nothing
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oWB As Excel.Workbook = moApp.Workbooks.Add
        Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Sheet1"), Excel.Worksheet)
        oSht.Cells(1, 1) = "2"
        oSht.Cells(1, 2) = "2"
        oSht.Cells(1, 3) = "=SUM(A1, B1)"
        moApp.Visible = True
        MessageBox.Show("Before Calculate()")
        oSht.Cells(1, 1) = "3"
        oSht.Calculate()
        MessageBox.Show("After Calculate()")
        oWB.Close(True, "C:\Test.xls")
        oSht = Nothing
        oWB = Nothing
    End Sub
 
End Class