Click here to Skip to main content
15,861,125 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 vb.net the following code works fine, only if the sheet in the excel is named Sheet1.
VB
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$]"
conn.Close()


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.

Thanks
Posted
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
 
Comments
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    
tyutyuytu
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
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:\vb.net-informations.xls';Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "Net-informations.com")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()

    End Sub
End Class



or
visit here...
how-to-import-data-from-excel-to-datagridview-by-visual-basic?forum=vbgeneral[^]
 
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
           SheetList.Add(objWorkSheets.Name)
           ' ListBox1.Items.Add(objWorkSheets.Name)
       Next
       End_Excel_App()
       Return SheetList
   End Function


Then Call it like this:

VB
'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
 
Comments
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

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