Getting individual cell info from Excel

Hi all,

Below is the code I have been using to extract all the data from an Excel Worksheet and put it into a Dataset :-

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & "data source=" & ImportFilePath & "; " & "Extended Properties=Excel 8.0;")
MyAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [" & SheetName & "$]", MyConnection)
MyConnection.Open()
MyDataset = New System.Data.DataSet
MyAdapter.Fill(MyDataset)
MyConnection.Close()

I am now trying to extract an specific cells info from Excel and put it into a specified cell on my datasheet. Can someone give me a code example of how I can acheive this.

Many Thanks
kiranboiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SanclerConnect With a Mentor Commented:
This is what you are doing wrong

MyAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [" & SheetName & "$]", MyConnection)
Dim strSQL As String = MyAdapter.ToString

What you want is just

Dim strSQL As String = "select * from [" & SheetName & "$]"

You are using something different from a DataAdapter, not a version of it.  A DataAdapter is really no more nor less than a pre-coded set of commands.  It lets you just say .Fill rather than having to code specific commands to get the table structure from the database, create the a datatable with the same structure in you application, call a DataReader to fill the datatable row by row.  (It also lets you ask it, once it has a Select command, to create the other commands that are needed to update the database and just call .Update.)  But in this setting, you just want a single command which you totally code yourself.

Roger
0
 
manchCommented:

If the Cell u needed is on rowno row and colno column then u can use

MyCellValue =  MyDataSet.Tables(0).Rows(rowno).Item(colno)

now u can use the MyCellValue as u want

0
 
bruintjeConnect With a Mentor Commented:
Hi kiranboi,
----------

another way is selecting only a cell
http://support.microsoft.com/kb/316934/en-us

like Select * from [Sheet1$A1]

----------
bruintje
share what you know, learn what you don't
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
kiranboiAuthor Commented:
thats just what I needed bruintje but how can I insert the cell value into a specific point in a dataset? I'm using :-

 MyAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [" & SheetName & "$C3]", MyConnection)
                MyConnection.Open()
                MyAdapter.Fill(MyDataset.Tables(0).Rows(3).Item(0))
                MyConnection.Close()

but its telling me :- Additional information: No accessible overloaded 'OleDbDataAdapter.Fill' can be called without a narrowing conversion.
0
 
bruintjeCommented:
not sure but you can narrow down in the fill method to using 7 parameters including rowindex etc which will give you the option of inserting a single row in a dataset specified with a string parameter for the name

but i assume you cannot fill a specific cell exactly as you did with the column and item
0
 
SanclerCommented:
You cannot use a dataadapter to fill specific cells.  You will need to use a DataReader or, as you are interested in just one value, you can use ExecuteScalar.  On these lines

   Dim strSQL As String = <Your query returning one result>
   Dim cmd As New OleDbCommand(strSQL,<your connection>)
   cmd.Connection.Open
   <your cell> = cmd.ExecuteScalar
   cmd.Connection.Close

Roger
0
 
kiranboiAuthor Commented:
I'm using the code below and still getting problems.

I get an 'An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll' error on the line :-
MyDataset.Tables(0).Rows(0).Items(0) = cmd.ExecuteScalar

Any ideas what I'm doing wrong?

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & "data source=" & ImportFilePath & "; " & "Extended Properties=Excel 8.0;")
MyAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [" & SheetName & "$]", MyConnection)
Dim strSQL As String = MyAdapter.ToString
Dim cmd As New System.Data.OleDb.OleDbCommand(strSQL, MyConnection)
cmd.Connection.Open()
MyDataset.Tables(0).Rows(0).Items(0) = cmd.ExecuteScalar
cmd.Connection.Close()
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.