Find how often a value exists in Excel sheet

[AzzKickah]

I'm currently developing a utility for my job with which you can check how often a certain date or name or whatever is in an Excel-sheet.

I have searched the Visual Basic Express help, this forum, internet and other Programming forums for the Range.Find and FindNext methods but I just cannot find the right solution for my problem and so I just cannot get it to work.

Who can just give me an example of how to count how often a value exists? Example: I want to know how often the date 4/6/2009 is in an Excel-sheet. What is the code for this??

Something I had so far:

  Private Sub FindDatum(ByVal TeZoekenDatum As String)
        Dim ExcelProg As Object = CreateObject("Excel.Application")
        Dim TicketFile As Object = ExcelProg.Workbooks.Open(OpenFileDialog1.FileName)
        Dim TicketList As Object = TicketFile.Worksheets(1)
        Dim Aantal As Integer
        Dim FoundMatches As Integer
        Dim misValue As Object = System.Reflection.Missing.Value

        For Aantal = 1 To FoundMatches
            TicketList.Columns("E").Find(TeZoekenDatum, misValue, misValue, misValue, misValue, misValue, misValue, misValue)
            TicketList.Columns("E").FindNext(TeZoekenDatum)
            FoundMatches = FoundMatches + 1
        Next

        FoundMatches = FoundMatches - 1
        Label5.Text = FoundMatches
    End Sub

Who can just give me an example of how to count how often a value exists?Any help would be really appreciated!!

 

[AzzKickah]

Anybody please?

I'm convinced that the solution must be something close to what I already have!
But I've been trying and searching the whole day but NOWHERE to find a solution to my question! Like nobody ever had the same issue than me?

How to determine how many times a value is present in an Excel-worksheet?

 

[GendoIkari]

Have you tried using ODBC? You can open the excel spreadsheet like a database table and simply run a count query on it.

Or, just using what you have; it seems you need to change your loop conditions. A For Loop isn't correct. Try something like this:

    Set c = TicketList.Find(TeZoekenDatum, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            FoundMatches = FoundMatches + 1
            Set c = TicketList.FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    Label5.Text = FoundMatches

Mostly taken from:

FindNext Method [Excel 2003 VBA Language Reference]

 

[AzzKickah]

Hey GendoIkari,

yes I found numerous VBA examples on the internet.
But that won't work in VB.NET
If that worked I would've solved it myself by now.
And what does that .Address thing do? Where does it come from?

See:

And when I change that code so I don't get errors anymore before compiling, I get this at runtime:

 

[AzzKickah]

Anyone on this?
I picked up my programming-hobby again and wanted to try to get further with this.

But I'm still unable to determine how often a certain date exists in an Excel-sheet.

Also, when I found a certain date in a specific row, I have to check if another value exists in that row (like 'Closed' or 'Assigned').

Any help would be very much appreciated!

 

[Paszt]

I would do as GendoIkari suggests and load the worksheet into a Dataset/DataTable. But if that worked, I guess you would've solved it yourself by now .

But seriously; if you know the structure of the excel sheet, I think that is the best solution. Even if you don't know the structure.

 

[AzzKickah]

Hey Paszt, thanks for your reply!

I have taken a look at the DataSet component, but I don't know how to load an Excel-file into it..?
I guess my knowledge is far too little to be able to do what I want..

Any hints that can push me in the right direction?

 

[Paszt]

I just posted an example of importing an excel spreadsheet. Here's the post: Import Excel Spreadsheet to DataSet