Solved

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

Posted on 2009-07-02
15
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 9

Expert Comment

by:Rahul Goel ITIL
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

617 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