Converting excel data to .MDB using VB.net

[ShaileshShinde]

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.

Can anyone help me to provide any examples or references for the same.

Thanks,
Shailesh

[Shuja Ali]

You can connect to excel like you can connect to any other database. You can use OLEDBConnection to connect to your excel file and get the data from the SHEET into a dataset using a Select statement.

Take a look at www.connectionstrings.com for connection string.

[ShaileshShinde]

Below is the code what I am doing......
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

[RobDog888]

another method to perhaps consider.

http://www.codeguru.com/forum/showpost.php?p=1243489&postcount=4

[ShaileshShinde]

Thanks!!!

But I don't want to extarct all the columns data from Excel to .MDB....

There are 15 columns in Excel Sheet1 out of which I have to extract data from three columns to .MDB file.


--
Shailesh