How to create Excel file in VB.Net

How to create a new worksheet in Excel file

Automation to Excel allows you to perform actions such as creating a new workbook, adding data to the workbook, or creating charts etc. The following VB.Net code example shows how to use COM interop to create an Excel file. Before going to create new Excel file programmatically in VB.Net, you must have Excel installed on your system for this code to run properly.

Excel Library

In order to access the object model from Visual VB.NET, you have to add the Microsoft Excel 12.0 Object Library to your current project.

Create a new project and add a Command Button to your VB.Net Form.

How to use COM Interop to Create an Excel Spreadsheet

Form the following images you can find how to add Excel reference library in your VB.Net project.

Select reference dialogue from Project menu.

excel_reference.jpg excel_library.jpg

How to create an Excel Document Programmatically

First we have to initialize the Excel application Object in your VB.Net application.

Before creating new Excel Workbook, you should check whether Excel is installed in your system.

Then create new Workbook

After creating the new Workbook, next step is to write content to worksheet

In the above code we write the data in the Sheet1, If you want to write data in sheet 2 then you should code like this.

Save Excel file (SaveAs() method)t

After write the content to the cell, next step is to save the excel file in your system.

How to properly clean up Excel interop objects

How to create an Excel Document in VB.Net , How to properly release Excel COM objects VB.Net

Finally, we have to properly clean up Excel interop objects or release Excel COM objects. Here we write a function to clean up the Excel Application, Excel Workbook and Excel Worksheet Objects.

Creating an Excel Spreadsheet Programmatically

Copy and paste the following source code in your VB.Net project file

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()

        If xlApp Is Nothing Then
            MessageBox.Show("Excel is not properly installed!!")
        End If

        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.Cells(1, 1) = "Sheet 1 content"

        xlWorkBook.SaveAs("d:\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _
         Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
        xlWorkBook.Close(True, misValue, misValue)


        MessageBox.Show("Excel file created , you can find the file d:\csharp-Excel.xls")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Class (C) 2021    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.
SiteMap  | Terms  | About