Click here to Skip to main content
Click here to Skip to main content
Go to top

Reading and writing an Excel file using VB.NET

, 20 Mar 2007
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

Introduction

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
    Try
        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
        olecon.Open()
        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
            ds.Tables(0).Rows.Add(dr)
            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
        MessageBox.Show(ex.Message)
    Finally
        olecon.Close()
        olecon = Nothing
        olecomm = Nothing
        oleadpt = Nothing
        ds = Nothing
        dr = Nothing
        pram = Nothing
    End Try
End Sub

License

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

Share

About the Author

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

Comments and Discussions

 
QuestionThanks for detailed PinmemberMike Earl17-Jan-14 4:08 
Questionwrite data from one source excel file to destination excel file Pinmembersanjivkp774-Dec-13 4:51 
GeneralThanks a Lot brother Pinmemberravisankarvattikuti14-Mar-10 5:47 
GeneralRe: Thanks a Lot brother PinmemberRaman Tayal15-Mar-10 6:54 
Generalthanks Pinmemberbhavin123014-Sep-09 18:13 
GeneralPlease clarify my question( urgent!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ) Pinmembercatchthedream31-Aug-09 1:43 
QuestionPK??? PinmemberGlobalUni22-Jul-09 22:10 
AnswerRe: PK??? PinmemberRaman Tayal23-Jul-09 7:18 
Questionmaking dictionary Pinmemberthaileang25-Jun-09 8:31 
QuestionExcel Upload PinmemberWendellB4-May-09 8:47 
AnswerRe: Excel Upload Pinmembergg42379-Nov-09 23:19 
GeneralUpdate a shared excell file [modified] Pinmemberfacu20yo19-Apr-09 3:57 
Questionneed help...urgent! PinmemberMember 430050816-Mar-09 18:11 
AnswerRe: need help...urgent! PinmemberRaman Tayal17-Mar-09 20:28 
GeneralRe: need help...urgent! PinmemberMember 430050820-Mar-09 19:13 
GeneralRe: need help...urgent! Pinmembergg423710-Aug-09 13:07 
Generalhelp Pinmemberpjacquez6816-Feb-09 7:06 
by the way bro, im Paul Jacquez, 2nd year student SSCT Surigao City, Philippines. i want studying vb.net bro because i want a programmer someday bro, plz help me bro. i want codes edit,delete and search with use of excel as database bro. plz help me bro.?
 
Thank you bro. God Bless U.
GeneralRe: help PinmemberRaman Tayal16-Feb-09 20:50 
Generali need your help bro, Pinmemberpjacquez6816-Feb-09 6:56 
Generaldelete Pinmemberpjacquez6816-Feb-09 4:15 
GeneralFrom Jacquez Pinmemberpjacquez6816-Feb-09 3:18 
GeneralGreat more code being posted THAT DOESN'T WORK OUT OF THE BOX. PinmemberMember 389560127-Jan-09 14:00 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. PinmemberRaman Tayal28-Jan-09 5:04 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. PinmemberMember 389560128-Jan-09 5:28 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. PinmemberRaman Tayal28-Jan-09 18:14 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. PinmemberAlejandro Marín20-Oct-09 6:42 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. PinmemberRaman Tayal21-Oct-09 7:47 
GeneralMy vote of 1 PinmemberMember 389560127-Jan-09 13:59 
Questionit takes much time to insert nearly 2000 rows from dataset to Excel. PinmemberUemsh19-Jun-08 4:59 
AnswerRe: it takes much time to insert nearly 2000 rows from dataset to Excel. PinmemberRaman Tayal23-Jun-08 19:35 
GeneralRe: it takes much time to insert nearly 2000 rows from dataset to Excel. PinmemberUemsh8-Jul-08 11:44 
AnswerRe: it takes much time to insert nearly 2000 rows from dataset to Excel. Pinmembergg42375-Nov-09 1:03 
Questionhow can I insert these dataset into my databse Pinmemberlama819858-May-08 2:50 
GeneralRead Excel (xls) file which is present on internet (by using VB.Net) Pinmemberkirangh5-May-08 21:45 
GeneralRe: Read Excel (xls) file which is present on internet (by using VB.Net) PinmemberSCL_896-Aug-09 22:11 
GeneralThird party components PinmemberBarbaMariolino28-Apr-08 4:04 
QuestionError on reading excel file Pinmemberneelu777930-Oct-07 23:11 
AnswerRe: Error on reading excel file PinmemberRaman Tayal31-Oct-07 18:03 
GeneralRe: Error on reading excel file [modified] Pinmemberneelu77791-Nov-07 7:01 
Generalhelp me Pinmemberslayer_stb30-Oct-07 18:24 
GeneralRe: help me PinmemberRaman Tayal31-Oct-07 18:34 
GeneralRe: help me PinmemberMick_wijaya30-Jul-08 21:50 
Generalpassword protected excel Pinmemberkenchua525-Jul-07 20:33 
GeneralRe: password protected excel Pinmemberprivacy space29-Feb-08 3:04 
GeneralDelete and update Pinmemberamu rath8-May-07 0:48 
GeneralAbout Excel Pinmembergangireddyh@gmail.com12-Apr-07 2:22 
GeneralRe: About Excel PinmemberRaman Tayal12-Apr-07 6:06 
GeneralRe: About Excel PinmemberB_u_n_d_y5-Mar-08 8:51 
GeneralRe: About Excel PinmemberRaman Tayal7-Mar-08 6:20 
QuestionGetting Error on insert data PinmemberShahPalak4-Apr-07 23:58 

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 | Mobile
Web03 | 2.8.140916.1 | Last Updated 20 Mar 2007
Article Copyright 2007 by Raman Tayal
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid