Click here to Skip to main content
15,940,921 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hay guys,
I got a little problem using VB.NET, Interop, Excel VBA
I am trying to search an Excel file, testing.xlsx Sheet2 Column H2-H10000
I would like to use a variable as a search parameter for Counting the duplicates.
The only problem is I don’t know how to do this is vb.net with the Interop for Excel.
It would be much appreciated if someone could provide some Snippets or something to help me resolve this problem.
This is just some of the code that I have put together to help you help me :)

VB
Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices

Class Form_Test

    Public MyWorkbook As New Excel.Application
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        MyWorkbook.Sheets("Sheet1").Select() ' This is the current Sheet
        Dim varsearch As String = TextBox10.Text ' The value will need to be searched and counted in an Excel Sheet
        Dim varresult As Integer = CInt(0)  ' This is the resulting number from the Counter

        ' Counter
' THIS is the line of which i have no idea of how to write it.
       varresult = MyWorkbook.Rows(MyWorkbook.ActiveCell.Column).'Count (varsearch) ToString() or something.

        MessageBox.Show("Your Repeating Value Is: " & varresult)

    End Sub
End Class

For example, I have an Excel workbook open with my Application and I want to search for the string “Item1” in Sheet1 Column H2 through H10000. I would like it to display the number it returns in a MessageBox, as I quickly tried to demonstrate in the code above.
I have been trying to get this to work for a while now, its getting a little tiring.

Thanks for reading.
Posted

dim obj as object
obj=new object("Excel.Application")
dim sheet as object
obj.workbooks.open(browse your file)
sheet=obj.worksheets(1)

for a=1 to 20000
if sheet.range("h" & a).value=textbox.text.trim then
msgbox ("Duplicate value")
exit for
end if
next
 
Share this answer
 
For some reason I don't have the OFfice stuff installed So I cant test the code.
Your code looks like your skipping a couple of steps.
First you need to get the column, then compare each cell in the column to the given text if true then add 1 to the counter if false then move to the next cell

Something like
VB
Dim textToSearchfor as string
textToSearchfor = iinputTextbox.text
Dim Counter as Integer
Counter = 0
For each cell in ColumnX
If currcell.Contains(textToSearchfor.tostring)
Counter add1

Next

Mgsbox(counter.tostring)


Hope this is somewhat helpful
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900