Question Updating textbox text from an Excel Spreadsheet.

dl_evans

New member
Joined
Oct 18, 2011
Messages
4
Programming Experience
Beginner
Hello folks! I'm new to VB and I'm working on a project for work. I'm a 911 Dispatcher and currently we have about 5 books of information that we have to go through by hand to check for things. I'm trying to make an app to speed up the process. So far what I've got is this:

I've made a google doc form that people fill out (names, addresses, etc).
That form is then downloaded as an excel spreadsheet.
Those spreadsheets are what I'm searching.

It works pretty well for searching but I'd like to add a few things and I'm stumped.

I'd like to delete a record in the Warrant List (see highlighted, italicized, and underlined text below)
The code I've got deletes the entire spreadsheet. :(

Eventually I'd like to be able to add/edit the spreadsheets (from the network at work) and cut out google docs altogether.

Anything else you need from me just let me know. I can get you a few test spreadsheets and the project folder if it helps. Thanks for taking a look.

VB.NET:
'**************************************************************
'*  this application will allow you to surch through five     *
'*  different excel spreadsheets matching the criteria given  *
'*  written by Nick Alexander and Dustin Evans for the        *
'*  McPherson County Emergency Communications Department      *
'*  October of 2011                                           *
'**************************************************************


Option Compare Text 'allows the search of capital and lowercase letters at once
Imports System
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel 'Microsoft Excel Library


Public Class Form1


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Make all search criteria invisible until selected from the ComboBox
        GroupBox2.Visible = False 'Warrant
        GroupBox1.Visible = False 'Keyholder
        GroupBox6.Visible = False 'PFA
        GroupBox7.Visible = False 'Probation Search
        GroupBox8.Visible = False 'Dog Tag Search
        ComboBox1.Items.Add("Warrant Search")
        ComboBox1.Items.Add("Keyholder Search")
        ComboBox1.Items.Add("PFA Search")
        ComboBox1.Items.Add("Probation Search")
        ComboBox1.Items.Add("Dog Tag Search")
    End Sub


    Private Sub ComboBox1_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged
        'Only search boxes visible when needed
        If ComboBox1.Text = "Warrant Search" Then
            GroupBox2.Visible = True 'Warrant
            GroupBox1.Visible = False 'Keyholder
            GroupBox6.Visible = False 'PFA
            GroupBox7.Visible = False 'Probation Search
            GroupBox8.Visible = False 'Dog Tag Search
        ElseIf ComboBox1.Text = "Keyholder Search" Then
            GroupBox2.Visible = False 'Warrant
            GroupBox1.Visible = True 'Keyholder
            GroupBox6.Visible = False 'PFA
            GroupBox7.Visible = False 'Probation Search
            GroupBox8.Visible = False 'Dog Tag Search
        ElseIf ComboBox1.Text = "PFA Search" Then
            GroupBox2.Visible = False 'Warrant
            GroupBox1.Visible = False 'Keyholder
            GroupBox6.Visible = True 'PFA
            GroupBox7.Visible = False 'Probation Search
            GroupBox8.Visible = False 'Dog Tag Search
        ElseIf ComboBox1.Text = "Probation Search" Then
            GroupBox2.Visible = False 'Warrant
            GroupBox1.Visible = False 'Keyholder
            GroupBox6.Visible = False 'PFA
            GroupBox7.Visible = True 'Probation Search
            GroupBox8.Visible = False 'Dog Tag Search
        ElseIf ComboBox1.Text = "Dog Tag Search" Then
            GroupBox2.Visible = False 'Warrant
            GroupBox1.Visible = False 'Keyholder
            GroupBox6.Visible = False 'PFA
            GroupBox7.Visible = False 'Probation Search
            GroupBox8.Visible = True 'Dog Tag Search
        ElseIf ComboBox1.Text = "Lost/Impounded Dog Search" Then
            GroupBox2.Visible = False 'Warrant
            GroupBox1.Visible = False 'Keyholder
            GroupBox6.Visible = False 'PFA
            GroupBox7.Visible = False 'Probation Search
            GroupBox8.Visible = False 'Dog Tag Search
        End If


    End Sub


    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        'error checking
        'makes sure a list has been chosen 
        'and boxes have been filled out
        If ComboBox1.Text = "Warrant Search" Then
            If TextBox3.Text = "" And TextBox4.Text = "" Or TextBox5.Text = "" Then
                MsgBox("Please fill at least first or last name and directory!", vbCritical, "Error")
                Exit Sub
            Else
                BackgroundWorker1.RunWorkerAsync()
            End If
        ElseIf ComboBox1.Text = "Keyholder Search" Then
            If TextBox1.Text = "" Or TextBox2.Text = "" Then
                MsgBox("Please fill out form entirley", vbCritical, "Error")
                Exit Sub
            Else
                BackgroundWorker2.RunWorkerAsync()
            End If
        ElseIf ComboBox1.Text = "PFA Search" Then
            If TextBox7.Text = "" Then
                MsgBox("Please fill out PFA Directory!", vbCritical, "Error")
            Else
                BackgroundWorker3.RunWorkerAsync()
            End If
        ElseIf ComboBox1.Text = "Probation Search" Then
            If TextBox14.Text = "" Then
                MsgBox("Please fill out a Probation Directory!", vbCritical, "Error")
            Else
                BackgroundWorker4.RunWorkerAsync()
            End If


        ElseIf ComboBox1.Text = "Dog Tag Search" Then
            If TextBox16.Text = "" Then
                MsgBox("Please fill out a Dog Tag Directory!", vbCritical, "Error")
            Else
                BackgroundWorker5.RunWorkerAsync()
            End If
        End If


    End Sub
    Private Sub BackgroundWorker1_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        'search the warrant list
        Dim eAPP As Excel.Application
        Dim eBOOK As Excel.Workbook
        Dim eSHEET As Excel.Worksheet
        Dim files As String() = Directory.GetFiles(TextBox5.Text)
        Dim filename As String
        Dim R As Integer
        Dim iA As Integer
        Dim iB As Integer
        Dim strLike As String
        Dim strLike2 As String
        Dim varWarrant1 As String
        Dim varWarrant2 As String
        Dim varWarrant3 As String
        Dim varwarrant4 As String


        R = 0


        For Each filename In files


            R = R + 1


            If R = 0 Then
                Exit Sub
                MsgBox("No files in directory", vbCritical, "Error")
            End If


            iB = 2


            eAPP = New Excel.Application
            eAPP.Visible = False
            eAPP.DisplayAlerts = False
            eBOOK = eAPP.Workbooks.Open(filename)
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eBOOK.Sheets(1), Excel.Worksheet)
            iA = eSHEET.Rows.End(Excel.XlDirection.xlDown).Row
            ' the * searches anything matching (allows for single characters first/last name)
            strLike = TextBox3.Text + "*"
            strLike2 = TextBox4.Text + "*"


            Do While iB < iA


                If CheckBox1.CheckState = 1 Then  'if box is checked match first AND last name
                    If eSHEET.Cells(iB, 2).value Like strLike And eSHEET.Cells(iB, 3).value Like strLike2 Then
                        varWarrant1 = MsgBox("Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Charged with: " + eSHEET.Cells(iB, 9).value + ControlChars.NewLine + "Bond: $" + eSHEET.Cells(iB, 10).value.ToString + ControlChars.NewLine + "Agency: " + eSHEET.Cells(iB, 7).value + ControlChars.NewLine + "Warrant number: " + eSHEET.Cells(iB, 8).value.ToString + ControlChars.NewLine + "Date issued: " + eSHEET.Cells(iB, 11).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 12).value.ToString + ControlChars.NewLine + "Hotfiles? " + eSHEET.Cells(iB, 13).value + ControlChars.NewLine + "Misc. info: " + eSHEET.Cells(iB, 14).value + ControlChars.NewLine + "Entered by: " + eSHEET.Cells(iB, 15).value, MessageBoxButtons.YesNo, Title:="Match results")
                        If varWarrant1 = vbYes Then
                            varWarrant2 = MsgBox("Press YES to delete the record" + ControlChars.NewLine + "Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Charged with: " + eSHEET.Cells(iB, 9).value + ControlChars.NewLine + "Bond: $" + eSHEET.Cells(iB, 10).value.ToString + ControlChars.NewLine + "Agency: " + eSHEET.Cells(iB, 7).value + ControlChars.NewLine + "Warrant number: " + eSHEET.Cells(iB, 8).value.ToString + ControlChars.NewLine + "Date issued: " + eSHEET.Cells(iB, 11).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 12).value.ToString + ControlChars.NewLine + "Hotfiles? " + eSHEET.Cells(iB, 13).value + ControlChars.NewLine + "Misc. info: " + eSHEET.Cells(iB, 14).value + ControlChars.NewLine + "Entered by: " + eSHEET.Cells(iB, 15).value, MessageBoxButtons.YesNo, Title:="Warrant results.")
                            If varWarrant2 = vbYes Then



[U][I][B]                                'eSHEET.Rows.Delete(iB).ToString()[/B][/I][/U]
[U][I][B]                                'eBOOK.Save()[/B][/I][/U]



                            End If
                        End If
                    End If
                Else                          'else if box isn't checked match first OR last name
                    If eSHEET.Cells(iB, 2).value Like strLike Or eSHEET.Cells(iB, 3).value Like strLike2 Then
                        varWarrant3 = MsgBox("Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Charged with: " + eSHEET.Cells(iB, 9).value + ControlChars.NewLine + "Bond: $" + eSHEET.Cells(iB, 10).value.ToString + ControlChars.NewLine + "Agency: " + eSHEET.Cells(iB, 7).value + ControlChars.NewLine + "Warrant number: " + eSHEET.Cells(iB, 8).value.ToString + ControlChars.NewLine + "Date issued: " + eSHEET.Cells(iB, 11).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 12).value.ToString + ControlChars.NewLine + "Hotfiles? " + eSHEET.Cells(iB, 13).value + ControlChars.NewLine + "Misc. info: " + eSHEET.Cells(iB, 14).value + ControlChars.NewLine + "Entered by: " + eSHEET.Cells(iB, 15).value, MessageBoxButtons.YesNo, Title:="Match results?")
                        If varWarrant3 = vbYes Then
                            varwarrant4 = MsgBox("Press YES to delete the record" + ControlChars.NewLine + "Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Charged with: " + eSHEET.Cells(iB, 9).value + ControlChars.NewLine + "Bond: $" + eSHEET.Cells(iB, 10).value.ToString + ControlChars.NewLine + "Agency: " + eSHEET.Cells(iB, 7).value + ControlChars.NewLine + "Warrant number: " + eSHEET.Cells(iB, 8).value.ToString + ControlChars.NewLine + "Date issued: " + eSHEET.Cells(iB, 11).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 12).value.ToString + ControlChars.NewLine + "Hotfiles? " + eSHEET.Cells(iB, 13).value + ControlChars.NewLine + "Misc. info: " + eSHEET.Cells(iB, 14).value + ControlChars.NewLine + "Entered by: " + eSHEET.Cells(iB, 15).value, MessageBoxButtons.YesNo, Title:="Warrant results.")
                            If varwarrant4 = vbYes Then


[U][I][B]                                ' eSHEET.Rows.Delete(iB).ToString()[/B][/I][/U]
[U][I][B]                                ' eBOOK.Save()[/B][/I][/U]


                            End If
                        End If
                    End If
                End If


                iB = iB + 1


            Loop


            eBOOK.Close()
            eAPP.Application.Quit()
            releaseobject(eAPP)
            releaseobject(eBOOK)


        Next


    End Sub
    Private Sub BackgroundWorker2_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker2.DoWork
        'keyholder search
        Dim eAPP As Excel.Application
        Dim eBOOK As Excel.Workbook
        Dim eSHEET As Excel.Worksheet
        Dim files As String() = Directory.GetFiles(TextBox2.Text)
        Dim filename As String
        Dim R As Integer
        Dim iA As Integer
        Dim iB As Integer
        Dim strLike As String
        Dim varResponse As Object
        Dim varResponse2 As Object


        R = 0


        For Each filename In files


            R = R + 1


            If R = 0 Then
                Exit Sub
                MsgBox("No files in directory", vbCritical, "Error")
            End If


            iB = 2
            'TO DO: add search to more than just the business name 
            'maybe address or names of keyholders
            '*****************************************************
            eAPP = New Excel.Application
            eAPP.Visible = False
            eAPP.DisplayAlerts = False
            eBOOK = eAPP.Workbooks.Open(filename)
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eBOOK.Sheets(1), Excel.Worksheet)
            iA = eSHEET.Rows.End(Excel.XlDirection.xlDown).Row
            'the * searches anything matching (allows for single character search)
            strLike = TextBox1.Text + "*"


            Do While iB < iA
                If eSHEET.Cells(iB, 2).value Like strLike Then 'if search matches anything in the business name field then display it
                    varResponse = MsgBox("Basic information." + ControlChars.NewLine + "Business name: " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Owner: " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 3).value + ControlChars.NewLine + "Phone number: " + eSHEET.Cells(iB, 5).value.ToString, MessageBoxButtons.YesNoCancel, Title:="View full information?")
                    If varResponse = vbYes Then  'if the record is what you want then display its full information
                        varResponse2 = MsgBox("Business name: " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Phone number: " + eSHEET.Cells(iB, 23).value.ToString + ControlChars.NewLine + "Address: " + eSHEET.Cells(iB, 24).value + ControlChars.NewLine + ControlChars.NewLine + "Owner: " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 3).value + ControlChars.NewLine + "Phone number: " + eSHEET.Cells(iB, 5).value.ToString + ControlChars.NewLine + "Alternate: " + eSHEET.Cells(iB, 6).value.ToString + ControlChars.NewLine + "Address: " + eSHEET.Cells(iB, 7).value + ControlChars.NewLine + ControlChars.NewLine + "Keyholder 1: " + eSHEET.Cells(iB, 9).value + " " + eSHEET.Cells(iB, 8).value + ControlChars.NewLine + "Phone number: " + eSHEET.Cells(iB, 10).value.ToString + ControlChars.NewLine + "Alternate: " + eSHEET.Cells(iB, 11).value.ToString + ControlChars.NewLine + "Address: " + eSHEET.Cells(iB, 12).value + ControlChars.NewLine + ControlChars.NewLine + "Keyholder 2: " + eSHEET.Cells(iB, 13).value + " " + eSHEET.Cells(iB, 14).value + ControlChars.NewLine + "Phone number: " + eSHEET.Cells(iB, 15).value.ToString + ControlChars.NewLine + "Alternate: " + eSHEET.Cells(iB, 16).value.ToString + ControlChars.NewLine + "Address: " + eSHEET.Cells(iB, 17).value + ControlChars.NewLine + ControlChars.NewLine + "Keyholder 3: " + eSHEET.Cells(iB, 19).value + " " + eSHEET.Cells(iB, 18).value + ControlChars.NewLine + "Phone number: " + eSHEET.Cells(iB, 20).value.ToString + ControlChars.NewLine + "Alternate: " + eSHEET.Cells(iB, 21).value.ToString + ControlChars.NewLine + "Address: " + eSHEET.Cells(iB, 22).value + ControlChars.NewLine + ControlChars.NewLine + "Dispatch notes: " + eSHEET.Cells(iB, 26).value + ControlChars.NewLine + ControlChars.NewLine + "Misc. Info: " + eSHEET.Cells(iB, 25).value, MessageBoxButtons.OK, Title:="Full list.")
                    End If
                End If


                iB = iB + 1


            Loop


            eBOOK.Close()
            eAPP.Application.Quit()
            releaseobject(eAPP)
            releaseobject(eBOOK)


        Next
    End Sub
    Private Sub BackgroundWorker3_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker3.DoWork
        'PFA List
        Dim eAPP As Excel.Application
        Dim eBOOK As Excel.Workbook
        Dim eSHEET As Excel.Worksheet
        Dim files As String() = Directory.GetFiles(TextBox7.Text)
        Dim filename As String
        Dim R As Integer
        Dim iA As Integer
        Dim iB As Integer
        Dim str1Like As String
        Dim str2Like As String
        Dim str3Like As String
        Dim str4Like As String


        R = 0


        For Each filename In files


            R = R + 1


            If R = 0 Then
                Exit Sub
                MsgBox("No files in directory", vbCritical, "Error")
            End If


            iB = 2


            eAPP = New Excel.Application
            eAPP.Visible = False
            eAPP.DisplayAlerts = False
            eBOOK = eAPP.Workbooks.Open(filename)
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eBOOK.Sheets(1), Excel.Worksheet)
            iA = eSHEET.Rows.End(Excel.XlDirection.xlDown).Row


            'the * searches anything matching (allows for single character search)
            str1Like = TextBox8.Text + "*" 'plaintiff last ib, 2
            str2Like = TextBox9.Text + "*" 'plaintiff first ib, 3
            str3Like = TextBox10.Text + "*" 'defendant last ib, 5
            str4Like = TextBox11.Text + "*" 'defendant first ib, 6


            Do While iB < iA
                If CheckBox4.CheckState = 1 Then ' if box is checked search only plaintiffs
                    If CheckBox2.CheckState = 1 Then 'if box is checked search plaintiffs by first AND last names
                        If eSHEET.Cells(iB, 2).value Like str1Like And eSHEET.Cells(iB, 3).value Like str2Like Then
                            MsgBox("Plaintiff Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Defendant Name: " + eSHEET.Cells(iB, 6).value + " " + eSHEET.Cells(iB, 7).value + " " + eSHEET.Cells(iB, 5).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 8).value + ControlChars.NewLine + "Type: " + eSHEET.Cells(iB, 9).value + ControlChars.NewLine + "Date entered: " + eSHEET.Cells(iB, 16).value + " Expiration date: " + eSHEET.Cells(iB, 11).value + ControlChars.NewLine + "Status: " + eSHEET.Cells(iB, 10).value + ControlChars.NewLine + "Served: " + eSHEET.Cells(iB, 15).value + ControlChars.NewLine + "Misc. Info: " + eSHEET.Cells(iB, 14).value, MessageBoxButtons.OK, Title:="PFA Plaintiff results: ")
                        End If
                    ElseIf CheckBox2.CheckState = 0 Then 'else search plaintiffs by first OR last names
                        If eSHEET.Cells(iB, 2).value Like str1Like Or eSHEET.Cells(iB, 3).value Like str2Like Then
                            MsgBox("Plaintiff Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Defendant Name: " + eSHEET.Cells(iB, 6).value + " " + eSHEET.Cells(iB, 7).value + " " + eSHEET.Cells(iB, 5).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 8).value + ControlChars.NewLine + "Type: " + eSHEET.Cells(iB, 9).value + ControlChars.NewLine + "Date entered: " + eSHEET.Cells(iB, 16).value + " Expiration date: " + eSHEET.Cells(iB, 11).value + ControlChars.NewLine + "Status: " + eSHEET.Cells(iB, 10).value + ControlChars.NewLine + "Served: " + eSHEET.Cells(iB, 15).value + ControlChars.NewLine + "Misc. Info: " + eSHEET.Cells(iB, 14).value, MessageBoxButtons.OK, Title:="PFA Plaintiff results: ")
                        End If
                    End If
                ElseIf CheckBox4.CheckState = 0 Then 'else search defendants only
                    If CheckBox3.CheckState = 1 Then 'if box is checked search defendants by first AND last name
                        If eSHEET.Cells(iB, 5).value Like str3Like And eSHEET.Cells(iB, 6).value Like str4Like Then
                            MsgBox("Defendant Name: " + eSHEET.Cells(iB, 6).value + " " + eSHEET.Cells(iB, 7).value + " " + eSHEET.Cells(iB, 5).value + ControlChars.NewLine + "Plaintiff Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 8).value + ControlChars.NewLine + "Type: " + eSHEET.Cells(iB, 9).value + ControlChars.NewLine + "Date entered: " + eSHEET.Cells(iB, 16).value + " Expiration date: " + eSHEET.Cells(iB, 11).value + ControlChars.NewLine + "Status: " + eSHEET.Cells(iB, 10).value + ControlChars.NewLine + "Served: " + eSHEET.Cells(iB, 15).value + ControlChars.NewLine + "Misc. Info: " + eSHEET.Cells(iB, 14).value, MessageBoxButtons.OK, Title:="PFA Defendant results: ")
                        End If
                    ElseIf CheckBox3.CheckState = 0 Then 'else search defendants by first OR last name
                        If eSHEET.Cells(iB, 5).value Like str3Like Or eSHEET.Cells(iB, 6).value Like str4Like Then
                            MsgBox("Defendant Name: " + eSHEET.Cells(iB, 6).value + " " + eSHEET.Cells(iB, 7).value + " " + eSHEET.Cells(iB, 5).value + ControlChars.NewLine + "Plaintiff Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 8).value + ControlChars.NewLine + "Type: " + eSHEET.Cells(iB, 9).value + ControlChars.NewLine + "Date entered: " + eSHEET.Cells(iB, 16).value + " Expiration date: " + eSHEET.Cells(iB, 11).value + ControlChars.NewLine + "Status: " + eSHEET.Cells(iB, 10).value + ControlChars.NewLine + "Served: " + eSHEET.Cells(iB, 15).value + ControlChars.NewLine + "Misc. Info: " + eSHEET.Cells(iB, 14).value, MessageBoxButtons.OK, Title:="PFA Defendant results: ")
                        End If
                    End If
                End If
                iB = iB + 1


            Loop


            eBOOK.Close()
            eAPP.Application.Quit()
            releaseobject(eAPP)
            releaseobject(eBOOK)


        Next


    End Sub
    Private Sub BackgroundWorker4_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker4.DoWork
        'probation list search
        Dim eAPP As Excel.Application
        Dim eBOOK As Excel.Workbook
        Dim eSHEET As Excel.Worksheet
        Dim files As String() = Directory.GetFiles(TextBox14.Text)
        Dim filename As String
        Dim R As Integer
        Dim iA As Integer
        Dim iB As Integer
        Dim str1Like As String
        Dim str2Like As String


        R = 0


        For Each filename In files


            R = R + 1


            If R = 0 Then
                Exit Sub
                MsgBox("No files in directory", vbCritical, "Error")
            End If


            iB = 2


            eAPP = New Excel.Application
            eAPP.Visible = False
            eAPP.DisplayAlerts = False
            eBOOK = eAPP.Workbooks.Open(filename)
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eBOOK.Sheets(1), Excel.Worksheet)
            iA = eSHEET.Rows.End(Excel.XlDirection.xlDown).Row
            'the * searches anything matching (allows for single character search)
            str1Like = TextBox12.Text + "*"
            str2Like = TextBox13.Text + "*"


            Do While iB < iA


                If CheckBox5.CheckState = 1 Then 'if box is checked search probaton list by first AND last name
                    If eSHEET.Cells(iB, 2).value Like str1Like And eSHEET.Cells(iB, 3).value Like str2Like Then
                        MsgBox("Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Offense: " + eSHEET.Cells(iB, 5).value + ControlChars.NewLine + "CSO: " + eSHEET.Cells(iB, 6).value + ControlChars.NewLine + "County: " + eSHEET.Cells(iB, 7).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 8).value, MessageBoxButtons.OK, Title:="Probation Results")
                    End If
                Else ' else search probation list by first OR last name
                    If eSHEET.Cells(iB, 2).value Like str1Like Or eSHEET.Cells(iB, 3).value Like str2Like Then
                        MsgBox("Name: " + eSHEET.Cells(iB, 3).value + " " + eSHEET.Cells(iB, 4).value + " " + eSHEET.Cells(iB, 2).value + ControlChars.NewLine + "Offense: " + eSHEET.Cells(iB, 5).value + ControlChars.NewLine + "CSO: " + eSHEET.Cells(iB, 6).value + ControlChars.NewLine + "County: " + eSHEET.Cells(iB, 7).value + ControlChars.NewLine + "Case number: " + eSHEET.Cells(iB, 8).value, MessageBoxButtons.OK, Title:="Probation Results")
                    End If
                End If


                iB = iB + 1


            Loop


            eBOOK.Close()
            eAPP.Application.Quit()
            releaseobject(eAPP)
            releaseobject(eBOOK)


        Next


    End Sub
    Private Sub BackgroundWorker5_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker5.DoWork
        'dog tag search
        Dim eAPP As Excel.Application
        Dim eBOOK As Excel.Workbook
        Dim eSHEET As Excel.Worksheet
        Dim files As String() = Directory.GetFiles(TextBox16.Text)
        Dim filename As String
        Dim R As Integer
        Dim iA As Integer
        Dim iB As Integer
        Dim strLike As String


        R = 0


        For Each filename In files


            R = R + 1


            If R = 0 Then
                Exit Sub
                MsgBox("No files in directory", vbCritical, "Error")
            End If


            iB = 2


            eAPP = New Excel.Application
            eAPP.Visible = False
            eAPP.DisplayAlerts = False
            eBOOK = eAPP.Workbooks.Open(filename)
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eBOOK.Sheets(1), Excel.Worksheet)
            iA = eSHEET.Rows.End(Excel.XlDirection.xlDown).Row
            ' no * needed. only search by full tag number so far
            'only allowed to search dog tag numbers
            'TODO: possibly search by dog's name or owner's name?
            '****************************************************
            strLike = TextBox15.Text


            Do While iB < iA
                If eSHEET.Cells(iB, 6).value.ToString Like strLike Then
                    MsgBox("Owner: " + eSHEET.Cells(iB, 15).value + ControlChars.NewLine, MessageBoxButtons.OK, Title:="Owner's information: ")
                End If


                iB = iB + 1


            Loop


            eBOOK.Close()
            eAPP.Application.Quit()
            releaseobject(eAPP)
            releaseobject(eBOOK)


        Next
    End Sub
    Public Sub releaseobject(ByVal obj As Object)
        'once app is closed clean everything up
        'close all the excel documents
        'release all resources
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try


    End Sub


    Private Sub SourceToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SourceToolStripMenuItem.Click
        'pop up browser to locate the list needed
        'shownewfolderbutton = false means no new folders can be created
        'description is what shows at the top of the browser to remind you
        'what it is you're looking for
        'Warrant Dialog
        FolderBrowserDialog1.ShowNewFolderButton = False
        FolderBrowserDialog1.Description = "Locate Warrant List."
        FolderBrowserDialog1.ShowDialog()
        TextBox5.Text = FolderBrowserDialog1.SelectedPath
    End Sub
    Private Sub BusinessListToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BusinessListToolStripMenuItem.Click
        'pop up browser to locate the list needed
        'shownewfolderbutton = false means no new folders can be created
        'description is what shows at the top of the browser to remind you
        'what it is you're looking for
        'keyholder dialog
        FolderBrowserDialog1.ShowNewFolderButton = False
        FolderBrowserDialog1.Description = "Locate Keyholder List."
        FolderBrowserDialog1.ShowDialog()
        TextBox2.Text = FolderBrowserDialog1.SelectedPath
    End Sub
    Private Sub PFAListToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PFAListToolStripMenuItem.Click
        'pop up browser to locate the list needed
        'shownewfolderbutton = false means no new folders can be created
        'description is what shows at the top of the browser to remind you
        'what it is you're looking for
        'PFA dialog
        FolderBrowserDialog1.ShowNewFolderButton = False
        FolderBrowserDialog1.Description = "Locate PFA List."
        FolderBrowserDialog1.ShowDialog()
        TextBox7.Text = FolderBrowserDialog1.SelectedPath
    End Sub
    Private Sub ProbationListToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProbationListToolStripMenuItem.Click
        'pop up browser to locate the list needed
        'shownewfolderbutton = false means no new folders can be created
        'description is what shows at the top of the browser to remind you
        'what it is you're looking for
        'Probation dialog
        FolderBrowserDialog1.ShowNewFolderButton = False
        FolderBrowserDialog1.Description = "Locate Probation List."
        FolderBrowserDialog1.ShowDialog()
        TextBox14.Text = FolderBrowserDialog1.SelectedPath
    End Sub
    Private Sub DogTagListToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DogTagListToolStripMenuItem.Click
        'pop up browser to locate the list needed
        'shownewfolderbutton = false means no new folders can be created
        'description is what shows at the top of the browser to remind you
        'what it is you're looking for
        'dog tag dialog
        FolderBrowserDialog1.ShowNewFolderButton = False
        FolderBrowserDialog1.Description = "Locate Dog Tag List."
        FolderBrowserDialog1.ShowDialog()
        TextBox16.Text = FolderBrowserDialog1.SelectedPath
    End Sub


    Private Sub DirectoriesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DirectoriesToolStripMenuItem.Click
        'menu item View -> Warrant
        'when selected make that directory visible
        GroupBox3.Visible = True  'Warrant
        GroupBox4.Visible = False 'Keyholder 
        GroupBox5.Visible = False 'PFA
        GroupBox10.Visible = False 'Probation
        GroupBox11.Visible = False 'Dog Tag
    End Sub


    Private Sub WarrantDirectoryToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WarrantDirectoryToolStripMenuItem.Click
        'menu item View -> Keyholder
        'when selected make that directory visible
        GroupBox4.Visible = True  'Keyholder
        GroupBox3.Visible = False 'Warrant
        GroupBox5.Visible = False 'PFA
        GroupBox10.Visible = False 'Probation
        GroupBox11.Visible = False 'Dog Tag


    End Sub
    Private Sub PFADirectoryToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PFADirectoryToolStripMenuItem.Click
        'menu item View -> PFA
        'when selected make that directory visible
        GroupBox5.Visible = True 'PFA
        GroupBox3.Visible = False 'Warrant
        GroupBox4.Visible = False 'Keyholder
        GroupBox10.Visible = False 'Probation
        GroupBox11.Visible = False 'Dog Tag
    End Sub
    Private Sub ProbationDirectoryToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProbationDirectoryToolStripMenuItem.Click
        'menu item View -> Probation
        'when selected make that directory visible
        GroupBox3.Visible = False 'Warrant
        GroupBox4.Visible = False 'Keyholder
        GroupBox5.Visible = False 'PFA
        GroupBox10.Visible = True 'Probation
        GroupBox11.Visible = False 'Dog Tag
    End Sub
    Private Sub DogTagDirectoryToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DogTagDirectoryToolStripMenuItem.Click
        'menu item View -> Dog Tag
        'when selected make that directory visible
        GroupBox3.Visible = False 'Warrant
        GroupBox4.Visible = False 'Keyholder
        GroupBox5.Visible = False 'PFA
        GroupBox10.Visible = False 'Probation
        GroupBox11.Visible = True 'Dog Tag
    End Sub


    Private Sub NoneToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NoneToolStripMenuItem.Click
        'menu item View -> None
        'when selected make that directory hidden
        GroupBox3.Visible = False 'Warrant
        GroupBox4.Visible = False 'Keyholder
        GroupBox5.Visible = False 'PFA
        GroupBox10.Visible = False 'Probation
        GroupBox11.Visible = False 'Dog Tag
    End Sub


    Private Sub ToolStripMenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem2.Click
        'menu item Background -> 911
        'when selected changed the background
        Me.BackgroundImage = WindowsApplication1.My.Resources.Resources._911
    End Sub


    Private Sub UmbrellaCorpToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UmbrellaCorpToolStripMenuItem.Click
        'menu item Background -> umbrella corp
        'when selected changed the background
        Me.BackgroundImage = WindowsApplication1.My.Resources.Resources.umbrellacorp
    End Sub


    Private Sub UmbrellaCorp2ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UmbrellaCorp2ToolStripMenuItem.Click
        'menu item Background -> umbrella corp 2
        'when selected changed the background
        Me.BackgroundImage = WindowsApplication1.My.Resources.Resources.umbrellacorp2
    End Sub


    Private Sub SearchIsOnToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchIsOnToolStripMenuItem.Click
        'menu item Background -> the search is on 
        'when selected changed the background
        Me.BackgroundImage = WindowsApplication1.My.Resources.Resources.thesearchison
    End Sub
End Class
 
Tried:
TextBox18.Text = eSHEET.Cells(iB, 2).value.ToString, TextBox18.Text = eSHEET.Cells(iB, 2).value,
eSHEET.Cells(iB, 2).value = TextBox18.Text, and
eSHEET.Cells(iB, 2).value = TextBox18.Text.ToString
none of it worked

and

Tried:
eSHEET.Rows.Delete(iB).ToString()
eBOOK.Save()
and
eSHEET.Rows.Delete(iB).value
eBOOK.Save()
didn't work either.
Well it works but the entire Workbook is deleted.
 
Anyone? Pretty please??

The editing/deleting part of the excel file is the last thing I need to add before this app is AMAZING.
 
Back
Top