Converting excel data to .MDB
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