By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,333 Members | 1,047 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,333 IT Pros & Developers. It's quick & easy.

Import Excel into Access

P: 12
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Import_IOlist_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Import_IOlist.Click
  2.  
  3.         Dim ExcelName, ExcelDataSource, ExcelExtension As String
  4.         Dim cmd As OleDbCommand
  5.         Dim cmd1 As OleDbCommand
  6.         Dim ExcelConn As OleDbConnection
  7.         Dim ds1 As DataSet
  8.         Dim da As OleDbDataAdapter
  9.  
  10.         da = New OleDbDataAdapter()
  11.         cmd = New OleDbCommand()
  12.         cmd1 = New OleDbCommand()
  13.         ds1 = New DataSet
  14.  
  15.         'Get the Excel file name and the extension
  16.         OpenFileDialog1.ShowDialog()
  17.         ExcelName = OpenFileDialog1.FileName
  18.         ExcelExtension = IO.Path.GetExtension(ExcelName)
  19.  
  20.         'Check if the file is Excel 2007 version, or earlier version
  21.         If ExcelExtension = ".xls" Then
  22.             ExcelDataSource = "data source=" & ExcelName & ";" & "Extended Properties=Excel 8.0;"
  23.             ExcelConn = New OleDbConnection(ProviderExcel8 & ExcelDataSource)
  24.             ExcelConn.Open()
  25.         ElseIf ExcelExtension = ".xlsx" Then
  26.             ExcelDataSource = "data source=" & ExcelName & ";" & "Extended Properties=Excel 12.0;"
  27.             ExcelConn = New OleDbConnection(ProviderExcel12 & ExcelDataSource)
  28.             ExcelConn.Open()
  29.         End If
  30.  
  31.  
  32.         'Codes to Import the data from Excel and put it into the MS Access
  33.         cmd1 = ExcelConn.CreateCommand
  34.         cmd1.Connection = ExcelConn
  35.         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$]"
  36.         cmd1.ExecuteNonQuery()
  37.  
  38.  
  39.         ' Code to Import the data to data adapter, which then can be used to display the data in the DataGridView
  40.         cmd = ExcelConn.CreateCommand
  41.         cmd.Connection = ExcelConn
  42.         cmd.CommandText = "select * from [IO_List$]"
  43.         da.SelectCommand = cmd
  44.  
  45.  
  46.         Try
  47.             da.Fill(ds1, "MyTable")
  48.             MsgBox("The Import is completed!")
  49.             Me.DataGridView1.DataSource = ds1
  50.             Me.DataGridView1.DataMember = "MyTable"
  51.         Catch e1 As Exception
  52.             MsgBox("Import faield, correct Column name in the sheet!")
  53.         End Try
  54.         ExcelConn.Close()
  55.     End Sub
  56.  
Jul 18 '12 #1
Share this Question
Share on Google+
1 Reply


P: 3
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.
Sep 4 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.