Click here to Skip to main content
13,865,831 members
Rate this:
Please Sign up or sign in to vote.
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 21:20pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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.
Peter_jnr 30-Nov-13 9:09am
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
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
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

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...
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

' 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) + "$]"
Alok9006585411 1-Apr-14 1:26am
How to get Excel sheets Fields Name from Above Codes
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

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

Rate this: bad
Please Sign up or sign in to vote.

Solution 7

dear sir

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web06 | 2.8.190214.1 | Last Updated 21 Dec 2016
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100