The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
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.OleDbCon
MyAdapter = New System.Data.OleDb.OleDbDat
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
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.OleDbCon
MyAdapter = New System.Data.OleDb.OleDbDat
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
Who is Participating?
If the Cell u needed is on rowno row and colno column then u can use
MyCellValue = MyDataSet.Tables(0).Rows(r
now u can use the MyCellValue as u want
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
----------
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
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.OleDbDat
MyConnection.Open()
MyAdapter.Fill(MyDataset.T
MyConnection.Close()
but its telling me :- Additional information: No accessible overloaded 'OleDbDataAdapter.Fill' can be called without a narrowing conversion.
MyAdapter = New System.Data.OleDb.OleDbDat
MyConnection.Open()
MyAdapter.Fill(MyDataset.T
MyConnection.Close()
but its telling me :- Additional information: No accessible overloaded 'OleDbDataAdapter.Fill' can be called without a narrowing conversion.
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
but i assume you cannot fill a specific cell exactly as you did with the column and item
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
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
I'm using the code below and still getting problems.
I get an 'An unhandled exception of type 'System.Data.OleDb.OleDbEx
MyDataset.Tables(0).Rows(0
Any ideas what I'm doing wrong?
MyConnection = New System.Data.OleDb.OleDbCon
MyAdapter = New System.Data.OleDb.OleDbDat
Dim strSQL As String = MyAdapter.ToString
Dim cmd As New System.Data.OleDb.OleDbCom
cmd.Connection.Open()
MyDataset.Tables(0).Rows(0
cmd.Connection.Close()
I get an 'An unhandled exception of type 'System.Data.OleDb.OleDbEx
MyDataset.Tables(0).Rows(0
Any ideas what I'm doing wrong?
MyConnection = New System.Data.OleDb.OleDbCon
MyAdapter = New System.Data.OleDb.OleDbDat
Dim strSQL As String = MyAdapter.ToString
Dim cmd As New System.Data.OleDb.OleDbCom
cmd.Connection.Open()
MyDataset.Tables(0).Rows(0
cmd.Connection.Close()
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.
-
Course of the Month11 days, 19 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
MyAdapter = New System.Data.OleDb.OleDbDat
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