Select Single Cell From Excel

[Guest]

I am trying to select a single cell from an Excel workbook. I am using the following code.

strCN = GetExcelConnection("C:\Test\Excel.xls")
strCMD = "select * from [Sheet1$A1:A1]"
oleDbCN = New OleDb.OleDbConnection
oleDbCN.ConnectionString = strCN
oleDbCN.Open()
oleDbCMD = New OleDb.OleDbCommand
oleDbCMD.Connection = oleDbCN
oleDbCMD.CommandType = CommandType.Text
oleDbCMD.CommandText = strCMD
ds = New DataSet
oleDbDA = New OleDb.OleDbDataAdapter(oleDbCMD)
oleDbDA.Fill(ds)
dt = ds.tables(0)

The table that is returned contains zero records. The name of the first column is the value from cell A1. Also if my SQL statement would have been "select * from [Sheet1$A1:A3]" then I would have got 2 rows returned with the value from A1 being the column name and the values from A2 and A3 being the two row values.

My question is how do I get the value of cell A1. Yes, I could get it from the column name, but surely that is not the best practice.

 

[Paul Clement]

¤ I am trying to select a single cell from an Excel workbook. I am using the following code.
¤
¤ strCN = GetExcelConnection("C:\Test\Excel.xls")
¤ strCMD = "select * from [Sheet1$A1:A1]"
¤ oleDbCN = New OleDb.OleDbConnection
¤ oleDbCN.ConnectionString = strCN
¤ oleDbCN.Open()
¤ oleDbCMD = New OleDb.OleDbCommand
¤ oleDbCMD.Connection = oleDbCN
¤ oleDbCMD.CommandType = CommandType.Text
¤ oleDbCMD.CommandText = strCMD
¤ ds = New DataSet
¤ oleDbDA = New OleDb.OleDbDataAdapter(oleDbCMD)
¤ oleDbDA.Fill(ds)
¤ dt = ds.tables(0)
¤
¤ The table that is returned contains zero records. The name of the first column is the value from cell A1. Also if my SQL statement would have been "select * from [Sheet1$A1:A3]" then I would have got 2 rows returned with the value from A1 being the column name and the values from A2 and A3 being the two row values.
¤
¤ My question is how do I get the value of cell A1. Yes, I could get it from the column name, but surely that is not the best practice.

Do you have HDR=No in your connection string? It's probably using the first row/cell value as the
column name.


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