Excel Access

[Jim Heavey]

I have written a little procedure which opens and displays the contents of
an excel spreadsheet. Everything works just fine when my column headings
are on the first row. But what if the data I want to extract is on the
15th row or the 64th row. How do I control where the selections
starts/ends. Here is the code that I am using....

Dim conString As String = String.Format
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended
Properties='Excel 8.0;HDR=YES;'", fileLocation)

If Me.cboSheetNames.SelectedIndex = -1 Then
ErrorProvider1.SetError(cboSheetNames, "Must Make A Selection")
Else
ErrorProvider1.SetError(cboSheetNames, "")
End If
sheetName = String.Format("[{0}$]", cboSheetNames.Text)
Dim selString As String = String.Format("select {0} From {1}",
txtFieldNames.Text, sheetName)
Dim con As New OleDb.OleDbConnection(conString)
Dim cmd As New OleDb.OleDbCommand(selString, con)
Dim da As New OleDb.OleDbDataAdapter(New OleDbCommand(selString))
da.SelectCommand.Connection = New OleDbConnection(conString)
Dim ds As New DataSet()
da.Fill(ds, "ExcelData")
DataGrid1.DataSource = ds.Tables("ExcelData")
xlApp.Workbooks.Close()


Thanks in advance for your assistance!!!!!!!!!!!!

 

[Miha Markic]

Hi Jim,

Fill method has an overload you want:
da.Fill(ds, 2, 1, "ExcelData")

Will start from 3rd record and will fill only one row.

 

[Paul Clement]

¤ I have written a little procedure which opens and displays the contents of
¤ an excel spreadsheet. Everything works just fine when my column headings
¤ are on the first row. But what if the data I want to extract is on the
¤ 15th row or the 64th row. How do I control where the selections
¤ starts/ends. Here is the code that I am using....
¤
¤ Dim conString As String = String.Format
¤ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended
¤ Properties='Excel 8.0;HDR=YES;'", fileLocation)
¤
¤ If Me.cboSheetNames.SelectedIndex = -1 Then
¤ ErrorProvider1.SetError(cboSheetNames, "Must Make A Selection")
¤ Else
¤ ErrorProvider1.SetError(cboSheetNames, "")
¤ End If
¤ sheetName = String.Format("[{0}$]", cboSheetNames.Text)
¤ Dim selString As String = String.Format("select {0} From {1}",
¤ txtFieldNames.Text, sheetName)
¤ Dim con As New OleDb.OleDbConnection(conString)
¤ Dim cmd As New OleDb.OleDbCommand(selString, con)
¤ Dim da As New OleDb.OleDbDataAdapter(New OleDbCommand(selString))
¤ da.SelectCommand.Connection = New OleDbConnection(conString)
¤ Dim ds As New DataSet()
¤ da.Fill(ds, "ExcelData")
¤ DataGrid1.DataSource = ds.Tables("ExcelData")
¤ xlApp.Workbooks.Close()
¤

The column headings are assumed to be in the first returned row of the Worksheet. If the physical
location is different then you need to specify a Range name or value:

Using a Range name defined in the Workbook:

"Select * from Range6x4"

Using a Range value:

"Select * from [Sheet1$A1:C100]"


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)