none
Reading AutoFilter data in Excel using vb.net RRS feed

  • Question

  • Greetings.

    Using below code (VB.net) i am able to filter Column 'A', 'B' in Excel Sheet. But i am not getting how to read column 'C' filtered data. Please help me. Thanks in advance.

    Dim excel_app As New Excel.ApplicationClass()
    Dim workbook As Excel.Workbook
    Dim sheet As Excel.Worksheet
    
    excel_app = New Excel.ApplicationClass()
    excel_app.Visible = True
    workbook = excel_app.Workbooks.Open(Filename:="C:\abc.xls", ReadOnly:=True)
    sheet = DirectCast(workbook.Sheets(1), Excel.Worksheet)
    sheet.Activate()
    sheet.AutoFilterMode = False
    
    Dim value_range As Excel.Range
    value_range = sheet.Range("A1").CurrentRegion
    value_range.Select()
    'sheet.Range("A1").AutoFilter(Field:=1, Criteria1:="ABC", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)
    'sheet.Range("B1").AutoFilter(Field:=2, Criteria1:="XYZ", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)
    value_range.AutoFilter(Field:=1, Criteria1:="ABC", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)
    value_range.AutoFilter(Field:=2, Criteria1:="XYZ", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)


    Tuesday, August 30, 2016 6:33 PM

Answers

All replies

  • Hi Mujeeb_khan67,

    This forum is to discuss and ask questions about the Visual Basic programming language, IDE, libraries, samples, and tools. According to your code, it is more related to VBA, we'll move the thread to VBA forum for better support.

    Thank you for participating in the forum activities.

    Best Regards,

    Neda Zhang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 31, 2016 9:31 AM
  • The field argument specifies which column to filter. For A it is 1, for B it is 2, For C it is 3

    If that is not your issue then Pls give more detail.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, September 8, 2016 4:52 AM
    Answerer
  • To get filtered data, you could use Range.SpecialCells method.

           Dim cells, cell As Excel.Range
            cells = value_range.Columns(3)
            For Each cell In cells.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeVisible)
                MsgBox(cell.Value)
            Next

    Thursday, September 8, 2016 1:13 PM
    Moderator