Processing a DataSet?

[RibTime]

I'm very new at VB and trying to lean by writing a small program to read an excel spreadsheet and then add/update some of the cells. As you can see below I have used ADO.Net and have loaded my data into a DataSet.

Having searched round the forums and web for examples I seem have drawn a blank. I suspect I'm missing something and would appreceiate some pointers or code.

How do I read a cell's worth of data out of my data set or should I use some other method to process the data?

 Dim conn As System.Data.OleDb.OleDbConnection 
 
Dim comm As System.Data.OleDb.OleDbDataAdapter
 
Dim fso As Scripting.FileSystemObject
 
fso = New Scripting.FileSystemObject
 
If (fso.FileExists("V:\Test.xls") = False) Then
 
MsgBox("Error Excel spreadsheet does not exist")
 
GoTo Clean_Up
 
End If
 
conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=v:\test.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
 
comm = New System.Data.OleDb.OleDbDataAdapter("Select * From [Sheet1$]", conn)
 
Dim Excel As New DataSet
 
Try
 
conn.Open()
 
comm.Fill(Excel)
 
Me.DataGrid.DataSource = Excel.DefaultViewManager
 
Catch ex As Exception
 
MessageBox.Show(ex.Message)
 
Finally
 
conn.Close()
 
conn.Dispose()
 
comm.Dispose()
 
End Try
 
Clean_Up:
 
conn = Nothing
 
comm = Nothing
 
fso = Nothing

Thanks in advance

RibTime

 

[RibTime]

After posting this thread I realise, or realize for the US, that it should be posted somwhere else.


Anyway 16 views and no answers!

RibTime

 

[Paszt]

In order to get the value of one cell, you use the Item collection of the Rows collection of the Tables collection of the dataset:

Excel.Tables(0).Rows(<index>).Item("<ColumnName>")

A few other pointers:
use the System.IO.File.Exists function to determine if the file exists instead of the Scripting.FileSystemObject.
Instead of using GoTo use the Finally statement in you Try/Catch block.

 

[RibTime]

Paszt

Many thanks for your reply and advice, I shall have a look at my code very soon!

Thanks again

RibTime