Selecting Data From an Excel file

[ZeroEffect]

Thanks For stopping by, Here is what I am doing.

 

I want to take an excel file grab some data from it and then do some other things with that data then export it as a new excel sheet.

 

I am able to load the whole excel file into a dataset which is great but I don't want all the data that is in the excel file. But As of right now I have not found what expresion to use to find the data I want.

 

Here is the code I am using to load the file.

 

       Dim DBSet As System.Data.DataSet
       Dim DBCommand As System.Data.OleDb.OleDbDataAdapter
       Dim DBCon As System.Data.OleDb.OleDbConnection
       Try
           DBCon = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=Analog Report.XLS;Extended Properties=Excel 8.0;")
           DBCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", DBCon)

           DBSet = New System.Data.DataSet
           DBCommand.Fill(DBSet)
           DataGrid1.DataSource = DBSet.Tables.Item(0) '///put the excel info in to a datagrid.
       Catch ex As Exception
           MessageBox.Show(ex.Message)
       Finally
           DBCon.Close() '/// close the connection to excel.
           DBSet = Nothing
           DBCommand = Nothing
       End Try

 

Ideally I would like to take the column D from row 9 to 40 as new columns rows and column K rows 9 through the end of the file as a value. The name in column D repeat.

 

and example would be D9 = Tower 1 Ratio, K9 = 0.82

 

And in a table it would be

 

Tower 1 Ratio

0.82

 

And every time Tower 1 Ratio was found in column D it would ad the value to the new table under Tower 1 Ratio.

 

Is there a way to do this and how. As always I'll be hunting for more info.

 

Thanks for your help,

 

ZeroEffect

[ZeroEffect]

Update

 

Ok I have made some progress I figured out how to get the rows I wanted.

 

DBCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$D7:K]", DBCon)

 

I load that into datagrid then I loop through that datagrid for the data I want then I load that into a new datagrid. So now I have a datagrid with two columns one with names and one with readings. My next step is to loop through the first column in the second datagrid and create a data tabe from it. The values in column one repete every so many rows. So durring the loop process Every new value will become a column in the new dataset. Once that is done I'll loop through the second column and place the value in the correct column.

 

It's progress :)

 

ZeroEffect