Click here to Skip to main content
13,195,379 members (61,452 online)
Click here to Skip to main content
Add your own
alternative version


21 bookmarked
Posted 17 Oct 2013

How to Export Data to Excel in VB.NET

, 17 Oct 2013
Rate this:
Please Sign up or sign in to vote.
I want to show how we can export data from data table to Excel simply.


There are many DLL files that help you to export data to Excel but always there are some problems with them that causes error or you should pay some money to use them with all features.

I want to show that how we can use Microsoft Office original DLL file in order to export data to standard Excel file. In this tip, we use Microsoft.Office.Interop.Excel.dll that is in your PC when you install Microsoft Office.


I use Microsoft.Office.Interop.Excel namespace. The way I use is very simple. If you want to learn more about it, you can see Microsoft.Office.Interop.Excel namespace page in Microsoft website.

Using the Code

This is all the code you need to do in your form. First of all, you need to declare references of objects we want to use. The important thing is about using Microsoft.Office.Interop.Excel.dll. You should first install the latest version of Microsoft Office, then set the reference to the Microsoft.Office.Interop.Excel.

There are two zip files that you can download and run. If you have any favorite query, use it. But if you don't have any database or query, don't worry you can download and run it in SQL Server. After that, you can extract the source and run it.

'References that we need
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO.Directory
Imports Microsoft.Office.Interop.Excel 'Before you add this reference to your project,
' you need to install Microsoft Office and find last version of this file.
Imports Microsoft.Office.Interop
Public Class Form1
    Private Sub btnBrowse_Click(sender As System.Object, _
    	e As System.EventArgs) Handles btnBrowse.Click
        'Initialize the objects before use
        Dim dataAdapter As New SqlClient.SqlDataAdapter()
        Dim dataSet As New DataSet
        Dim command As New SqlClient.SqlCommand
        Dim datatableMain As New System.Data.DataTable()
        Dim connection As New SqlClient.SqlConnection
        'Assign your connection string to connection object
        connection.ConnectionString = "Data Source=.;_
        Initial Catalog=pubs;Integrated Security=True"
        command.Connection = connection
        command.CommandType = CommandType.Text
        'You can use any command select
        command.CommandText = "Select * from Authors"
        dataAdapter.SelectCommand = command

        Dim f As FolderBrowserDialog = New FolderBrowserDialog
            If f.ShowDialog() = DialogResult.OK Then
                'This section help you if your language is not English.
                System.Threading.Thread.CurrentThread.CurrentCulture = _
                Dim oExcel As Excel.Application
                Dim oBook As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                oExcel = CreateObject("Excel.Application")
                oBook = oExcel.Workbooks.Add(Type.Missing)
                oSheet = oBook.Worksheets(1)
                Dim dc As System.Data.DataColumn
                Dim dr As System.Data.DataRow
                Dim colIndex As Integer = 0
                Dim rowIndex As Integer = 0
                'Fill data to datatable

                'Export the Columns to excel file
                For Each dc In datatableMain.Columns
                    colIndex = colIndex + 1
                    oSheet.Cells(1, colIndex) = dc.ColumnName
                'Export the rows to excel file
                For Each dr In datatableMain.Rows
                    rowIndex = rowIndex + 1
                    colIndex = 0
                    For Each dc In datatableMain.Columns
                        colIndex = colIndex + 1
                        oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                'Set final path
                Dim fileName As String = "\ExportedAuthors" + ".xls"
                Dim finalPath = f.SelectedPath + fileName
                txtPath.Text = finalPath
                'Save file in final path
                oBook.SaveAs(finalPath, XlFileFormat.xlWorkbookNormal, Type.Missing, _
                Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
                'Release the objects
                oBook.Close(False, Type.Missing, Type.Missing)
                'Some time Office application does not quit after automation: 
                'so i am calling GC.Collect method.
                MessageBox.Show("Export done successfully!")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)
        End Try
    End Sub
    Private Sub ReleaseObject(ByVal o As Object)
            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
            End While
            o = Nothing
        End Try
    End Sub
End Class


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Alireza C
Software Developer (Senior)
Iran (Islamic Republic of) Iran (Islamic Republic of)
I like programming because when you design a software you create a thing that improve your life.

You may also be interested in...


Comments and Discussions

Questionerror Pin
Member 1308726220-Jul-17 0:08
memberMember 1308726220-Jul-17 0:08 
QuestionFormat Column to Be Text Pin
Member 120304715-Jan-17 0:38
memberMember 120304715-Jan-17 0:38 
QuestionFormatting header cells in sheet Pin
MayurMuley16-Jul-15 5:59
memberMayurMuley16-Jul-15 5:59 
QuestionSimple and straight forward very easy to Follow Pin
Member 114926494-May-15 6:18
memberMember 114926494-May-15 6:18 
QuestionWhat about this DLL? Pin
Dietmar Schoder28-Feb-15 3:35
professionalDietmar Schoder28-Feb-15 3:35 
Questionhow to format particular column to number Pin
Nirmala Saravanan14-Oct-14 3:04
memberNirmala Saravanan14-Oct-14 3:04 
GeneralThanks Pin
Abderrazzak Zahir1-Aug-14 8:35
memberAbderrazzak Zahir1-Aug-14 8:35 
QuestionUnable to cast object of type 'System.Data.DataTable' to type 'Microsoft.Office.Interop.Excel.DataTable'. Pin
mitchiee12267-Jul-14 19:26
membermitchiee12267-Jul-14 19:26 
AnswerRe: Unable to cast object of type 'System.Data.DataTable' to type 'Microsoft.Office.Interop.Excel.DataTable'. Pin
Alireza Chegini10-Jul-14 0:47
memberAlireza Chegini10-Jul-14 0:47 
Questionextended query Pin
p gosney15-May-14 5:53
memberp gosney15-May-14 5:53 
AnswerRe: extended query Pin
Alireza Chegini10-Jul-14 0:49
memberAlireza Chegini10-Jul-14 0:49 
QuestionCannot create ActiveX component Pin
albchinsh8-Mar-14 22:42
memberalbchinsh8-Mar-14 22:42 
Questionexcel export Pin
Member 1048023428-Feb-14 22:47
memberMember 1048023428-Feb-14 22:47 
AnswerRe: excel export Pin
Alireza Chegini1-Mar-14 0:13
memberAlireza Chegini1-Mar-14 0:13 
QuestionVariables i and j?? Pin
moordoom14-Nov-13 3:17
membermoordoom14-Nov-13 3:17 
AnswerRe: Variables i and j?? Pin
Alireza Chegini16-Nov-13 7:09
memberAlireza Chegini16-Nov-13 7:09 
GeneralMy vote of 4 Pin
Klaus Luedenscheidt17-Oct-13 18:53
memberKlaus Luedenscheidt17-Oct-13 18:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171019.1 | Last Updated 17 Oct 2013
Article Copyright 2013 by Alireza C
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid