Solved

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

Posted on 2009-07-02
15
361 Views
Last Modified: 2013-11-26
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!
0
Comment
Question by:baiowolf
15 Comments
 
LVL 9

Expert Comment

by:Rahul Goel
ID: 24770466
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
 
LVL 7

Expert Comment

by:Seo_Expert
ID: 24770516
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 24770592
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
 

Author Comment

by:baiowolf
ID: 24772406
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
 
LVL 12

Expert Comment

by:ErezMor
ID: 24774294
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
 

Author Comment

by:baiowolf
ID: 24778873
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
 
LVL 12

Expert Comment

by:ErezMor
ID: 24779001
change While .Cells(i, 1) <> ""
to While .Cells(i, 1).Value <> ""
are you using vb6 or vb.net?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:baiowolf
ID: 24780087
I'm using .net
0
 

Author Comment

by:baiowolf
ID: 24780153
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
 

Author Comment

by:baiowolf
ID: 24780989
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
 
LVL 12

Accepted Solution

by:
ErezMor earned 500 total points
ID: 24780992
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
 

Author Comment

by:baiowolf
ID: 24781143
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
 

Author Comment

by:baiowolf
ID: 24781225
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
 
LVL 12

Expert Comment

by:ErezMor
ID: 24786731
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
 

Author Comment

by:baiowolf
ID: 24786828
There are.  I'll fill those with placeholders.  Thanks again for all your help!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now