Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

VB.net SQL Client version of OledB code to get data from Excel to SQL

Sub Excel_to_SQL()

        ' Create Data Table for MS-Office 2007 or 2003

        Dim dtExcel As New System.Data.DataTable()

        dtExcel.TableName = "MyExcelData"

Hi

What would the VB.net equivalent of the following code be?

Thanks


        Dim SourceConstr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Sachindra\Desktop\MyExcel2003.xls';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'"

        Dim con As New OleDbConnection(SourceConstr)

        Dim query As String = "Select * from [Sheet1$]"

        Dim data As New OleDbDataAdapter(query, con)

        data.Fill(dtExcel)
        MsgBox("Data Imported Successfully into DataTable")

    End Sub
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

You mean VBA? The above code uses ADO.NET which is not available in VBA. You would need to use recordsets.
ASKER CERTIFIED SOLUTION
Avatar of YZlat
YZlat
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Murray Brown

ASKER

Thanks