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.
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