Reading the value from a cell in Excel File

[GKG]

Hi,

This is my current issue.

In the application i get an excel file,then i need to read all the values in that excel sheet.
This i accomplished by using :

Dim objCmdSelectStore As New OleDbCommand("SELECT * FROM [StoreDetails$]", objconn)

it wrks fine...but...

Now I need to read another excel sheet which has many values.But I dont need all those values.What i need is only the value from 1 CELL.
So can any one tell me if there is a way to read only 1 cell value by using a similar code mentioned above(i.e using a 'Select' query )

I tried this but doesnot work:

Dim VarCmd As New OleDbCommand("Select CellName from [Variable$]", objconn)

Plzzzz Help !!!!!!!!!

Cheers
GKG

 

[DavidT_macktool]

Set up a named range in excel and read it in

see this article:

http://www.developer.com/net/vb/article.php/3107171

even better - from Microsoft

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp


select * from [Sheet1$A1:B10]
or
select * from [MyNamedRange]

Hope this helps,

 

[MyForumID]

It took me a long time to find something that worked for me. I don't know if this will help you read a specific cell because I haven't gotten that far yet, but it will allow you to select/write into a specific cell on any given worksheet in a file:

[SIZE=2][COLOR=#0000ff]    Dim[/COLOR][/SIZE][SIZE=2] oexcel [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Excel.Application
[/SIZE][SIZE=2][COLOR=#0000ff]    Dim[/COLOR][/SIZE][SIZE=2] oexcelbook [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Excel.Workbook
[/SIZE][SIZE=2][COLOR=#0000ff]    Dim[/COLOR][/SIZE][SIZE=2] oexcelsheet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Excel.Worksheet[/SIZE]
[SIZE=2]
     oexcelbook = oexcel.Workbooks.Open("Filename.xls", , [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE][SIZE=2])
     oexcelsheet = oexcelbook.Worksheets("Sheet1")
     oexcelsheet.Select()
     oexcelsheet.Range("A1").Formula = "Finally!!!"
     oexcel.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE]

I haven't run it for a long time since I've been working on my form, but hopefully it helps as a starting point for you.

Good luck.

 

[DavidT_macktool]

Need to select at least two cells

                ExcelConnection = New System.Data.OleDb.OleDbConnection( _
                      "provider=Microsoft.Jet.OLEDB.4.0; " & _
                      "data source=" & filename & " ; " & _
                      "Extended Properties=Excel 8.0;")
                ' Select the data from Sheet1 of the workbook.
                ExcelCommand = New System.Data.OleDb.OleDbDataAdapter( _
                      "select * from  [Sheet1$A1:A2]", ExcelConnection)
                DSexcel = New System.Data.DataSet
                ExcelCommand.Fill(DSexcel)
                ExcelConnection.Close()
                RGrid.DataSource = DSexcel.Tables(0)

I have to select at least 2 rows, the first row becomes the column names/headers, the second row is the data. Not sure of the code to select non-consecutive cells.
Not sure how you get a meaningful column header for a cell in the middle of the spreadsheet, guess you could reference that cell in cell z2 and add a cloumn name in z1 - kinda hiding what you want from the excel users.

Not sure if this will help, could get you started, let me know

 

[GKG]

I shall try this

Get back to u soon

Cheers
GKG