Posted By

brettville on 12/20/10


Tagged

sql TreeView macro excel vba dataset OleDB jet OleDbConnection OleDbDataAdapter openfiledialog


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

jnh


Use OLEDB To Read An Excel File


 / Published in: VB.NET
 

Doesn't do any validation, you would want to check if a

  1. Dim ofD As New OpenFileDialog
  2. ofD.ShowDialog()
  3. If ofD.FileName.Length = 0 Then Exit Sub
  4. Dim cS As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  5. "Data Source=" & ofD.FileName & ";" & _
  6. "Extended Properties=Excel 8.0;"
  7. Dim cN As New OleDb.OleDbConnection(cS)
  8. cN.Open()
  9. Dim dA As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", cN)
  10. Dim dSet As New DataSet
  11. dA.Fill (dSet)
  12. tvDB.Nodes.Clear()
  13. Dim dT As DataTable = dSet.Tables(0)
  14. Dim nParent As TreeNode
  15. For iCol As Integer = 0 To dT.Columns.Count - 1
  16. nParent = tvDB.Nodes.Add(dT.Columns(iCol).ColumnName)
  17. For iRow As Integer = 0 To dT.Rows.Count - 1
  18. nParent.Nodes.Add (dT.Rows(iRow).Item(iCol))
  19. Next
  20. nParent.Expand()
  21. Next
  22. dA.Dispose()
  23. dSet.Dispose()
  24. cN.Close()
  25. cN.Dispose()

Report this snippet  

You need to login to post a comment.