Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.
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.
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