[RESOLVED] Search multiple Excel files loaded into listbox

[devilsden]

Hi Guys, I got few issues here with my program. Please help me out
1. I get a list of excel files from a dir into a list box and search for a string/number by selecting each files at once. I want to select multiple files at once and search them one by one.
May be using checklistbox.

2. The output is shown in a new window (in labels) with result from that particular file. If multiple files are selected than the output should show result from all files in different labels.

3. Excel program stays open in memory after closing my application.

4. Also I cant get the app to display message "maskedtextbox1.text" not found in the sheet. If I do after line 30 Else messagebox.show "String not found", it goes into loop or displays the message even if the string exists in the excel file.

Please help
here is the code
Loading files

If System.IO.Directory.Exists("c:\temp") Then
            Dim clsList As New ArrayList()
            For Each sFile As String In System.IO.Directory.GetFiles("c:\temp", "*.xls")
                clsList.Add(sFile)
            Next
            clsList.Sort(New FileComparer())
            For Each sFile As String In clsList
                ListBox1.Items.Add(System.IO.Path.GetFileName(sFile))
            Next
        Else
            MessageBox.Show("Please verify that the folder exists and you have access to it. Exiting application now.")
            Me.Close()
        End If

Searching Excel Files (XL file has only 4 sheets)

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim rng As Excel.Range
        Dim orng As Excel.Range
        Dim wc As Integer
        Dim serial As String
        Dim ExcelSheetName As String = ""
        Dim i As Integer
        If IsNumeric(MaskedTextBox1.Text) Then

            xlApp = CreateObject("Excel.Application")
            If ListBox1.SelectedItems.Count <> 0 Then 'Must select a file to search
                If MaskedTextBox1.Text <> "" Then 'Must input text
                    xlBook = xlApp.Workbooks.Open("c:\temp" & ListBox1.SelectedItem)
                    For wc = 1 To 4 'Searches 4 worksheets
                        xlSheet = xlBook.Worksheets(wc)
                        rng = xlSheet.Range("a1:a100") 'Search for range upto
                        serial = (MaskedTextBox1.Text) 'Gets the serial number
                        For i = 1 To rng.Count
                            If rng.Cells(i).Value = serial Then
                                Me.Hide()
                                
                                infoform.fault.Text = rng.Cells(i).offset(0, 2).value() 'Gets the fault information from XL sheet
                                infoform.Text = rng.Cells(i).offset(0, 0).value() 'Gets the serial number from XL sheet
                                infoform.filename.Text = xlBook.Name 'Gets the current filename
                                orng = xlSheet.Range("A1")
                                infoform.status.Text = orng.Value 'Gets the current status from the XL sheet
                                infoform.Show()
                            End If

                        Next i
                    Next
                    xlBook.Close() 'Closed the current sheet
                    xlApp.Quit() 'Quits the search process

                    Try
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
                        xlBook = Nothing 'Clears xl sheet from memory
                        'Catch ex As Exception
                        ' xlBook = Nothing
                    Finally
                        GC.Collect()
                    End Try
                    GoTo ex
                Else
                    MessageBox.Show("Please enter a valid serial number to search.")
                    GoTo ex
                End If
            Else
                MessageBox.Show("Please select a file to search.")
                GoTo ex
            End If
        Else
            MessageBox.Show("Please enter a valid serial number to search.")
        End If

ex:
    End Sub

[dglienna]

First, WELCOME to the forums!

1) You don't need LINE NUMBERS here.
2) Please use CODE TAGS so we can read your code.

' Like This

3) You can EDIT your first post, and replace the CODE

[devilsden]

I thought of using checkedlistbox instead of listbox where I can select the files I want to search but I cant think of the code... I am new to programming

[dglienna]

Use a HIDDEN LISTBOX as the second object. (It can be set to SORT AUTOMATICALLY)

[devilsden]

I use this function for sorting

  Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
            Dim dtDateX As DateTime = System.IO.File.GetCreationTime(x.ToString())
            Dim dtDateY As DateTime = System.IO.File.GetCreationTime(y.ToString())
            Return dtDateY.CompareTo(dtDateX)
        End Function