Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8057
  • Last Modified:

Read data from Excel file

I need to read from a Excel file and display the cell data on a form. I read helpfile and found out things like DataSet, DataAdapter, OleDbConnection, OleDbDataAdapter. I am very confused and really need some advice here.

I copied the below coding but encounters error
"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

-----------------------------------------------------------------------
Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
      "provider=Microsoft.Jet.OLEDB.4.0; " & _
      "data source=C:\myData.XLS; " & _
      "Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
      "select * from [Sheet1$]", MyConnection)

DS = New System.Data.DataSet()
MyCommand.Fill(DS)
MyConnection.Close()
-----------------------------------------------------------------------
I have no idea what to put for "provider=??"

I didnt use FileOpen because it is said that it will cause file corruption and loss of file integrity.

Please help..

0
xiaohui
Asked:
xiaohui
  • 2
1 Solution
 
prakash_prkCommented:
call  MyConnection.Open()
before fill the adapter

regards
prakash
0
 
prakash_prkCommented:
Write the exception  handling mechanism. It will give the full info about the exception..
---------------------------------------------------------------------------------------------------------------------------
  Dim DS As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Try

            MyConnection = New System.Data.OleDb.OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=C:\myData.XLS; " & _
                  "Extended Properties=Excel 8.0;")

            MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
                  "select * from [Sheet1$]", MyConnection)

            MyConnection.Open()

            DS = New System.Data.DataSet
            MyCommand.Fill(DS)
            MyConnection.Close()
            Me.DataGrid1.DataSource = DS
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
---------------------------------------------------------------------------------------------------------------------------
regards
prakash
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now