My Blog

Writing some text to Excel File

In continuation from here

Now let’s try writing something to the Excel File. In order to do so, we need to first define the Excel worksheet object so that we can interact with it. Once that is done will will write to column A and Col B. Let’s say the data that we want to put in Excel is as follows

In the code below I will be creating a new file and then use code to populate the above data in Excel. So obviously the code goes in the first button “Create”. If you choose to, you can also put the data in an existing workbook using the 2nd button “Open”

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January"
            .Range("A3").Value = "February"
            .Range("A4").Value = "March"
            .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"
        End With
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:TutorialSample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    End Sub
End Class

Now when you run the project, your form will launch and when you click on “Create” button, Excel will launch and a new workbook will be created. You will see the data in the Excel Sheet. See image below.