My Blog

Creating a New Excel File and Adding Data using ACE

In continuation from here

Click on File and click on New Project. Under Visual basic, Select Windows and then select Windows Form Application. Give the project an appropriate name and select a location where you want to save this project.

When the project opens you will be presented with Form1. For the time being just lets create 2 buttons and name then Create and Cancel. Give an appropriate heading to the form. Your form may look like this.

And now we are ready to code.

Creating our connection string

To create a new file we simply provide the name of a non existent file in the connection string. This will create a new file.

Private Const connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

Deciding what kind of data do we want to write to the Excel File

Let’s say, we want the following fields in the Excel file.

Sno, Employee_Name, Company, Date_Of_joining, Stipend, Stocks_Held

If you notice, I have taken fields of different data type. Sno is of INT Type, Employee Name and Company is of VARCHAR Type, Date Of joining is of DATE Type, Stipend is DECIMAL Type and Stocks Held is of PERCENTAGE Type.

Now Excel has no PERCENTAGE type for fields. It has a percentage FORMATTING for NUMERIC fields and hence we will store the value of Stocks Held in DECIMAL Type. So For example 1% = 0.01

Command for writing data to the Excel File

To create a table in an Excel workbook, run the CREATE TABLE command. The syntax of the command is

CREATE TABLE Sheet1 (F1 char(255), F2 char(255))

Sheet1 is the name of the worksheet in Excel. F1,F2 etc is the Field and char(255) is the Data Type with the length that you would like to specify. This is optional. When you run this command, a new worksheet is created with the name of the table you specify in the command. Note that if the workbook for the connection does exist then it will give you an error. You may want to check for the existence of the file first and delete it before creating a new one?

This is how our final code will look like

Imports System.Data.OleDb
Imports System.Data

Public Class Form1

    '~~> Create Button
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim olecon As OleDbConnection = New OleDbConnection
        Dim olecmd As OleDbCommand = New OleDbCommand
        Dim FilePath As String = "C:\Temp\"
        Dim FileName As String = "EmployeeDatabase.xlsx"

        '~~> Construct your connection string
        Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                   "Data Source=" & FilePath & FileName & ";" &
                                   "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

        olecon.ConnectionString = connstring

        olecmd.Connection = olecon

        '~~> Command to create the table
        olecmd.CommandText = "CREATE TABLE Sheet1 (Sno Int, " &
                                                   "Employee_Name VARCHAR, " &
                                                   "Company VARCHAR, " &
                                                   "Date_Of_joining DATE, " &
                                                   "Stipend DECIMAL, " &
                                                   "Stocks_Held DECIMAL)"

        '~~> Adding Data
        olecmd.CommandText = "INSERT INTO Sheet1 (Sno, Employee_Name, Company,Date_Of_joining,Stipend,Stocks_Held) values " &
                              "('1', 'Siddharth Rout', 'Defining Horizons', '20/7/2014','2000.75','0.01')"

        '~~> Close the connection

        '~~> Inform User
        MessageBox.Show("The file " & FileName & " has been created successfully and has been placed in " & FilePath)
    End Sub
    '~~> Close Button
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    End Sub
End Class

Now you are ready to run your project.

When executed, the program will create an Excel File as shown below.