Results 1 to 17 of 17

Thread: [RESOLVED] Search Excel & return related value

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2011
    Posts
    278

    Resolved [RESOLVED] Search Excel & return related value

    Hello,

    I've been doing some searching and so far only found related posts for previous versions of VB .

    Essentially all I am wanting to do is have a user form where you enter a reference number, hit enter and it searches an excel 2010 spreadsheet for that reference number.

    If the reference number is found it returns the associated name which is in the cell in the column on the right. (e.g. ref = A1 & name = B1).

    Thanks in advance,

    Chris.

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Search Excel & return related value

    Here are bits and pieces that when put together correctly should allow you to get what you are after. The idea is to read a worksheet using OleDb and read sheets into DataTable object or read a range back i.e.

    Code:
    Sheet1$A3:B3
    A working example where you will need to alter the data provider
    http://www.vbforums.com/showpost.php...05&postcount=4

    For your data provider: Microsoft.ACE.OLEDB.12.0
    In the connection string ConnectionNoHeader you may need to tweak the extended properties. I would run my project and see how things work prior to using it in your project.

    Aother note, in my project the following gets data back
    Code:
    select F1 As Column1, F2 As Whatever, F3 As LastCol from [Sheet3$A3:D100]
    The sheet does not have headers so if we do not alias them for display purposes they will have headers of F1, F2 etc.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2011
    Posts
    278

    Re: Search Excel & return related value

    Your example seems to return an entire grid rather than making a comparison with a user entered value and a value already existing within a database.

    As it stands I have a successful OleDb connection to my .xls but can't compare/extract any data from it.

    Even if I got to the stage where I could have a boolean result determining if the field existed I reckon I could figure it out from there.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2011
    Posts
    278

    Re: Search Excel & return related value

    Also, when I do manage to search the spreadsheet for a value.. would it just be best to create a loop or is there another option?

    There's going to be a massive amount of data within this sheet so I'm wondering what sort of speed this is going to run at.

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Search Excel & return related value

    What I gave you is essentially the building blocks for working with Excel worksheet data.

    You can read an entire sheet very fast using OleDb.

    Example, a worksheet with say 3,000 rows by 20 columns should execute in less than two seconds.

    Once you have the data back but the returning data into a DataTable then the DataTable becomes the DataSource of a BindingSource.
    The BindingSource allows you to search, return the row number which then knowing the column you search on can convert the row/column into Excel format i.e. A3. Depending on how you perform a search, just one column or mutiple columns the operation using OleDb is fast, split second fast, unless you use a poor method of searching which would be hard to do.

    In regards to doing a compare, no I do not show how to but as mentioned above provide building blocks for you to run with, expand on.

    A good source of reference http://www.codeproject.com/KB/miscct...ta_access.aspx


    Code for converting a numeric to alpha for providing the cell in Excel format.
    Code:
    Public Module ExcelExtensions
        <System.Diagnostics.DebuggerStepThrough()> _
        <System.Runtime.CompilerServices.Extension()> _
        Public Function ExcelColumnName(ByVal Index As Integer) As String
            Dim chars = New Char() _
                { _
                    "A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c, _
                    "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, _
                    "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c _
                }
    
            Index -= 1
    
            Dim columnName As String
    
            Dim quotient = Index \ 26
    
            If quotient > 0 Then
                columnName = ExcelColumnName(quotient) + chars(Index Mod 26)
            Else
                columnName = chars(Index Mod 26).ToString()
            End If
    
            Return columnName
    
        End Function
    
    End Module

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2011
    Posts
    278

    Re: Search Excel & return related value

    Thanks for that link, really helpful article.

    I've actually managed to use your original post to piece together what I need, just working on moving through the rows and comparing variables.

    Quick question, I've read about an Excel function Range.Find which returns the number of rows used. Just wondering if this is usable from VB or would it make more sense to just loop until the cell = ""?

    Thanks for all the help, really appreciated.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2011
    Posts
    278

    Re: Search Excel & return related value

    Following on from the previous question, could I make use of the row feature you have included in that sample?

    The only problem I foresee is the fact that I'm not using a range of fields like you have, instead I'm specifying a cell.

    Also with the line below, how can I make the From [] into a variable name? I need to be able to change it in order to be able to loop through the rows.

    vb Code:
    1. Dim searchrefs As OleDbCommand = New OleDbCommand("select * from [InsertVariable]", MyConnection)

  8. #8
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Search Excel & return related value

    I whipped together an example which is attached in VS2008. The code featured below does not directly solve your task but provide methods to work with to put together your own solution. Also note by whipping a project together there may be extra/unneeded code as that is what whipping together is about but most important it works.

    Points of interest.

    The connection object is form level as this is better than creating a new connection each time you open the data.

    This example searchs a worksheet which the first row has headers, searchs the column LastName for Smith. Note how the first column and the variable for the where clause uses embedded expressions which would allow you to do replacement parms. One column is hidden from view in the DataGridView as we do not need to see it.
    Code:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim LastName As String = "Smith"
    
        Dim cmd As New OleDbCommand
        Dim dr As System.Data.IDataReader
    
        If cn.IsClosed Then
            cn.Open()
        End If
    
        cmd = New OleDbCommand()
        cmd.Connection = cn
        cmd.CommandText = _
        <SQL>
            SELECT 
                <%= CustomerIdentifier %>, 
                FirstName, 
                LastName 
            FROM 
                [Customers$] 
            WHERE 
                LastName="<%= LastName %>"
        </SQL>.Value
    
        Dim dtData As New DataTable
        dr = cmd.ExecuteReader
        dtData.Load(dr)
        dtData.Columns(CustomerIdentifier).ColumnMapping = MappingType.Hidden
        bsCustomers.DataSource = dtData
    
        DataGridView1.DataSource = bsCustomers
        DataGridView1.Columns("FirstName").HeaderText = "B"
        DataGridView1.Columns("LastName").HeaderText = "C"
    
    End Sub
    The following example loads a worksheet, first searches on first name column the the last name column. Note how the positioning is determined in that we need to increment the position an item is located because the BindingSource is zero based and we must compensate for the header row.

    Code:
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    
        Dim cmd As New OleDbCommand
        Dim dr As System.Data.IDataReader
    
        If cn.IsClosed Then
            cn.Open()
        End If
    
        cmd = New OleDbCommand()
        cmd.Connection = cn
        cmd.CommandText = _
        <SQL>
            SELECT <%= CustomerIdentifier %>, FirstName, LastName FROM [Customers$]
        </SQL>.Value
    
        Dim dtData As New DataTable
        dr = cmd.ExecuteReader
        dtData.Load(dr)
        dtData.Columns(CustomerIdentifier).ColumnMapping = MappingType.Hidden
        bsCustomers.DataSource = dtData
    
        ListBox1.Items.Clear()
    
        Dim Position As Integer = bsCustomers.Locate("LastName", "Smith")
        If Position <> -1 Then
            '
            ' We add +2 for position because we need 1 for minus header and
            ' the second 1 for the BindingSource is zero based
            '
            ListBox1.Items.Add(String.Format("Smith was located at C{0}", Position + 2))
        End If
    
        Position = bsCustomers.Locate("FirstName", "Kevin")
        If Position <> -1 Then
            ListBox1.Items.Add(String.Format("Kevin was located at B{0}", Position + 2))
        End If
    
    End Sub
    Attached Files Attached Files

  9. #9
    Member fixo's Avatar
    Join Date
    Aug 2011
    Location
    SPb, Russia
    Posts
    45

    Re: Search Excel & return related value

    Quote Originally Posted by OhGreen View Post
    Thanks for that link, really helpful article.

    I've actually managed to use your original post to piece together what I need, just working on moving through the rows and comparing variables.

    Quick question, I've read about an Excel function Range.Find which returns the number of rows used. Just wondering if this is usable from VB or would it make more sense to just loop until the cell = ""?

    Thanks for all the help, really appreciated.
    You could be use native Excel methods as well, e.g

    'Require Reference to Microsoft Excel XX.X ObjectLibrary
    Code:
        Imports System.IO
        Imports Microsoft.Office.Interop.Excel
        Imports Excel = Microsoft.Office.Interop.Excel
        Imports System.Threading
        Imports System.Globalization
        '------------------------------
        'Usage
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim result As String() = testFind("C:\myXlFile.xls", TextBox1.Text, "A")
            MsgBox(result(0) & vbLf & result(1))
        End Sub
    
        Public Function testFind(ByVal xlfileName As String, ByVal strToSearh As String, ByVal colName As String) As String()
    
            Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US")
            Dim result() As String = New String(1) {}
            Dim xlApp As Excel.Application = Nothing
            Dim xlBooks As Excel.Workbooks = Nothing
            Dim xlBook As Excel.Workbook = Nothing
            Dim xlSheet As Excel.Worksheet = Nothing
            Dim xlRange As Excel.Range = Nothing
            Dim xlFindRange As Excel.Range = Nothing
            Dim missing As Object = Type.Missing
            Try
    
                xlApp = New Excel.Application()
                xlApp.Visible = True
                xlApp.DisplayAlerts = False
                xlApp.UserControl = True
                xlBooks = DirectCast(xlApp.Workbooks, Excel.Workbooks)
                'Change full path of Excel file here:
                xlBook = DirectCast(xlBooks.Open(xlfileName, True, False, missing, "", missing, _
                 False, missing, missing, True, missing, missing, missing, missing, missing), Excel.Workbook)
                'get first sheei in the workbook
                xlSheet = DirectCast(xlBook.Worksheets.Item(1), Excel.Worksheet)
                'get used range of certain sheet
                xlRange = DirectCast(xlSheet.UsedRange, Excel.Range)
    
                ''  get colunmn "A" of this range
                xlRange = DirectCast(xlRange.Columns(colName), Excel.Range)
    
                xlFindRange = DirectCast(FindInColumn(xlRange, strToSearh, Nothing, _
                                                    Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, _
                                                    Microsoft.Office.Interop.Excel.XlLookAt.xlWhole), Excel.Range)
                If xlFindRange Is Nothing Then
                    MsgBox("Referenced value was not found")
                End If
                result(0) = xlFindRange.Value.ToString()
                result(1) = xlFindRange.Offset(0, 1).Value.ToString()
                DirectCast(xlBook, Excel._Workbook).Close(True, missing, missing) ''<-- see here
    
                releaseObject(xlRange)
                releaseObject(xlSheet)
                releaseObject(xlBook)
                releaseObject(xlBooks)
    
    
            Catch ex As Exception
                MsgBox("Error:" & vbLf & ex.Message & "Trace: " & vbLf & ex.StackTrace)
            Finally
                xlApp.Quit()
                releaseObject(xlApp)
            End Try
            Return result
        End Function
    
        ' autor unknown
        Function FindInColumn(ByVal rng As Excel.Range, _
                            Optional ByVal what As String = "*", _
                            Optional ByVal after As Excel.Range = Nothing, _
                            Optional ByVal lookin As Microsoft.Office.Interop.Excel.XlFindLookIn = Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, _
                            Optional ByVal lookat As Microsoft.Office.Interop.Excel.XlLookAt = Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, _
                            Optional ByVal order As Microsoft.Office.Interop.Excel.XlSearchOrder = Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, _
                            Optional ByVal direction As Microsoft.Office.Interop.Excel.XlSearchDirection = Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, _
                            Optional ByVal matchcase As Boolean = False) As Excel.Range
    
            If after Is Nothing Then
                after = rng.Cells(1, 1)
            End If
    
            Return rng.Find(What:=what, _
                                              After:=after, _
                                              LookIn:=lookin, _
                                              LookAt:=lookat, _
                                              SearchOrder:=order, _
                                              SearchDirection:=direction, _
                                              MatchCase:=matchcase)
        End Function
    
        Public Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
                obj = Nothing
            Catch ex As System.Exception
    
                System.Diagnostics.Debug.Print(ex.ToString())
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub

  10. #10
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Search Excel & return related value

    Personally I have a problem with native Excel methods but Fixo is correct in that this is a valid way to do this.

    My first choice is using Aspose Cells which does not require any references or even have Excel installed for that matter. This is what I use for work but for many the cost is too much to justify.

    Second choice is OleDb simple because other than no ability to format data the only other issue is not being able to compile on a 64bit OS as 64bit since 64bit OS do not have OleDb provider to Office but do have it for 32bit so a project must be compiled as 32bit.

    Last option is Office automation because when upgrading from one office package to another can break your code plus it requires more code than OleDb to read/write data (again without the ability to format data)

    In regards to formatting data and using OleDb a smart idea is to create your Excel file using a preformatted template, same with Aspose Cells although Aspose Cells can format easily anything you want within a workbook.

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2011
    Posts
    278

    Re: Search Excel & return related value

    I've got to the point now where I'm not progressing any further and so I'll try and keep this concise to see if anyone can reply with a direct answer.

    The SQL segment I have at the moment returns a value I specify by directly typing it into the SQL as below shows.

    vb Code:
    1. Dim searchrefs As OleDbCommand = New OleDbCommand("select * from [A4:A4]", MyConnection)
    2. Label1.Text = CStr(searchrefs.ExecuteScalar)

    I want to be able to create a variable which I can use in the SQL rather than [A4:A4]. This would allow me to change the variable in a loop to progress through the list so that it compares each value in the spreadsheet to the one that the user has entered.

    For example:

    vb Code:
    1. Dim vCell as String
    2. Dim UserRef as String
    3.  
    4. vCell = [A4:A4]
    5. UserRef = textbox1.text
    6.  
    7. Do until UserRef =  Label1.Text
    8. Dim searchrefs As OleDbCommand = New OleDbCommand("select * from vCell", MyConnection)
    9. Label1.Text = CStr(searchrefs.ExecuteScalar)
    10. vCell = [B4:B4]  'I'll sort progression through the cells myself this is just for an example.
    11. Loop
    Last edited by OhGreen; Aug 23rd, 2011 at 07:57 AM.

  12. #12
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Search Excel & return related value

    You can loop in a Do-While or For-Next where the For-Next is demoed below.

    Code:
    Private Sub Demo()
    
        Dim Statement As String = ""
        Dim RowIndex As Integer = 4
        Dim ColumnName As String = ""
    
        For Iterator As Integer = 1 To 62
            ColumnName = Iterator.ExcelColumnName
    
            Statement = String.Format("SELECT * FROM {0}", _
                        String.Format("[{0}{1}:{0}{1}]", _
                                      ColumnName, _
                                      RowIndex))
    
            Console.WriteLine("{0,3} {1}", Iterator, Statement)
        Next
    
    End Sub

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2011
    Posts
    278

    Re: Search Excel & return related value

    Brilliant thanks Kevin.

    I'm getting an error ('ExcelColumnName' is not a member of 'Integer'.) for the below line:

    vb Code:
    1. ColumnName = Iterator.ExcelColumnName

  14. #14
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Search Excel & return related value

    Quote Originally Posted by OhGreen View Post
    Brilliant thanks Kevin.

    I'm getting an error ('ExcelColumnName' is not a member of 'Integer'.) for the below line:

    vb Code:
    1. ColumnName = Iterator.ExcelColumnName
    Did you add the code modules here

    here is the signature
    Code:
    Public Function ExcelColumnName(ByVal Index As Integer) As String

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2011
    Posts
    278

    Re: Search Excel & return related value

    Edit: Fixed.
    Last edited by OhGreen; Aug 24th, 2011 at 08:28 AM.

  16. #16
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Search Excel & return related value

    Quote Originally Posted by OhGreen View Post
    Edit: Fixed.
    If the solution is good for you please mark this thread resolved.

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2011
    Posts
    278

    Re: Search Excel & return related value

    Thanks for the help Kevin, I've got this up and running now.

    Rated & Solved.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width