Count row with data and specific value from excel in VB.net

Question

I have 2 Labels (lblTotal) and (lblYes) which I put on my form. I want to read an excel file and count how many people come and how many say "yes", like this (picture). So "lblTotal" will count all row with data (exclude table header) and "lblYes" will show the counting of Answer with "Yes" which updates in real time. So far I got the lblTotal to work:

Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
Dim xlCells As Excel.Range = Nothing

Public Sub Total()
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open("D:\Question\Data.xlsx")
        xlApp.Visible = False
        xlWorkSheet = xlWorkBook.Worksheets("Sheet1")

Dim xx As Integer = xlWorkSheet.UsedRange.Rows.Count - 1
        lblTotal.Text = xx.ToString()
        xlApp.Quit()
End Sub

However, I have no idea how to count row that has the Yes answer. Also this doesn't allow real time update. Even though I use a timer to do the job (update on each tick) but it takes it too much to load. For example, in the code above, I used Button to refresh by calling the Total() function in the button click event.

I was thinking about the Button Save event that triggered this update as well. But my biggest question is how can I make it count "Yes" answer only?

Thank. That give me a way of new search but what should I start? xlworksheet.range("D1").autofilter — Tendo Sai
– Tendo Sai, Commented Sep 14, 2018 at 4:58
Looks like Gabriels' approach of simply running through the range and counting the "Yes" values works for you. It's probably a better solution than applying a filter. — BobRodes
– BobRodes, Commented Sep 14, 2018 at 19:22

Gabriel Stancu · Accepted Answer · 2018-09-14 07:03:44Z

1

I would do something like this, inside the Total() function:

    Dim WorkSheetPath As String = "D:\Question\Data.xlsx"
    Dim xlApp As Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlWorksheet As Excel.Worksheet
    Dim Range As Excel.Range
    Dim rCnt As Integer
    Dim Obj As Object

    xlApp = New Excel.Application
    xlWorkbook = xlApp.Workbooks.Open(WorkSheetPath)
    xlWorksheet = xlWorkbook.Worksheets("sheet1")

    Range = xlWorksheet.UsedRange
    Dim count As Integer = 0
    For rCnt = 1 To Range.Rows.Count
        Obj = CType(Range.Cells(rCnt, 4), Excel.Range) 'if you change the index of the answer column in your excel file, change the index here as well (it is 4 right now)
        Dim answer As String = Obj.Value.ToString()
        If answer = "Yes" Then
            count += 1
        End If
    Next
    xlWorkbook.Close()
    xlApp.Quit()
    lblTotal.Text = count.ToString()

Hope this helps. ^^

edited Sep 14, 2018 at 7:03

answered Sep 14, 2018 at 5:37

Gabriel Stancu

4,8704 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Tendo Sai Over a year ago

Dim workbook As New ExcelDocument error woth not defined.

Gabriel Stancu Over a year ago

Can you please explain it? I am not sure I understood your error

Tendo Sai Over a year ago

ExcelDocument is not defined.

Gabriel Stancu Over a year ago

Have you imported the Microsoft.Office.Interop.Excel reference where you have this piece of code?

Tendo Sai Over a year ago

isn't it suppose to be Excel.Application? I think

|

Collectives™ on Stack Overflow

Count row with data and specific value from excel in VB.net

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related