1. Add two references to your .NET project:

System.Data
System.Data.DataSetExtensions

2. Define some variables in your VB.NET method:

Dim myguixt = New guixt
Dim MyConnection As OleDb.OleDbConnection
Dim DtSet As DataSet
Dim MyCommand As OleDb.OleDbDataAdapter
Dim sqlcondition As String
Dim excelfile As String = "d:\Excel\SampleData.xls"

3. Check if the file can be accessed:

Try

 File.Open(excelfile, FileMode.Open, FileAccess.Write, _
 FileShare.None).Dispose()

 Catch ex As IOException
 MsgBox("File could not be opened/accessed")
Return

End Try

When the file is open in Excel, it cannot be accessed by the OLE DB interface!

4. Open a connection to the Excel file via OLE DB:

MyConnection = New OleDb.OleDbConnection _
("provider=Microsoft.Jet.OLEDB.4.0;Data source='" & excelfile _ 
& "';Extended Properties=Excel 8.0")

6. Mapping to a DataSet:

In order to read the data in VB.NET we need a mapping to a DataTable:

MyCommand.TableMappings.Add("Table", "DataTable1")

Then you can read in the data from the Excel file into a new DataSet:

DtSet = New DataSet
MyCommand.Fill(DtSet)

In this case you can access the columns e.g. by index number. We recommend using a mapping to an existing DataSet (called DataSet1 in this example):  

You should also set the DataType to System.Object so the OLE DB interface is not trying to guess the data type.

DtSet = New DataSet1
MyCommand.Fill(DtSet)

Now we can close the connection:

MyConnection.Close()

7. Finally you can access the data in the DataSet:

For Each r As DataRow In DtSet.Tables(0).Rows
  countRows += 1
 
  ' Read a value
  Dim total = ""
  Try
  total = r.Item("Total").ToString

  ' Add row to SAP table
  myguixt.SetVariable("table_regions.cell.col_total." _ 
   & countRows.ToString, total)

Catch ex As Exception

End Try

' Read row and add it to our View
DataGridView5.Rows.Add({r.Item(1), r.Item(6)})
' or
DataGridView5.Rows.Add(r.ItemArray)

Next r