Excel

[Guest]

Hi
I am using the below code to read an excel sheet.
It works fine, but the problem i am facing is the name of the excel sheet,
which i have hardcoded in my query as [sheet1$].

I am getting errors when the sheet name is not [sheet1$] !!!

How can i make the query Generic even if the sheet name is different????



Dim sbConn As StringBuilder = New StringBuilder
sbConn.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
filePath)
sbConn.Append(";Extended Properties=")
sbConn.Append(Convert.ToChar(34))
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2")
sbConn.Append(Convert.ToChar(34))

Dim cnExcel As OleDbConnection = New
OleDbConnection(sbConn.ToString())
cnExcel.Open()
Dim cmdExcel As OleDbCommand = New OleDbCommand("select * from
[sheet1$]", cnExcel)
Dim drExcel As OleDbDataReader
Try
drExcel =
cmdExcel.ExecuteReader(CommandBehavior.CloseConnection)
Catch
lblError.Text = "Please Check the Excel Data before UPLOADING"
End Try


Thanks
vinay

 

[Guest]

Can you use an Excel object and get the sheet names and ordinal position
before opening the connection?