Solved

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

Posted on 2009-07-02
15
364 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSL https .net web site force redirect 3 34
Hiding column macro 10 28
Excel Score Formula 5 47
Excel 2010 Text Formatting placing a hyphen in front of text 3 20
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

912 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

25 Experts available now in Live!

Get 1:1 Help Now