Question Filtering excel data programmatically

[speedytrack_2836]

Hi all,

The code that I currently have allows me to browse for an excel 2007 file and then open it, then, by pressing a button, it retrieves of range of data from the excel file and posts it in a message box. What I need is this:

After I open the file but before I retrieve the data from it I need to write some code that will filter the first column, ultimately only showing cells in that column with a "1" in it.

Any help will be highly appreciated.

Here's the code, I use visual basic 2008 express edition:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class MainForm

'Sets the variables
Dim APP As Excel.Application
Dim Workbook As Excel.Workbook

Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click

Using bbt As New OpenFileDialog

bbt.Filter = "All files (*.*)|*.*" ''''''''''''''''''''''''''''''Filters the files to display all files.

bbt.Title = "Select a File" '''''''''''''''''''''''''''''''''''''Title of the browser.

If bbt.ShowDialog() = Windows.Forms.DialogResult.OK Then ''''''''If a file is selected and OK is pressed then...

APP = New Excel.ApplicationClass ''''''''''''''''''''''''''''Creates and excel application and...
Workbook = APP.Workbooks.Open(bbt.FileName) '''''''''''''''''Opens the selected file.
'worksheet = workbook.Worksheets("sheet1")

APP.Visible = True ''''''''''''''''''''''''''''''''''''''''''Makes file visible.
APP.UserControl = True ''''''''''''''''''''''''''''''''''''''Gives user control of file.

Else : MessageBox.Show("Please Select a File") ''''''''''''''''''If user exits without choosing a file then show
' 'Message.
End If

End Using

End Sub

Private Sub btnExtract_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExtract.Click

Dim Sheets As Excel.Sheets
Dim Sheet As Excel._Worksheet
Dim Range As Excel.Range
Dim saRet(,) As Object
Dim Rows As Long
Dim Cols As Long
Dim valueString As String
'Dim Range1 As Excel.Range


Sheets = Workbook.Worksheets ''''''''''''''''''''''''''''''''''''''''Get a reference to the first sheet of the
Sheet = Sheets(1) 'Workbook.

Range = Sheet.Range("A1", "C10") '''''''''''''''''''''''''''''''''''''Get a range of data.

saRet = Range.Value '''''''''''''''''''''''''''''''''''''''''''''''''Retrieve the data from the range.

'Past this point is code that makes an array and displays the data that has been pulled

Rows = saRet.GetUpperBound(0) '''''''''''''''''''''''''''''''''''''''Determine the dimensions of the array
Cols = saRet.GetUpperBound(1)

valueString = vbCrLf ''''''''''''''''''''''''''''''''''''''''''''''''Build a string that contains the data of
For rowCounter = 1 To Rows 'The array.
For colCounter = 1 To Cols

'Write the next value into the string
valueString = String.Concat(valueString, saRet(rowCounter, colCounter).ToString() + " ")

Next colCounter

'Write in a new line
valueString = String.Concat(valueString, vbCrLf)

Next rowCounter

MessageBox.Show(valueString, "Array Values") '''''''''''''''''''''''''''''''''''''''''Report the value of the array.


Range = Nothing '''''''''''''''''''''''''''''''''''''''''''''''''''''Clean up
Sheet = Nothing
Sheets = Nothing

End Sub
End Class