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

Hello,

You can look the below link for your answer.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23722906.html

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

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?

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

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?

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

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

are you using vb6 or vb.net?

I'm using .net

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.

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

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.

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.

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?