Is it possible to create VB form to search Excel file and return results?

I have an excel file composed of 3 sheets (could be combined into one) that is being used for donated inventory tracking.  It details the names of the donors, and then in other cells goes on to include info about the title of the item donated, the date it was donated, and so on.

Is it possible to make a VB form that could search the excel file by first and/or last name and display returned matches (partial or whole), along with the corresponding data from the other cells?

Example:  Search for "willis" returns

Name: Willis, Bruce
Title: Bruno album
Date: 10/22/90

Also, if there's an easier way to accomplish this, with or without VB, please speak up.  I'm a total newb at VB, but it's the only tool I have at my disposal that I have any experience with.

Thanks!
baiowolfAsked:
Who is Participating?
 
ErezMorConnect With a Mentor Commented:
ok
this code run from a button click event on a windows form that has a datagridview for results (3 columns Name, Title, Date) named "dgvResults", a text box for the name searched called "txtName" and the above button, and it works on the excel file gave you so no excuses this time.
in ths implementation you do not need to add references to excel cause it's late bound (declared inside the code) but you will need excel on the target machine

Dim i As Integer = 1
Dim NameFound As Boolean = False
Dim xlApp As Object
 
dgvResults.Rows.Clear()
xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open("C:\My ComApps\userformsample.xls")
With xlApp.ActiveWorkbook.Sheets("Sheet1")
     While .Cells(i, 1).value <> ""
          If InStr(1, .Cells(i, 1).Value, txtName.Text) > 0 Then
               Dim dgvRow As String() = {.Cells(i, 1).Value, .Cells(i, 2).Value, .Cells(i, 3).Value}
               dgvResults.Rows.Add(dgvRow)
               NameFound = True
          End If
          i = i + 1
     End While
     If NameFound = False Then MsgBox("The requested name wasnt found in donors table")
End With
xlApp.application.quit()
xlApp = Nothing

Open in new window

0
 
Rahul Goel ITILSenior Consultant - DeloitteCommented:
yes you can use that using ODBC connection using VB.Net and C# both
http://www.c-sharpcorner.com/UploadFile/mgold/Query2Excel12032005011029AM/Query2Excel.aspx
0
 
Seo_ExpertCommented:
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ErezMorCommented:
do you mean a vb6 form or vba user-form (from within excel?
beacuse that would change the actual access to the excel fiel (if you're inside the excel file, then no references and Open command are needed)
so for now i'll skip the vb6 and assume vba from within the open excel file
take a look at the user form inthe vba section of the attached file
you can see there that any sheetcan be referenced and searched, and i use cells(row,col) instead of the Range(..) method cause it's easier to program with
hope that's what you asked for

userFormSample.xls
0
 
baiowolfAuthor Commented:
ErezMor -

Thanks very much for the provided sample; it was very helpful.  I am looking for a solution that would work from outside Excel, though.  The issue is that we want people to be able to search the spreadsheet if they already have an idea of the name they're looking for, but not necessarily have access to the spreadsheet itself.  Also, would it be possible return results from partial search matches?  So, in the example you provided, searching for "maya" or "may" would both return the results for maya?  That also makes me wonder, how could multiple results be handled?  If there's both a "maya" and a may" in the spreadsheet, and the search for "may" returns both results, what would be the best way to handle the display of the results?
0
 
ErezMorCommented:
hello again
so moving the code to an independant vb is not hard, you need to go to references in your vb application and find "Microsoft excel ..."
add the necessary controls as it was in the user form, then use the code attached here in the search button click
look at the difference in the code to allow for partial matches (use of InStr function instead of equation), and the ability to keep searching (by hitting the "Search" button again, beacuse i variable turned to static it keeps looking down the spreadsheet as long as the value search has not been changed)
this is just sticking with the original structure, you might consider returning a whole table of results and not the 1 by 1  solution here.
or even connection by ADO or DAO and returning a recordset (which you can display as a datagrid)

Static i As Integer
Static strSearchTerm As String
Dim NameFound As Boolean
Dim strResult As String
Dim xlApp As New Excel.Application
 
xlApp.Workbooks.Open "file and path to the excel spreadsheet here"
If strSearchTerm <> txtName.Text Then
    i = 1
    strSearchTerm = txtName.Text
End If
With xlApp.ActiveWorkbook.Sheets("Sheet1")
    While .Cells(i, 1) <> "" And NameFound = False
        If InStr(1, .Cells(i, 1).Value, txtName.Text) > 0 Then
            NameFound = True
            strResult = "Name: " & .Cells(i, 1).Value & vbCrLf
            strResult = strResult & "Name: " & .Cells(i, 2).Value & vbCrLf
            strResult = strResult & "Name: " & .Cells(i, 3).Value & vbCrLf
            txtResult.Text = strResult
        End If
        i = i + 1
    Wend
    If NameFound = False Then
        MsgBox "The requested name wasnt found in donors table"
        i = 1
    End If
End With

Open in new window

0
 
baiowolfAuthor Commented:
Thanks so much for all the help, but I can't seem to get your provided code to work, and I don't have the knowledge to troubleshoot it  When I start debugging the form pops up fine, but after clicking the "search" button, I get an "InvalidCastException was unhandled" error, with the following details:

Overload resolution failed because no Public '<>' can be called with these arguments:     'Public Shared Operator <>(a As String, b As String) As Boolean':         Argument matching parameter 'a' cannot convert from '__ComObject' to 'String'.

Am I missing something?
0
 
ErezMorCommented:
change While .Cells(i, 1) <> ""
to While .Cells(i, 1).Value <> ""
are you using vb6 or vb.net?
0
 
baiowolfAuthor Commented:
I'm using .net
0
 
baiowolfAuthor Commented:
The switch from While .Cells(i, 1) <> ""

to

While .Cells(i, 1).Value <> ""

Gives the error:
Conversion from string "" to type 'Double' is not valid.
0
 
baiowolfAuthor Commented:
Okay, fixed the above error.  I made some minor changes to the code to accommodate the way the source spreadsheet is set up (the vital info I want returned upon a successful search is found in the 2nd, 3rd, 4th, and 10th columns only, with the names being located in the 2nd column).  No crashes, no errors.  The app just can't find anything I search for.  Always returns "no results found."  Any ideas?  I'm including my code:
Static i As Integer
        Static strSearchTerm As String
        Dim NameFound As Boolean
        Dim strResult As String
        Dim xlApp As New Excel.Application
 
        xlApp.Workbooks.Open("c:\\keyindex\KeyIndexMaster.xls")
        xlApp.Visible = False
        If strSearchTerm <> txtName.Text Then
            i = 1
            strSearchTerm = txtName.Text
        End If
        With xlApp.ActiveWorkbook.Sheets("Sheet1")
            While .Cells(i, 2).Value <> "" And NameFound = False
                If InStr(1, .Cells(i, 2).Value, txtName.Text) > 0 Then
                    NameFound = True
                    strResult = "Donor Name: " & .Cells(i, 2).Value & vbCrLf
                    strResult = strResult & "Key Title: " & .Cells(i, 3).Value & vbCrLf
                    strResult = strResult & "Date Donated: " & .Cells(i, 4).Value & vbCrLf
                    strResult = strResult & "Notes: " & .Cells(i, 10).Value & vbCrLf
                    txtResult.Text = strResult
                End If
                i = i + 1
            End While
            If NameFound = False Then
                MsgBox("The requested name was not found")
                i = 1
            End If
        End With
    End Sub

Open in new window

0
 
baiowolfAuthor Commented:
Okay, just tried the new code, and it's working great.  Only question left: is there any way to make the search not case-sensitive?  With your code, searching for "May" returns results for "Maya," which is perfect, but searching for "may" with a lower-case m returns no results.
0
 
baiowolfAuthor Commented:
Another interesting development:  When I try the new code with my spreadsheet, it will only return results for a couple of specific searches, and even then it's not returning all the results, just one at a time.
0
 
ErezMorCommented:
to ignore case, change the istr function to
InStr(1, .Cells(i, 1).Value, txtName.Text, CompareMethod.Text)
about your excel file, i cant really help without seeing it, remember that the code i gave you determines the end of the database by finding a blank cell in row 1. are there blank cells inside your table?
0
 
baiowolfAuthor Commented:
There are.  I'll fill those with placeholders.  Thanks again for all your help!
0
All Courses

From novice to tech pro — start learning today.