Click here to Skip to main content
15,440,991 members
Please Sign up or sign in to vote.
4.64/5 (4 votes)
See more:

I am inserting data from excel document to datagridview control in the following code works fine, only if the sheet in the excel is named Sheet1.
Dim conn As OleDbConnection
Dim dtr As OleDbDataReader
Dim dta As OleDbDataAdapter
Dim cmd As OleDbCommand
Dim dts As DataSet
Dim excel As String
Dim OpenFileDialog As New OpenFileDialog

OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"

If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then

Dim fi As New FileInfo(OpenFileDialog.FileName)
Dim FileName As String = OpenFileDialog.FileName

excel = fi.FullName
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
dts = New DataSet
dta.Fill(dts, "[Sheet1$]")
DataGridView1.DataSource = dts
DataGridView1.DataMember = "[Sheet1$]"

The problem now is the sheet might have a different name and i want to get the first sheet in the excel document despite what name it is. Any help regarding this will be highly appreciated.

Updated 20-Dec-16 20:20pm

Rather than DataSet, use DataTable[^]. It enables you to define only DataSource[^] property for DataGridView, no matter about DataMember[^] ;)

MSDN wrote:
You do not need to set this property (DataMember) when binding to a data source that contains a single list or table.
Share this answer
Peter_jnr 30-Nov-13 9:09am     CRLF
When I decide to use the datatable, how can you set another datatable to read from the datasource which will now be the datagrid's content and save it to access (a database created at runtime with the users own filename)? I know my question is not clear but please try to understand. I am trying to write the datagrid's content to access as a new database created by the user at runtime.. Thanks in advance...
Maciej Los 30-Nov-13 12:01pm    
Sorry... I believe my answer on the question is complete. I would suggest you to post another question.
Member 11090340 15-Oct-14 12:32pm    
Member 13230605 30-May-17 5:45am     CRLF
I've tried it but I have a problem like that : ERROR [42000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.16]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Sheet1$]' at line 1
Use this....

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\';Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "")
        DtSet = New System.Data.DataSet
        DataGridView1.DataSource = DtSet.Tables(0)

    End Sub
End Class

visit here...
Share this answer
' This function open the excel file in excel application and get all the work sheet names and return it as array
   Private Function getExcelSheetsName(ByVal Excelfilename As String) As ArrayList
       Dim objExcel As Excel.Application
       Dim objWorkBook As Excel.Workbook
       Dim objWorkSheets As Excel.Worksheet
       Dim SheetList As New ArrayList
       objExcel = CreateObject("Excel.Application")
       objWorkBook = objExcel.Workbooks.Open(Excelfilename)
       For Each objWorkSheets In objWorkBook.Worksheets
           ' ListBox1.Items.Add(objWorkSheets.Name)
       Return SheetList
   End Function

Then Call it like this:

'get all excel worksheet names
           Dim arrList As ArrayList = getExcelSheetsName(excel)
           'get the first sheet name and add the sign $ to it
           Dim sheetName As String = "[" + arrList(0) + "$]"
Share this answer
Alok9006585411 1-Apr-14 1:26am    
How to get Excel sheets Fields Name from Above Codes
This is the line that get the sheets field name:
Dim sheetName As String = "[" + arrList(0) + "$]"

the number (0) is for the first sheet, (1) for the second one and ... so one

Share this answer
dear sir

my quotion is my excel sheet frist save then import excel sheet
please code send me
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900