Click here to Skip to main content
11,797,355 members (80,112 online)
Click here to Skip to main content

Reading and writing an Excel file using VB.NET

, 20 Mar 2007 CPOL 243.1K 2.6K 47
Rate this:
Please Sign up or sign in to vote.
This code helps the user to interact with an Excel file using the OleDBDataProvider in VB.NET.

Screenshot - ExcelSheet.jpg


This articles helps user to Insert, Update, Delete, and Select data in Excel files using the OLEDBDataProvider in VB.NET.

Here is the connection string to connect with Excel using OleDBDataProvider:

Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

Here is the code on the button click event to select and insert data in an Excel file:

Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
    Dim pram As OleDbParameter
    Dim dr As DataRow
    Dim olecon As OleDbConnection
    Dim olecomm As OleDbCommand
    Dim olecomm1 As OleDbCommand
    Dim oleadpt As OleDbDataAdapter
    Dim ds As DataSet
        olecon = New OleDbConnection
        olecon.ConnectionString = connstring
        olecomm = New OleDbCommand
        olecomm.CommandText = _
           "Select FirstName, LastName, Age, Phone from [Sheet1$]"
        olecomm.Connection = olecon
        olecomm1 = New OleDbCommand
        olecomm1.CommandText = "Insert into [Sheet1$] " & _
            "(FirstName, LastName, Age, Phone) values " & _
            "(@FName, @LName, @Age, @Phone)"
        olecomm1.Connection = olecon
        pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
        pram.SourceColumn = "FirstName"
        pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
        pram.SourceColumn = "LastName"
        pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
        pram.SourceColumn = "Age"
        pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
        pram.SourceColumn = "Phone"
        oleadpt = New OleDbDataAdapter(olecomm)
        ds = New DataSet
        oleadpt.Fill(ds, "Sheet1")
        If IsNothing(ds) = False Then
            dr = ds.Tables(0).NewRow
            dr("FirstName") = "Raman"
            dr("LastName") = "Tayal"
            dr("Age") = 24
            dr("Phone") = 98989898
            oleadpt = New OleDbDataAdapter
            oleadpt.InsertCommand = olecomm1
            Dim i As Integer = oleadpt.Update(ds, "Sheet1")
            MessageBox.Show(i & " row affected")
        End If
    Catch ex As Exception
        olecon = Nothing
        olecomm = Nothing
        oleadpt = Nothing
        ds = Nothing
        dr = Nothing
        pram = Nothing
    End Try
End Sub


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


About the Author

Raman Tayal
Software Developer (Senior)
India India
No Biography provided

You may also be interested in...

Comments and Discussions

Questionempty rows cells after importing data from excel 2000 Pin
Makumbisula16-Feb-15 19:40
memberMakumbisula16-Feb-15 19:40 
Questionhow to jump on 1st cell to 10th cell Pin
Member 1111752829-Sep-14 1:08
memberMember 1111752829-Sep-14 1:08 
QuestionThanks for detailed Pin
Mike Earl17-Jan-14 4:08
memberMike Earl17-Jan-14 4:08 
Questionwrite data from one source excel file to destination excel file Pin
sanjivkp774-Dec-13 4:51
membersanjivkp774-Dec-13 4:51 
GeneralThanks a Lot brother Pin
ravisankarvattikuti14-Mar-10 5:47
memberravisankarvattikuti14-Mar-10 5:47 
GeneralRe: Thanks a Lot brother Pin
Raman Tayal15-Mar-10 6:54
memberRaman Tayal15-Mar-10 6:54 
Generalthanks Pin
bhavin123014-Sep-09 18:13
memberbhavin123014-Sep-09 18:13 
GeneralPlease clarify my question( urgent!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ) Pin
catchthedream31-Aug-09 1:43
membercatchthedream31-Aug-09 1:43 
QuestionPK??? Pin
GlobalUni22-Jul-09 22:10
memberGlobalUni22-Jul-09 22:10 
AnswerRe: PK??? Pin
Raman Tayal23-Jul-09 7:18
memberRaman Tayal23-Jul-09 7:18 

In this I am loading the data into the dataset when reading the values from Excel file. Datatable in dataset supports the primary key contraints enforncement on the dataset which restrict the duplicate value to be insert in dataset. This will helps you to achive the functionality. But in this you need to check whether the data loaded in dataset should be unique otherwise you will get an exception while puting the primary key constraints.

Or you can also predefine the datatable in dataset and put the primary key contraint on respective column and then load the data from the excel but you must ensure that the excel has not been tempered by the user manually. In case user has manually instert the duplicate row in excel then you will get an exception while reading the value from excel.

You can refer to the below link to create primary key constraints in datatable.[^]

Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: -

Questionmaking dictionary Pin
thaileang25-Jun-09 8:31
memberthaileang25-Jun-09 8:31 
QuestionExcel Upload Pin
WendellB4-May-09 8:47
memberWendellB4-May-09 8:47 
AnswerRe: Excel Upload Pin
gg42379-Nov-09 23:19
membergg42379-Nov-09 23:19 
GeneralUpdate a shared excell file [modified] Pin
facu20yo19-Apr-09 3:57
memberfacu20yo19-Apr-09 3:57 
Questionneed help...urgent! Pin
Member 430050816-Mar-09 18:11
memberMember 430050816-Mar-09 18:11 
AnswerRe: need help...urgent! Pin
Raman Tayal17-Mar-09 20:28
memberRaman Tayal17-Mar-09 20:28 
GeneralRe: need help...urgent! Pin
Member 430050820-Mar-09 19:13
memberMember 430050820-Mar-09 19:13 
GeneralRe: need help...urgent! Pin
gg423710-Aug-09 13:07
membergg423710-Aug-09 13:07 
Generalhelp Pin
pjacquez6816-Feb-09 7:06
memberpjacquez6816-Feb-09 7:06 
GeneralRe: help Pin
Raman Tayal16-Feb-09 20:50
memberRaman Tayal16-Feb-09 20:50 
Generali need your help bro, Pin
pjacquez6816-Feb-09 6:56
memberpjacquez6816-Feb-09 6:56 
Generaldelete Pin
pjacquez6816-Feb-09 4:15
memberpjacquez6816-Feb-09 4:15 
GeneralFrom Jacquez Pin
pjacquez6816-Feb-09 3:18
memberpjacquez6816-Feb-09 3:18 
GeneralGreat more code being posted THAT DOESN'T WORK OUT OF THE BOX. Pin
Member 389560127-Jan-09 14:00
memberMember 389560127-Jan-09 14:00 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. Pin
Raman Tayal28-Jan-09 5:04
memberRaman Tayal28-Jan-09 5:04 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. Pin
Member 389560128-Jan-09 5:28
memberMember 389560128-Jan-09 5:28 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. Pin
Raman Tayal28-Jan-09 18:14
memberRaman Tayal28-Jan-09 18:14 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. Pin
Alejandro Marín20-Oct-09 6:42
memberAlejandro Marín20-Oct-09 6:42 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. Pin
Raman Tayal21-Oct-09 7:47
memberRaman Tayal21-Oct-09 7:47 
GeneralMy vote of 1 Pin
Member 389560127-Jan-09 13:59
memberMember 389560127-Jan-09 13:59 
Questionit takes much time to insert nearly 2000 rows from dataset to Excel. Pin
Uemsh19-Jun-08 4:59
memberUemsh19-Jun-08 4:59 
AnswerRe: it takes much time to insert nearly 2000 rows from dataset to Excel. Pin
Raman Tayal23-Jun-08 19:35
memberRaman Tayal23-Jun-08 19:35 
GeneralRe: it takes much time to insert nearly 2000 rows from dataset to Excel. Pin
Uemsh8-Jul-08 11:44
memberUemsh8-Jul-08 11:44 
AnswerRe: it takes much time to insert nearly 2000 rows from dataset to Excel. Pin
gg42375-Nov-09 1:03
membergg42375-Nov-09 1:03 
Questionhow can I insert these dataset into my databse Pin
lama819858-May-08 2:50
memberlama819858-May-08 2:50 
GeneralRead Excel (xls) file which is present on internet (by using VB.Net) Pin
kirangh5-May-08 21:45
memberkirangh5-May-08 21:45 
GeneralRe: Read Excel (xls) file which is present on internet (by using VB.Net) Pin
SCL_896-Aug-09 22:11
memberSCL_896-Aug-09 22:11 
GeneralThird party components Pin
BarbaMariolino28-Apr-08 4:04
memberBarbaMariolino28-Apr-08 4:04 
QuestionError on reading excel file Pin
neelu777930-Oct-07 23:11
memberneelu777930-Oct-07 23:11 
AnswerRe: Error on reading excel file Pin
Raman Tayal31-Oct-07 18:03
memberRaman Tayal31-Oct-07 18:03 
GeneralRe: Error on reading excel file [modified] Pin
neelu77791-Nov-07 7:01
memberneelu77791-Nov-07 7:01 
Generalhelp me Pin
slayer_stb30-Oct-07 18:24
memberslayer_stb30-Oct-07 18:24 
GeneralRe: help me Pin
Raman Tayal31-Oct-07 18:34
memberRaman Tayal31-Oct-07 18:34 
GeneralRe: help me Pin
Mick_wijaya30-Jul-08 21:50
memberMick_wijaya30-Jul-08 21:50 
Generalpassword protected excel Pin
kenchua525-Jul-07 20:33
memberkenchua525-Jul-07 20:33 
GeneralRe: password protected excel Pin
privacy space29-Feb-08 3:04
memberprivacy space29-Feb-08 3:04 
GeneralDelete and update Pin
amu rath8-May-07 0:48
memberamu rath8-May-07 0:48 
GeneralAbout Excel Pin
gangireddyh@gmail.com12-Apr-07 2:22
membergangireddyh@gmail.com12-Apr-07 2:22 
GeneralRe: About Excel Pin
Raman Tayal12-Apr-07 6:06
memberRaman Tayal12-Apr-07 6:06 
GeneralRe: About Excel Pin
B_u_n_d_y5-Mar-08 8:51
memberB_u_n_d_y5-Mar-08 8:51 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.151002.1 | Last Updated 20 Mar 2007
Article Copyright 2007 by Raman Tayal
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid