Import Excel into Access

[Amjad Hussein]

Hi guys

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

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

[genek]

Open your Excel Spreadsheet, select the data you want in Access and copy it into an Excel spreadsheet all by its self. Save the file as a .csv (comma seperted vocabulary) and you should be able to open the .csv file directly in Access. Set the file type to Text Files (it contains the .csv option) when you are in the Open file dialog box.