Shail Shin

Shail Shin

  • NA
  • 1
  • 0

Converting excel data to .MDB

Apr 27 2006 5:14 AM
Hi All, I have to convert excel data to mdb using vb.net. excel sheet contains data in 10 columns which i want to extract into .mdb file in which i have created table1 with 10 fields. once data extraction is done i want to view data in any viewer. I need that user select the xls file and click convert button. from sheet1 columnA "term" ColumnC "description" and columnF "comments" data should get extracted to mdb file in which i have created table as terms. which contains below mentioned fields: term_id, term, desc, comments and so on.. Also sheet one contains: term, description, comments it's creates new table or gives error as terms already exits, actually it should extract data to terms which i have already created and put the excelk data for respective fields. But stuck with this only........? Code: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strFileName As String Dim ofd As New OpenFileDialog ofd.CheckFileExists = True ofd.Filter = "XLS File (*.xls) | *.xls" ofd.Title = "Select the XLS File" ofd.Multiselect = False ofd.ShowDialog() mstrInputXLSFile = ofd.FileName TextBox1.Text = mstrInputXLSFile End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim mExcelFile As String = mstrInputXLSFile Dim mAccessFile As String = "C:\temp\access_schema.mdb" Dim mWorkSheet As String Dim mTableName As String Dim mDataBase As DAO.Database 'mExcelFile = Application.StartupPath & "\Book1.xls" 'mAccessFile = Application.StartupPath & "\Db2.mdb" mWorkSheet = "Sheet1" mTableName = "Terms" mDataBase = DAODBEngine_definst.OpenDatabase(mExcelFile, True, False, "Excel 5.0;HDR=Yes;IMEX=1") mDataBase.Execute("Select * into [;database=" & mAccessFile & "]." & mTableName & " FROM [" & mWorkSheet & "$]") MsgBox("Done. Use Access to view " & mTableName) End Sub Edit/Delete Message