I have an excel table which I want to import into access, but I have no office installed in my PC, I have opened my database in sub main(), and then I use this code in Import_IOlist button to do the import but it didn't work
However, what I want to do is to import one sheet [IO_List] from excel file into my existing database and put the imported sheet into a new table inside my database, any help will be much appreciated.
Thank you in advance for your help
Expand|Select|Wrap|Line Numbers
- Private Sub Import_IOlist_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Import_IOlist.Click
- Dim ExcelName, ExcelDataSource, ExcelExtension As String
- Dim cmd As OleDbCommand
- Dim cmd1 As OleDbCommand
- Dim ExcelConn As OleDbConnection
- Dim ds1 As DataSet
- Dim da As OleDbDataAdapter
- da = New OleDbDataAdapter()
- cmd = New OleDbCommand()
- cmd1 = New OleDbCommand()
- ds1 = New DataSet
- 'Get the Excel file name and the extension
- OpenFileDialog1.ShowDialog()
- ExcelName = OpenFileDialog1.FileName
- ExcelExtension = IO.Path.GetExtension(ExcelName)
- 'Check if the file is Excel 2007 version, or earlier version
- If ExcelExtension = ".xls" Then
- ExcelDataSource = "data source=" & ExcelName & ";" & "Extended Properties=Excel 8.0;"
- ExcelConn = New OleDbConnection(ProviderExcel8 & ExcelDataSource)
- ExcelConn.Open()
- ElseIf ExcelExtension = ".xlsx" Then
- ExcelDataSource = "data source=" & ExcelName & ";" & "Extended Properties=Excel 12.0;"
- ExcelConn = New OleDbConnection(ProviderExcel12 & ExcelDataSource)
- ExcelConn.Open()
- End If
- 'Codes to Import the data from Excel and put it into the MS Access
- cmd1 = ExcelConn.CreateCommand
- cmd1.Connection = ExcelConn
- cmd1.CommandText = "select * into [ms access;Database= C:\Users\NOAMHUS\Documents\Amjad ABB\Engineering Tool\CBM Generator r3.2\CBM_Generator1.mdb].[NewTable] from [IO_List$]"
- cmd1.ExecuteNonQuery()
- ' Code to Import the data to data adapter, which then can be used to display the data in the DataGridView
- cmd = ExcelConn.CreateCommand
- cmd.Connection = ExcelConn
- cmd.CommandText = "select * from [IO_List$]"
- da.SelectCommand = cmd
- Try
- da.Fill(ds1, "MyTable")
- MsgBox("The Import is completed!")
- Me.DataGridView1.DataSource = ds1
- Me.DataGridView1.DataMember = "MyTable"
- Catch e1 As Exception
- MsgBox("Import faield, correct Column name in the sheet!")
- End Try
- ExcelConn.Close()
- End Sub