Import excel Data to data table

[gloringg]

Hi,

I am currently using vb 2008 and access 2003 as back end database.

I am trying to import excel data to a data table.

my excel and access table header & columns are same.

I was trying to do this.........

   OpenFileDialog1.ShowDialog()

        
        Dim fn As String = System.IO.Path.Combine(System.Windows.Forms.Application.StartupPath, "ABC.mdb")



        con = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source= " & fn & "")


        cmd = New OleDbCommand("INSERT INTO Data SELECT * FROM [Sheet$] IN '" & OpenFileDialog1.FileName.ToString & "' 'Excel 8.0'", con)


        Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
        Catch myException As Exception
            MsgBox("Couldn't insert record: " + myException.ToString())
        Finally
            cmd.Connection.Close()
        End Try
        Form1.Show()

But this is giving me an error as "unrecognized data format .xls"

Can some one please correct this?

Thanks

 

[cjard]

Try going the other way round:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sheet.xls;Extended Properties=Excel 8.0;Persist Security Info=False

INSERT INTO [Table1] IN 'C:\db.mdb' SELECT * FROM [Sheet$]