In continuation from here…
We already covered here how to create the form, set references and place the required buttons. Now we will create a blank workbook and write some text to it.
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 wish, 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(sender As Object, e As 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 Me.Close() 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 that the data is populated in the Excel Sheet. See image below.