Search multiple Excel files loaded into listbox

[devilsden]

Hi Guys, I got two issues here Please help me out
1. I get a list of excel files from a dir into a list box and search for number by select 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 (labels) with result from that particular file. If multiple files are selected that 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.

Please help
here is the code
Loading files

If System.IO.Directory.Exists("directory") Then
            Dim clsList As New ArrayList()
            For Each sFile As String In System.IO.Directory.GetFiles("directory", "*.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("Directory" & 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

[lolafuertes]

Two?

Lets start on point 4:
Remove the () on the line 19: serial = MaskedTextBox1.Text 'Gets the serial number

Point 3:
You only release the xlBook. You need to release also the xlSheet (before releasing the book) and the xlApp (after releasing the book). You don't need to call GC.

Point 1:
Yes you can select multiple files if you set the listbox SelectionMode property to Multiple

Point 2:
If you need to show results for multiple files, I would suggest to change the form presentation, avoiding all the individual fields and replacing them with a ListView.

Each file result can be created in a new ListViewItem and the corresponding ListViewSubItems for the field info to show. On design time you can set the column headers you need.

Hope this helps