Click here to Skip to main content
15,920,005 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi.... i have a code for retrieve each data from excel to update dbf file. i use Microsoft.Office.Interop.Excel for read the data each range of column.
now i want to change the connection excel using oledb..
how can i retrieve data of each column and set it into a variable?
here is my code for Microsoft.Office.Interop.Excel.
VB
xlApp = New Excel.Application

xlWorkBook = xlApp.Workbooks.Open(xlsName)
xlWorkSheet = xlWorkBook.Worksheets("sheet1")
xlRange = xlWorkSheet.UsedRange
endrow = xlRange.Rows.Count

For rCnt = 3 To endrow

    Empno = xlRange.Cells(rCnt, 1).Value
    totalhrs = xlRange.Cells(rCnt, 3).Value
    latehr = xlRange.Cells(rCnt, 4).Value

 Next

xlWorkBook.Close()
xlApp.Quit()

this is my oledb connection. after the connection,i really dont have an idea what should i do to get the data. please help me...i hope someone in codeproject can help me.
VB
  Dim xlsConnect As System.Data.OleDb.OleDbConnection
  Dim DtSet As System.Data.DataSet
  Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

Try

      If (Path.GetExtension(xlsName) = ".xls") Then
          xlsConnect = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlsName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2""")
      ElseIf (Path.GetExtension(xlsName) = ".xlsx") Then
          xlsConnect = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xlsName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';")
      End If
      MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1$]", xlsConnect)
      'looping data should be here

      xlsConnect.Close()
  Catch ex As Exception
      MsgBox(ex.ToString)
  End Try
Posted
Updated 1-Aug-13 20:34pm
v2

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900