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.Sort(New FileComparer())
            For Each sFile As String In clsList
            MessageBox.Show("Please verify that the folder exists and you have access to it. Exiting application now.")
        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
                                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
                            End If

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

                        xlBook = Nothing 'Clears xl sheet from memory
                        'Catch ex As Exception
                        ' xlBook = Nothing
                    End Try
                    GoTo ex
                    MessageBox.Show("Please enter a valid serial number to search.")
                    GoTo ex
                End If
                MessageBox.Show("Please select a file to search.")
                GoTo ex
            End If
            MessageBox.Show("Please enter a valid serial number to search.")
        End If

    End Sub
6 Years
Discussion Span
Last Post by lolafuertes


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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.