Read all cells - in all sheets - of an excel file

V

VBOfficer

Newcomer
Joined
Jan 22, 2009
Messages
24
So this is the VB forum :D
As my post's title indicates, I wanna read all cells of all sheets/pages in my excel file and just show the text content of each cell.
I have written my code but have 2 problems:
Visual Basic: 
Dim ExcelConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Excel.xls;Extended Properties=Excel 8.0;")
ExcelConnection.Open()
Dim MyCommands As New OleDbCommand("SELECT * FROM [Sheet1$]", ExcelConnection)
Dim MyReader As OleDbDataReader = MyCommands.ExecuteReader
While MyReader.Read()
    'How should I read cells here?
End While
MyReader.Close()
ExcelConnection.Close()
1. How can I read cells inside the While?
2. My select command only selects Sheet1, how can I select ALL sheets?
Thank you guys/gals, as I am not expert in database.
 
I never recommend this code in the .NET environment, but however, this is the only way I know about this.
Let's see what the others will say...
Visual Basic: 
Dim db As DAO.Database
Dim DAODBEngine As New DAO.DBEngine()
db = DAODBEngine.OpenDatabase("D:\Book1.xls", False, True, "Excel 8.0;")
For MyLoop As Integer = 0 To db.TableDefs.Count.ToString - 1
        MessageBox.Show(db.TableDefs(MyLoop).Name)
Next
db.Close()
 
This is what you should be using...
Visual Basic: 
Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Excel.xls;Extended Properties=Excel 8.0;")
MyConnection.Open()
Dim SchemaTable As New DataTable
SchemaTable = MyConnection.GetOleDbSchemaTable(Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
For MyLoop As Integer = 0 To SchemaTable.Rows.Count - 1
    MsgBox("SELECT * FROM " + SchemaTable.Rows(MyLoop)!TABLE_NAME.ToString)
    Dim MyCommands As New OleDbCommand("SELECT * FROM [" + SchemaTable.Rows(MyLoop)!TABLE_NAME.ToString + "]", MyConnection)
    Dim MyReader As OleDbDataReader = MyCommands.ExecuteReader
    If MyReader.HasRows = True Then
        While MyReader.Read
            For MyReadLoop As Integer = 0 To MyReader.FieldCount - 1
                If MyReader(MyReadLoop).ToString <> Nothing Then
                    MessageBox.Show(MyReader(MyReadLoop).ToString)
                End If
            Next
        End While
    End If
    MyReader.Close()
Next
 
Back
Top