[RESOLVED] Search Excel & return related value

[OhGreen]

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.

[kevininstructor]

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.

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

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.

[OhGreen]

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.

[OhGreen]

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.

[kevininstructor]

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.

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

[OhGreen]

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.

[OhGreen]

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.

Dim searchrefs As OleDbCommand = New OleDbCommand("select * from [InsertVariable]", MyConnection)

[kevininstructor]

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.

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.

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

[fixo]

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

    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

[kevininstructor]

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.

[OhGreen]

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.

Dim searchrefs As OleDbCommand = New OleDbCommand("select * from [A4:A4]", MyConnection)
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:

Dim vCell as String
Dim UserRef as String
 
vCell = [A4:A4]
UserRef = textbox1.text
 
Do until UserRef =  Label1.Text
Dim searchrefs As OleDbCommand = New OleDbCommand("select * from vCell", MyConnection)
Label1.Text = CStr(searchrefs.ExecuteScalar)
vCell = [B4:B4]  'I'll sort progression through the cells myself this is just for an example.
Loop

[kevininstructor]

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

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

[OhGreen]

Brilliant thanks Kevin.

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

ColumnName = Iterator.ExcelColumnName

[kevininstructor]

Brilliant thanks Kevin.

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

ColumnName = Iterator.ExcelColumnName

Did you add the code modules here

here is the signature

Public Function ExcelColumnName(ByVal Index As Integer) As String

[OhGreen]

Edit: Fixed.

[kevininstructor]

Edit: Fixed.

If the solution is good for you please mark this thread resolved.

[OhGreen]

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

Rated & Solved.