Reading and writing an Excel file using VB.NET

Thanks Raman for this educative thread.
I am using Microsoft Office Professional Plus 2016 Excel Version 1903. What will be my provider?
I have used following:

Quote:
"Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=C:\Thesis\Dataset\AccessExcel.xlsx;Extended Properties=""Excel 16.0 Xml;HDR=YES;"""

and the error says

Quote:
Microsoft.ACE.OLEDB.12.0 is not registered in local machine

what will be the syntax for provider of excel service.
Thanks again

Hi members after importing data from excel am getting empty rows where could the problem be am using windows 7 32bit and office 2000 on my computer

VB

Try
           Dim filename As String
           Dim ofd As New OpenFileDialog
           ofd.Title = "Please select the excel which you want to import"
           If ofd.ShowDialog = DialogResult.OK Then
               filename = ofd.FileName
               Dim strin As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filename & ";Extended Properties=""Excel 8.0;HDR=yes"";"
               Dim con As OleDbConnection = New OleDbConnection(strin)
               If con.State = ConnectionState.Closed Then
                   con.Open()
                   Dim command As OleDbCommand = New OleDbCommand()
                   command.CommandText = "select * from [Sheet1$]"
                   command.Connection = con
                   Dim adapter As OleDbDataAdapter = New OleDbDataAdapter()
                   adapter.SelectCommand = command
                   Dim sqlSelect As String = "SELECT * FROM [Sheet1$];"
                   Dim WorkBook As New DataSet
                   Dim ExcelAdapter As System.Data.Common.DataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlSelect, strin)
                   ExcelAdapter.Fill(WorkBook)
                   DataGridView1.DataSource = WorkBook.Tables(0)
                   ' Dim dt As New DataSet
                   ' adapter.Fill(dt, "student")
                   'DataGridView1.DataSource = dt.Tables(0)
                   'DataGridView1.Refresh()
               End If
           Else
               MsgBox("Havn't selected the file,Form Gonna end now")
               Exit Sub
           End If
       Catch ex As Exception
           MsgBox(ex.ToString)
       End Try

In excel inserted some round shape in cell1.how jump that shape from 1st cell to 10th cell ..but that round shape should be in cell1 but that round shape should cjump to cell10.how to do that please give me answer

hello. Thanks for your detailed solution. But i am using a form which have email text box and i am not getting value of special char of @ in excel file. Please if you can help me in that matter it will be good.

VB

Private Const connstringSource As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\CIQExcel\UploadTest.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;"""

Private Const connstringDestination As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\CIQExcel\Argo - Upload Template Design.xlsm;Extended Properties=""Excel 12.0 Macro;HDR=NO;IMEX=1;"""

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim pram As OleDbParameter
    Dim dr As DataRow
    Dim ds As DataSet
    Dim ds1 As DataSet
    Dim olecon As OleDbConnection
    Dim olecon1 As OleDbConnection
    Dim olecomm As OleDbCommand
    Dim olecomm1 As OleDbCommand
    Dim olecomm2 As OleDbCommand
    Dim oleadpt As OleDbDataAdapter
    Dim oleadpt1 As OleDbDataAdapter
    Dim oleadpt2 As OleDbDataAdapter

    Try
        ''open Source file
        olecon = New OleDbConnection
        olecon.ConnectionString = connstringSource
        olecomm = New OleDbCommand
        olecomm.CommandText = "Select f2,f3 from [UploadTest$]"
        olecomm.Connection = olecon
        oleadpt = New OleDbDataAdapter(olecomm)
        ds = New DataSet
        olecon.Open()
        oleadpt.Fill(ds, "UploadTest")

        '' open Destination file
        olecon1 = New OleDbConnection
        olecon1.ConnectionString = connstringDestination
        olecomm1 = New OleDbCommand
        olecomm1.CommandText = "Select * from [Input$]"
        olecomm1.Connection = olecon1
        oleadpt1 = New OleDbDataAdapter(olecomm1)
        ds1 = New DataSet
        olecon1.Open()
        oleadpt1.Fill(ds1, "Input")

        olecomm2 = New OleDbCommand
        olecomm2.CommandText = "Update [Input$] set F2 = @F3"
        olecomm2.Connection = olecon1

        pram = olecomm2.Parameters.Add("@F3", OleDbType.VarChar)
        pram.SourceColumn = "F3"
        pram.SourceVersion = DataRowVersion.Original

        If IsNothing(ds) = False Then
            For j As Integer = 1 To ds.Tables(0).Rows.Count() - 1
                For k As Integer = 1 To ds.Tables(0).Columns().Count() - 1
                    dr = ds1.Tables(0).Rows(j - 1)
                    dr.BeginEdit()
                    dr(k) = ds.Tables(0).Rows(j).Item(k).ToString()
                    dr.EndEdit()
                Next
            Next

            oleadpt2 = New OleDbDataAdapter
            oleadpt2.UpdateCommand = olecomm2
            oleadpt2.Update(ds1, "Input")
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        olecon.Close()
        olecon1.Close()
        olecon = Nothing
        olecon1 = Nothing
        olecomm = Nothing
        olecomm1 = Nothing
        oleadpt = Nothing
        ds = Nothing
        ds1 = Nothing
        dr = Nothing
        pram = Nothing
    End Try
End Sub

In the above code, I am fetching the information from one source excel file and writing to destination excel file with column A as intact. I am writing from column B onwards. The destination dataset ds1 is populate as expected but my destination excel file is not updated with this dataset.

I am getting error as "Operation must be an updateable query"

let me know if you need sample files for reference.

Thanks in advance for your time and help.

I really appreciate for your time you have taken to post your article. it has really helped me and there is some thing i would like to add to the rest of the people who are trying to access a excel 2007 file there needs to be a change to the configuration as follows

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

http://www.connectionstrings.com/excel-2007[^]

And the rest of the code is all the same.

Brother Raman ! thanks again thanks a lot and please keep ur posts coming and kindly ignore the comments of the lazy people who want you to stop development.

Regards

Ravi Shankar Vattikuti

Thanks a lot Ravi

Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/

thanks this article helped me as well.

I have excel file and I set username and pwd for that file. How can I open that file and insert and select rows from it. Please clarify, its urgent.

How can i make a primary key like if column is ID how can i make it as my primary key and control it in my insert statment?

Hi
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.

http://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey.aspx[^]

Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/

hi, i want to make Korean to Khmer dictionary.
1. how can i load font in vb.net?
2. how can i load the word list and definition from Access 2007?
3. how can i publish this application in smaller size?
4. Please give me some more advice for making a dictionary.

The problem I have is I do my development on my work PC at home. The SQL Server (SQL 2000) is located in our Home Office on a server farm. Running a query that looks at the C: drive will not work when run on the SQL server as my excel file is not located there (it's on my PC). The only solution I've been able to come up with is NewRow and Rows.Add to do this, but OMG is it slow. I have a spreadsheet on 2 tabs that has a little over 100k rows and it takes about 3-4 hours to upload this. And it's 2 fields that I'm uploading, a number and a bit. Any suggestions on what I can do otherwise? Due to NT security with the company I work with we can't place files on the SQL server, and we also cannot create linked servers.

Thank you.
Wendell

Hi there, Great Job !!!the code works perfect !!!

I have one question, how can i update an excell file that is shared among other users ???

The code you wrote DOES update the file when it's in shared mode, BUT I have to close the file and open it again to see the changes. For some reason it won't refresh automatically. Can you help me with this ??

Thanks in advance. Big Grin | :-D

Facu20yo.

modified on Sunday, April 19, 2009 10:04 AM

i trying to read and write data from/to excel file row by row.how i can identified row and column.your code given only to read the entire data.please help me...

Hi
Actually I am not very clear with your requirement. I have two points coming in my mind. Please confrim are you looking the solution corresponding to that points or any other requirement you have.

1. Are you want to constantly conecting to Excel sheet and read the row one by one.
2. Or you want to read the complete data from Excel at one shot and use the data one by one with the help of Binding Manager. For e.g. in you application you want to display the data in some form and move between different records by pressing the Next, Previous button on the form.

Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/

actually i'm trying to read data from cell and use it for calculation in my program.i have a lot of data in excel file.
i want to read data from row 1 and multiple column.for example : i read data from row 1 and column A to P.i need to read the A1 and do the calculation.read data B1 and do the calculation and so on...
i think i should put some looping.but i don't know how??please help me...

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.

Hi Paul

Please check the code given below which helps you to edit and update the record in Excel. Rest of the code same as I was posted on website.

olecomm1 = New OleDbCommand
olecomm1.CommandText = "Update [Sheet1$] set " & _
"FirstName=@FName, LastName=@LName, Age=@Age, Phone=@Phone where " & _
"FirstName=@Name"
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"
pram = olecomm1.Parameters.Add("@Name", OleDbType.VarChar)
pram.SourceColumn = "FirstName"
pram.SourceVersion = DataRowVersion.Original
If IsNothing(ds) = False Then
dr = ds.Tables(0).Rows(0)
dr.BeginEdit()
dr("FirstName") = "Bunty"
dr("LastName") = "Aggarwal"
dr("Age") = 26
dr("Phone") = 98989898
dr.EndEdit()
oleadpt.UpdateCommand = olecomm1
Dim i As Integer = oleadpt.Update(ds, "Sheet1")
MessageBox.Show(i & " row affected")
End If

The above code shows that how you can update the first row from dataset, you can customize your code to delete the specific row from the dataset.

As far as delete is concerned, it is currently not allowed in Excel using code but you can achive the same by delete the old excel file and writing the new one.

In case of any query please feel free to contact me.

Thanks

Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/

base in your program about "TestExcel2" bro, i need your help bro on how to edit the existing record bro, using vb.net bro. plz help me bro?

Hi bro,i have a question how to delete data in excel 2007 using vb.net?..pls reply with this codes thank you....and god bless...

Hi bro, wow very nice bro like it. Thank you so much bro, i really appreciate your code project "Reading and writing excel file using VB.NET."

Please remove yourself from the computer programming field, jesus.

Thanks for the suggention. I will definatly think about this, but could you please let me know why you want me to quite from my job. If you are having some issue with the code then let me know. I will try my best to reslove your issue.

Regards

Raman Tayal

Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/

Because the code you posted doesn't not run "out of the box". What's the point of posting code that doesn't work? When I say doesn't work, I should be able to download a code example and it should run without an issue.

Reading and writing an Excel file using VB.NET

Introduction

License

Comments and Discussions