Business Analytics Programming
Contents
Introduction to VBA application. 3
Introduction to VBA Application
The following section seeks to: outline and introduce the VBA application, evidence the reasons for its creation and state its core objectives.
Testy Taffy, the company has been in business for almost 100 years. The company was known as the Candy Company until 1990 when the company changed its name to Company to reflect its decision to discontinue all of its product lines other than taffy. Since 1990, Company has positioned itself as a leader in gourmet taffy. The company primarily does business in the US, although in recent years it has picked up some key international accounts. The company prides itself in using the finest ingredients and in processing the taffy in a manner that keeps the product fresh and soft. Company currently produces over 60 flavours and a variety of standard assortments.
For my project, I created a system that will help the company to manage customer complaints. The system uses Microsoft Excel as the user interface and Microsoft Access to store the data. When a complaint is received, the employee receiving the complaint will open the application by clicking on the file on his or her desktop. The employee will enter the required information, such as Customer Number, date of the complaint, Invoice Number, Item Number, complaint category etc along with the customer’s description of the complaint. The employee also has access to a chart summarizing historical complaints in case he or she needs to reference that information.
After the employee records the complaint, an email is sent to the appropriate individual(s) in the company. The email includes the complaint information along with a chart showing historical complaints.
When the individual receives the email, he or she will take the necessary action to resolve the complaint. Once the complaint is resolved, the employee will record the resolution in the customer complaint system. At the end of each week, the sales manager will use a report to review all complaints that have not been resolved within 5 days. He will also review complaints that have been resolved and change the status of the complaints from open to closed.
The customer complaint system will help the company to better understand complaint trends by providing a centralized location to store all complaint data. The system will further help the company by creating an instantaneous way for employees to be made aware of complaints that resulted from process failures in their respective departments. Additionally, the system will provide a framework for holding the respective managers accountable for resolving process failures that led to the complaint. Finally, the system provides a set of reports to enable employees to quickly view complaint data and to follow up on unresolved items.
Impact on Business
Firstly, this application is aimed to have a standard centralized mechanism to handle and resolve customer complaints. As the company would not gain much from using this application, and should perhaps consider using available sources with no additional capital or cost required hence to use Microsoft Excel and Microsoft Access for their greater requirements. However, this application has 2 major advantages:
Higher efficiency
Manually communicating with the customer can be a demanding job. With this application, viewing their complaints and working on them within just a few clicks.Sending them the acknowledgement of the complaint, updating post-resolution will help employees to work quickly.
Customer Satisfaction
Customer’s satisfaction gets improved by getting know that their complaints are getting noticed and also are getting resolved so that they can revisit which help the business growth.
User Manual
The sections that follow describe the functionality of the complaint system. The functionality of the system is broken into three sections: entering complaints, editing a complaint, and viewing reports.
Entering a Complaint
When the employee opens the program to record a complaint, the program generates a historical chart that users can view either when entering a complaint or when looking at the historical complaint report.
Once the program is open, the user can open the form to record the complaint by clicking either the record new complaint button on the ribbon or by clicking the top button displayed on the spreadsheet as illustrated below
Upon clicking the button, the program loads the list of customers into the complaint form, sets up the list of potential email recipients, generates worksheets that will temporarily store the complaint data, and retrieves a unique complaint number from the customer complaint database.
Once the complaint form is open, the user can begin to enter the required information. Since likely, the user will not have the customer’s customer number memorized, the user can simply begin to type the customer’s name into the search field and the program will automatically complete the rest of the name as shown in the screenshot below
The user cannot record a complaint without entering at least one item. Additionally, the user cannot enter an item unless they have selected at least one complaint category. If the user attempts to add an item without selecting a complaint category, a message will display as shown in the screenshot below.
If the user attempts to record a complaint without adding at least one item, the message shown in the below screenshot will appear.
When the user clicks the add item button, the item data is recorded on a sheet in the workbook and the appropriate recipients are added to the email list based on the complaint category the user selected. An item appears in the list that contains the information the user entered. This is shown in the below screenshot.
If the user needs to remove an item that he or she has entered, the user does so by clicking on the remove item button. At any point, after the complaint form is open, the user can view a chart that details the company’s historical complaints. The user may reference this to understand how prevalent a certain type of complaint is.
After the user has entered all necessary information, he or she clicks on the record button to process the complaint. When the user clicks the record button, the remaining data on the form is transferred to a worksheet. This data, along with the previously recorded item data, is then inserted into the database. The historical chart is updated, the program sends an email to the previously designated recipients, and the form is closed. An example of the email received by the recipients is shown below. This process is a key feature of the system. This email allows the user who enters the complaint to easily inform the necessary people in the company of the complaint. The email also makes the recipient accountable for investigating why the complaint occurred and making a resolution. Lastly, the email provides the recipient with a visual summary of how prevalent the particular type of complaint has been.
Editing Complaint
After the recipient resolves the complaint, he or she will access the system and update the complaint by clicking on the edit complaint button either on the ribbon or on the worksheet as illustrated below.
Once the edit form is open, the user has several options for finding the complaint as shown in the below screenshot.
If the user has the complaint number, he or she can simply enter that number into the Complaint No. field and press the find button adjacent to the text box. The user can follow a similar process to lookup up the complaint by the invoice number. If the user only remembers the customer’s name, he or she can enter the customer’s name and then cycle through the complaints listed for the customer until he or she finds the correct complaint. If the user reaches the last complaint about a given customer and then clicks the Next button again, the message below will display.
Once the user finds the complaint, he or she can record the resolution along with his or her initials. When the user clicks on the update button, the database will be updated.
Other users can also use the edit form to update complaint information as needed. Periodically, the sales manager will access the user form to review open complaints and to close them. In order to close a complaint, the sales manager must double click on the text box next to the status label. Upon double-clicking on the text box, a form will appear for the user to enter a password. If the user enters an incorrect password, the following message will be displayed.
The user will then be taken back to the edit form. This feature is essential to ensuring that only authorized employees can close out complaints. Additionally, this process allows management to review customer complaints. Once the user enters the correct password, the status of the complaint is changed to closed. The user must click update to save the change.
Viewing Report
Users can use the complaint system to view the following reports: past due complaints and historical complaints. To view the past due to complaints form, the user must click on the view past due complaints button on either the ribbon or the worksheet as shown below.
The user will then be taken to the following report.
The message box instructs the user to select a complaint in column A and then click on the edit complaint button to either view or edit the complaint. This report is important because it allows managers to see which complaints have not been resolved within five days of being received. The manager can look at the complaint and can contact the appropriate employee to find out why the employee has not resolved the complaint.
When the manager selects a complaint number and clicks the edit complaint button, the system opens the edit form and displays the information for the selected complaint as shown below.
Users can also view a report that displays a chart of historical complaints. To access this report, the user clicks on the view historical complaints button on the worksheet as shown below.
When the report opens, a chart showing historical complaints is displayed. The user can use the pivot slicers to filter the data by month and by complaint category as shown below.
This report allows users to analyze how prevalent complaints of each category are. It also allows them to see which types of complaints are most prevalent.
Technical Documentation
Worksheets
Workbook:
Activate the homepage, hide the Past Due sheet and create a Historical and Analysis sheet as those are temporary.
wksHomePage:
Home Page is the main page of the application form where the user access the application
wksUpdate:
Used for data dump from Access database
wksPastDue:
To view the past due data, and to work upon all those complaints which are pending
wksCustomerData:
To view and use Customer information to lookup and links
Forms
frmClose :
This form is used to get the password from the user while closing any complaints
frmComplaint:
This form is used to enter a new complaint
frmEdit:
This form is used to update/edit/close the entered complaints
frmHistoricalGraph:
This form is used to show a graphical representation of the current status of the complaints so far
Modules
mdlMain:
Launches the initial user form and contains all the anchors, public variables and public arrays used in the application.
mdlChart:
For creating charts, pivots of entered complaints
mdlReports:
This module contains all the procedures which create the reporting for viewing of past due complaints
Appendix – VBA Code
Workbook_Open
Private Sub Workbook_Open()
wksHomePage.Select
Application.DisplayAlerts = False
On Error Resume Next
wksPastDue.Visible = xlSheetHidden
generateHistChart
Application.DisplayFormulaBar = False
wksHomePage.Select
With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End With
End Sub
Workbook_BeforeClose
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
End Sub
frmClose
Option Explicit
Private Sub cmdEnter_Click()
Dim pWord As String
Dim cResponse As String
If frmClose.txtPassword.Value <> “P@ssw0rd” Then
MsgBox (“You are not authorized to close this complaint”)
Else
frmEdit.txtStatus.Locked = False
If frmEdit.txtStatus.Value = “Closed” Then
frmEdit.txtStatus.Value = “Open”
Else
frmEdit.txtStatus.Value = “Closed”
End If
frmEdit.txtStatus.Locked = True
End If
frmClose.txtPassword = “”
frmClose.Hide
End Sub
frmComplaint
Option Explicit
Private Sub cmdHistorical_Click()
generateHistChart
frmHistoricalGraph.show
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.DisplayAlerts = False
On Error Resume Next
Sheets(“complaintHeader”).Delete
On Error Resume Next
Sheets(“complaintDetail”).Delete
Application.DisplayAlerts = True
End Sub
Private Sub cmbSearch_Change()
frmComplaint.txtCustNo = cmbSearch.Value
End Sub
Private Sub cmdAdd_Click()
Dim strItem(1, 4) As String
Dim intCatCount As Integer
Dim strCategories As String
Dim strEntry As String
Dim strEntryLen As Integer
Dim x As Integer
Dim strCatArray(9) As String
Dim y As Integer
Dim intRowCount As Integer
Dim intBoxesChecked As Integer
strCatArray(1) = “Billing”
strCatArray(2) = “Delivery”
strCatArray(3) = “Labeling”
strCatArray(4) = “Wrong Product”
strCatArray(5) = “Oozing Taffy”
strCatArray(6) = “Hard Taffy”
strCatArray(7) = “Design”
strCatArray(8) = “Foreign Object”
strCatArray(9) = “Health Concern”
strItem(0, 0) = txtItem
strItem(0, 1) = txtAffected
strItem(0, 2) = txtLot
If cbBilling = True Then
strCategories = “1”
‘Sheet4.Range(“B1”).Value = 1
‘Sheet4.Range(“B2”).Value = 1
send(0) = sList(0)
send(1) = sList(1)
intBoxesChecked = intBoxesChecked + 1
End If
If cbDelivery = True Then
strCategories = strCategories & “2”
‘Sheet4.Range(“B3”).Value = 1
send(1) = sList(1)
send(2) = sList(2)
intBoxesChecked = intBoxesChecked + 1
End If
If cbLabel = True Then
strCategories = strCategories & “3”
‘Sheet4.Range(“B3”).Value = 1
‘Sheet4.Range(“B4”).Value = 1
‘Sheet4.Range(“B5”).Value = 1
send(1) = sList(1)
send(2) = sList(2)
send(3) = sList(3)
send(4) = sList(4)
intBoxesChecked = intBoxesChecked + 1
End If
If cbWrongProduct = True Then
strCategories = strCategories & “4”
‘Sheet4.Range(“B1”).Value = 1
‘Sheet4.Range(“B3”).Value = 1
send(0) = sList(0)
send(1) = sList(1)
send(2) = sList(2)
intBoxesChecked = intBoxesChecked + 1
End If
If cbOozing = True Then
strCategories = strCategories & “5”
‘Sheet4.Range(“B4”).Value = 1
‘Sheet4.Range(“B5”).Value = 1
send(1) = sList(1)
send(3) = sList(3)
send(4) = sList(4)
send(5) = sList(5)
send(6) = sList(6)
intBoxesChecked = intBoxesChecked + 1
End If
If cbHard = True Then
strCategories = strCategories & “6”
‘Sheet4.Range(“B4”).Value = 1
‘Sheet4.Range(“B5”).Value = 1
send(1) = sList(1)
send(3) = sList(3)
send(4) = sList(4)
send(5) = sList(5)
send(6) = sList(6)
intBoxesChecked = intBoxesChecked + 1
End If
If cbDesign = True Then
strCategories = strCategories & “7”
‘Sheet4.Range(“B4”).Value = 1
‘Sheet4.Range(“B5”).Value = 1
send(1) = sList(1)
send(3) = sList(3)
send(4) = sList(4)
send(5) = sList(5)
send(6) = sList(6)
intBoxesChecked = intBoxesChecked + 1
End If
If cbForeign = True Then
strCategories = strCategories & “8”
‘Sheet4.Range(“B4”).Value = 1
‘Sheet4.Range(“B5”).Value = 1
send(1) = sList(1)
send(3) = sList(3)
send(4) = sList(4)
send(5) = sList(5)
send(6) = sList(6)
intBoxesChecked = intBoxesChecked + 1
End If
If cbHealth = True Then
strCategories = strCategories & “9”
‘Sheet4.Range(“B6”).Value = 1
send(1) = sList(1)
send(5) = sList(5)
intBoxesChecked = intBoxesChecked + 1
End If
If intBoxesChecked > 0 Then
strItem(0, 3) = strCategories
strEntryLen = Len(strCategories)
For x = 1 To strEntryLen
strEntry = strItem(0, 0) & “,” & strItem(0, 1) & “,” & strItem(0, 2) & “,” & Mid(strItem(0, 3), x, 1)
For y = 1 To 9
If Right(Trim(strEntry), 1) = y Then
strEntry = strItem(0, 0) & “,” & strItem(0, 1) & “,” & strItem(0, 2) & “,” & strCatArray(y)
If Sheets(“complaintDetail”).Cells(1, 1).Value = “” Then
Sheets(“complaintDetail”).Cells(1, 1).Value = frmComplaint.txtComplaintNum.Value
Sheets(“complaintDetail”).Cells(1, 2).Value = strItem(0, 0)
Sheets(“complaintDetail”).Cells(1, 3).Value = strItem(0, 1)
Sheets(“complaintDetail”).Cells(1, 4).Value = strItem(0, 2)
Sheets(“complaintDetail”).Cells(1, 5).Value = strCatArray(y)
Else
intRowCount = Sheets(“complaintDetail”).Cells(Rows.Count, 1).End(xlUp).Row
Sheets(“complaintDetail”).Cells(intRowCount + 1, 1).Value = frmComplaint.txtComplaintNum.Value
Sheets(“complaintDetail”).Cells(intRowCount + 1, 2).Value = strItem(0, 0)
Sheets(“complaintDetail”).Cells(intRowCount + 1, 3).Value = strItem(0, 1)
Sheets(“complaintDetail”).Cells(intRowCount + 1, 4).Value = strItem(0, 2)
Sheets(“complaintDetail”).Cells(intRowCount + 1, 5).Value = strCatArray(y)
End If
End If
Next y
With lstItems
.AddItem (strEntry)
End With
Next
txtItem = “”
txtAffected = “”
txtLot = “”
cbBilling = False
cbDelivery = False
cbLabel = False
cbWrongProduct = False
cbOozing = False
cbHard = False
cbDesign = False
cbForeign = False
cbHealth = False
Else
MsgBox “You must select at least one complaint category to continue”, , “Selection Required”
End If
End Sub
Private Sub cmdCancel_Click()
Dim x As Integer
Dim intlCount As Integer
intlCount = lstItems.ListCount
For x = intlCount – 1 To 0 Step -1
With lstItems
.RemoveItem (x)
End With
Next
txtItem = “”
txtAffected = “”
txtLot = “”
cbBilling = False
cbDelivery = False
cbLabel = False
cbWrongProduct = False
cbOozing = False
cbHard = False
cbDesign = False
cbForeign = False
cbHealth = False
txtDesc = “”
txtComplaintNum = “”
txtCustNo = “”
txtComplaintDate = “”
txtInvoiceNo = “”
cmbSearch = “”
Application.DisplayAlerts = False
Sheets(“complaintHeader”).Delete
Sheets(“complaintDetail”).Delete
Application.DisplayAlerts = True
frmComplaint.Hide
wksHomePage.Select
End Sub
Private Sub cmdRecord_Click()
Dim x As Integer
Dim intlCount As Integer
If frmComplaint.lstItems.ListCount = 0 Then
MsgBox “You must enter an item and click add the item to continue”, , “Input Required”
Else
Sheets(“complaintHeader”).Cells(1, 1).Value = frmComplaint.txtComplaintNum
Sheets(“complaintHeader”).Cells(1, 2).Value = frmComplaint.txtCustNo
Sheets(“complaintHeader”).Cells(1, 3).Value = frmComplaint.txtComplaintDate.Value
Sheets(“complaintHeader”).Cells(1, 4).Value = frmComplaint.txtInvoiceNo
Sheets(“complaintHeader”).Cells(1, 5).Value = frmComplaint.txtDesc
data
generateHistChart
sendEmail
intlCount = lstItems.ListCount
For x = intlCount – 1 To 0 Step -1
With lstItems
.RemoveItem (x)
End With
Next
txtDesc = “”
txtComplaintNum = “”
txtCustNo = “”
txtInvoiceNo = “”
cmbSearch = “”
frmComplaint.Hide
Application.DisplayAlerts = False
Sheets(“complaintHeader”).Delete
Sheets(“complaintDetail”).Delete
Application.DisplayAlerts = True
wksHomePage.Select
End If
End Sub
Private Sub cmdRemove_Click()
Dim x As Integer
With lstItems
For x = 0 To .ListCount – 1
If .Selected(x) Then
.RemoveItem (x)
End If
Next
End With
End Sub
frmEdit
Option Explicit
Private Sub cmdFindInvoiceNo_Click()
Dim intCount As Integer
Dim m As Integer
intCount = wksUpdate.Cells(Rows.Count, 1).End(xlUp).Row – 1
For m = 1 To intCount
If wksUpdate.Cells(m + 1, 4).Value = frmEdit.txtInvoiceNo Then
frmEdit.txtCustomerNo = wksUpdate.Cells(m + 1, 2).Value
frmEdit.txtComplaintDate = wksUpdate.Cells(m + 1, 3).Value
frmEdit.txtComplaintNo = wksUpdate.Cells(m + 1, 1).Value
frmEdit.txtDesc = wksUpdate.Cells(m + 1, 5).Value
frmEdit.txtStatus = wksUpdate.Cells(m + 1, 6).Value
frmEdit.txtResolution = wksUpdate.Cells(m + 1, 7).Value
frmEdit.txtInitials = wksUpdate.Cells(m + 1, 8).Value
End If
Next
If frmEdit.txtCustomerNo.Value = “” Then
MsgBox (“Enter a valid invoice number”)
End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
nextCustRow = 0
custNo = “”
nextDateRow = 0
cDay = “”
nextStatRow = 0
cStatus = “”
If editSource = “fromChart” Then
Sheets(“PastDue”).Select
Else
wksHomePage.Select
End If
End Sub
Private Sub cmbEditSearch_Change()
frmEdit.txtCustomerNo = frmEdit.cmbEditSearch.Value
End Sub
Private Sub cmdFindCompNo_Click()
Dim intCount As Integer
Dim y As Integer
intCount = wksUpdate.Cells(Rows.Count, 1).End(xlUp).Row – 1
For y = 1 To intCount
If wksUpdate.Cells(y + 1, 1).Value = frmEdit.txtComplaintNo Then
frmEdit.txtCustomerNo = wksUpdate.Cells(y + 1, 2).Value
frmEdit.txtComplaintDate = wksUpdate.Cells(y + 1, 3).Value
frmEdit.txtInvoiceNo = wksUpdate.Cells(y + 1, 4).Value
frmEdit.txtDesc = wksUpdate.Cells(y + 1, 5).Value
frmEdit.txtStatus = wksUpdate.Cells(y + 1, 6).Value
frmEdit.txtResolution = wksUpdate.Cells(y + 1, 7).Value
frmEdit.txtInitials = wksUpdate.Cells(y + 1, 8).Value
End If
Next
If frmEdit.txtCustomerNo.Value = “” Then
MsgBox (“Enter a valid complaint number”)
End If
End Sub
Private Sub cmdNextCust_Click()
If nextCustRow = 0 Then
nextCustRow = 2
Else
nextCustRow = nextCustRow + 1
End If
Do Until wksUpdate.Cells(nextCustRow, 2).Value = “”
If wksUpdate.Cells(nextCustRow, 2).Value = frmEdit.txtCustomerNo.Value Then
frmEdit.txtComplaintNo = wksUpdate.Cells(nextCustRow, 1).Value
frmEdit.txtCustomerNo = wksUpdate.Cells(nextCustRow, 2).Value
frmEdit.txtComplaintDate = wksUpdate.Cells(nextCustRow, 3).Value
frmEdit.txtInvoiceNo = wksUpdate.Cells(nextCustRow, 4).Value
frmEdit.txtDesc = wksUpdate.Cells(nextCustRow, 5).Value
frmEdit.txtStatus = wksUpdate.Cells(nextCustRow, 6).Value
frmEdit.txtResolution = wksUpdate.Cells(nextCustRow, 7).Value
frmEdit.txtInitials = wksUpdate.Cells(nextCustRow, 8).Value
custNo = frmEdit.txtCustomerNo.Value
Exit Do
End If
nextCustRow = nextCustRow + 1
Loop
If wksUpdate.Cells(nextCustRow, 2).Value = “” And custNo = “” Then
MsgBox (“There are no complaints listed for this customer.”)
nextCustRow = 0
ElseIf wksUpdate.Cells(nextCustRow, 2).Value = “” And custNo <> “” Then
MsgBox (“There are no more complaints listed for this customer.”)
nextCustRow = 0
custNo = “”
frmEdit.txtComplaintNo = “”
frmEdit.txtComplaintDate = “”
frmEdit.txtInvoiceNo = “”
frmEdit.txtDesc = “”
frmEdit.txtStatus = “”
frmEdit.txtResolution = “”
frmEdit.txtInitials = “”
End If
End Sub
Private Sub cmdNextStatus_Click()
If nextStatRow = 0 Then
nextStatRow = 2
Else
nextStatRow = nextStatRow + 1
End If
Do Until wksUpdate.Cells(nextStatRow, 6).Value = “”
If wksUpdate.Cells(nextStatRow, 6).Value = “Open” Then
frmEdit.txtComplaintNo = wksUpdate.Cells(nextStatRow, 1).Value
frmEdit.txtCustomerNo = wksUpdate.Cells(nextStatRow, 2).Value
frmEdit.txtComplaintDate = wksUpdate.Cells(nextStatRow, 3).Value
frmEdit.txtInvoiceNo = wksUpdate.Cells(nextStatRow, 4).Value
frmEdit.txtDesc = wksUpdate.Cells(nextStatRow, 5).Value
frmEdit.txtStatus = wksUpdate.Cells(nextStatRow, 6).Value
frmEdit.txtResolution = wksUpdate.Cells(nextStatRow, 7).Value
frmEdit.txtInitials = wksUpdate.Cells(nextStatRow, 8).Value
cStatus = frmEdit.txtStatus
Exit Do
End If
nextStatRow = nextStatRow + 1
Loop
If wksUpdate.Cells(nextStatRow, 6).Value = “” And cStatus = “” Then
MsgBox (“There are no open complaints.”)
nextStatRow = 0
ElseIf wksUpdate.Cells(nextStatRow, 3).Value = “” And cStatus <> “” Then
MsgBox (“There are no more complaints listed for this status.”)
nextStatRow = 0
cStatus = “”
frmEdit.txtComplaintNo = “”
frmEdit.txtCustomerNo = “”
frmEdit.txtComplaintDate = “”
frmEdit.txtInvoiceNo = “”
frmEdit.txtDesc = “”
frmEdit.txtStatus = “”
frmEdit.txtResolution = “”
frmEdit.txtInitials = “”
End If
End Sub
Private Sub cmdUpdate_Click()
Dim cn As New ADODB.Connection
Dim sql As String
On Error Resume Next ‘ these lines should allow you to connect. Depending on your version of windows, only one will execute without error.
cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
On Error GoTo 0
sql = ” UPDATE ComplaintHeader ” _
& ” SET ComplaintNo ='” & frmEdit.txtComplaintNo _
& “‘, CustomerNo ='” & frmEdit.txtCustomerNo _
& “‘, ComplaintDate ='” & frmEdit.txtComplaintDate _
& “‘, InvoiceNo ='” & frmEdit.txtInvoiceNo _
& “‘, ComplaintDesc ='” & frmEdit.txtDesc _
& “‘, Status ='” & frmEdit.txtStatus _
& “‘, Resolution ='” & frmEdit.txtResolution _
& “‘, Initials ='” & frmEdit.txtInitials & “‘ WHERE ComplaintNo ='” & frmEdit.txtComplaintNo & “‘;”
sql = Replace(sql, frmEdit.txtDesc.Value, Replace(frmEdit.txtDesc.Value, “‘”, “””))
sql = Replace(sql, frmEdit.txtResolution.Value, Replace(frmEdit.txtResolution.Value, “‘”, “””))
cn.Execute sql
cn.Close
frmEdit.txtComplaintNo = “”
frmEdit.txtCustomerNo = “”
frmEdit.txtComplaintDate = “”
frmEdit.txtInvoiceNo = “”
frmEdit.txtDesc = “”
frmEdit.txtStatus = “”
frmEdit.txtResolution = “”
frmEdit.txtInitials = “”
frmEdit.cmbEditSearch = “”
edit
nextCustRow = 0
custNo = “”
nextDateRow = 0
cDay = “”
nextStatRow = 0
cStatus = “”
frmEdit.Hide
If editSource = “fromChart” Then
Sheets(“PastDue”).Select
Else
wksHomePage.Select
End If
End Sub
Private Sub txtStatus_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmClose.show
End Sub
mdlChart
Option Explicit
Sub dpChart()
Dim ch As Chart
Dim sh As Shape
Dim objBtnEdit As Object
Dim objBtnClose As Object
Dim intRowNum As Integer
‘to count the number of rows
intRowNum = Sheets(“PastDue”).Cells(Rows.Count, 1).End(xlUp).Row
‘to create a table
ActiveSheet.ListObjects.Add(xlSrcRange, Range(“$A$1”).CurrentRegion, , xlYes).Name = _
“daysPassed”
Range(“daysPassed[ComplaintDate]”).NumberFormat = “m/d/yyyy”
‘to create a chart
Set sh = Sheets(“PastDue”).Shapes.AddChart
Set ch = sh.Chart
ch.SetSourceData Source:=Range(“PastDue!$A$1:$B$” & intRowNum)
ch.ChartTitle.Caption = “Days Passed”
ch.ChartType = xlColumnClustered
With sh.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.6000000238
.Transparency = 0
.Solid
End With
With ch.SeriesCollection(1).Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
With ch.SeriesCollection(1).Format.ThreeD
.BevelTopType = msoBevelCircle
.BevelTopInset = 6
.BevelTopDepth = 6
End With
ch.SetElement (msoElementLegendRight)
sh.Top = Range(“A2”).Top
sh.Left = Range(“F1”).Left
sh.Height = “300”
sh.Width = “500”
Set objBtnEdit = ActiveSheet.Buttons.Add(252, 7.5, 65.25, 38.5)
objBtnEdit.OnAction = “viewPastDue”
objBtnEdit.Characters.Text = “Edit Complaint”
With objBtnEdit.Characters(Start:=1, Length:=14).Font
.Name = “Calibri”
.FontStyle = “Regular”
.Size = 11
.ColorIndex = 1
End With
ActiveSheet.Shapes(objBtnEdit.Name).ScaleHeight 1.2105263158, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes(objBtnEdit.Name).IncrementTop 0.75
Set objBtnClose = ActiveSheet.Buttons.Add(252, 75, 65.25, 38.5)
objBtnClose.OnAction = “closePastDue”
objBtnClose.Characters.Text = “Close”
With objBtnClose.Characters(Start:=1, Length:=14).Font
.Name = “Calibri”
.FontStyle = “Regular”
.Size = 11
.ColorIndex = 1
End With
ActiveSheet.Shapes(objBtnClose.Name).ScaleHeight 1.2105263158, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes(objBtnClose.Name).IncrementTop 0.75
Range(“A2”).Select
End Sub
Sub buildHistorical()
Sheets.Add.Name = “Historical”
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Range(“Analysis!$A$1”).CurrentRegion, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=”Historical!R1C1″, TableName:=”PivotTable1″, DefaultVersion _
:=xlPivotTableVersion14
Sheets(“Historical”).Select
Cells(1, 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range(“Historical!$A$1:$C$18”)
ActiveSheet.Shapes(“Chart 1”).IncrementLeft 192
ActiveSheet.Shapes(“Chart 1”).IncrementTop 15
histChart
End Sub
Sub histChart()
With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“ComplaintCategory”)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“ComplaintDate”)
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables(“PivotTable1”).AddDataField ActiveSheet.PivotTables( _
“PivotTable1”).PivotFields(“ComplaintNo”), “Count of ComplaintNo”, xlCount
Range(“B2”).Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveSheet.Shapes(“Chart 1”).IncrementLeft -639
ActiveSheet.Shapes(“Chart 1”).IncrementTop -111.75
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveSheet.Shapes(“Chart 1”).ScaleWidth 1.4145833333, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes(“Chart 1”).ScaleHeight 1.5173611111, msoFalse, _
msoScaleFromTopLeft
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables(“PivotTable1”), _
“ComplaintDate”).Slicers.Add ActiveSheet, , “ComplaintDate”, “ComplaintDate”, _
60, 323.25, 144, 198.75
ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables(“PivotTable1”), _
“ComplaintCategory”).Slicers.Add ActiveSheet, , “ComplaintCategory”, _
“ComplaintCategory”, 97.5, 360.75, 144, 198.75
ActiveChart.ChartTitle.Text = “Historical Complaints”
ActiveSheet.Shapes.Range(Array(“ComplaintCategory”)).Select
ActiveSheet.Shapes(“ComplaintCategory”).IncrementLeft 177
ActiveSheet.Shapes(“ComplaintCategory”).IncrementTop -95.25
ActiveSheet.Shapes.Range(Array(“ComplaintDate”)).Select
ActiveSheet.Shapes(“ComplaintDate”).IncrementLeft 373.5
ActiveSheet.Shapes(“ComplaintDate”).IncrementTop -57
With ActiveSheet.Shapes(“Chart 1”).Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Solid
End With
With ActiveSheet.Shapes(“Chart 1”).Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.8000000119
.Transparency = 0
.Solid
End With
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.ShowAllFieldButtons = False
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetElement (msoElementDataLabelInsideEnd)
ActiveChart.SeriesCollection(2).Select
ActiveChart.SetElement (msoElementDataLabelInsideEnd)
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
mdlMain
Option Explicit
Public send(8) As String
Public sList(8) As String
Public formName As String
Public nextCustRow As Integer
Public custNo As String
Public nextDateRow As Integer
Public cDay As Variant
Public nextStatRow As Integer
Public cStatus As String
Public pdCompNo As String
Public editSource As String
Sub sendEmail()
Dim OLApp As Object
Dim rnglRange As Range
Dim custName As Variant
Dim custNum As Variant
Dim arg4 As Boolean
Dim intColumn As Integer
Dim itemProbs As String
Dim x As Integer
Dim strArray As Variant
Dim sendTo As String
Dim y As Integer
‘to create vlookup for customer name
Set rnglRange = wksCustomerData.Range(“A2”).CurrentRegion
custNum = frmComplaint.txtCustNo.Value
arg4 = False
intColumn = 2
custName = Application.WorksheetFunction.VLookup(custNum, rnglRange, intColumn, arg4)
‘ to create list of items
With frmComplaint.lstItems
For x = 0 To frmComplaint.lstItems.ListCount – 1
strArray = Split(frmComplaint.lstItems.List(x), “,”)
itemProbs = itemProbs & ” ” & Chr(149) & ” Item ” & strArray(0) & ” had ” & strArray(1) & ” case(s) of lot number ” _
& strArray(2) & ” affected by ” & LCase(strArray(3)) & ” issues
“
Next
End With
‘ to open outlook and send message
On Error Resume Next
Set OLApp = GetObject(, “Outlook.Application”)
On Error GoTo 0
If OLApp Is Nothing Then
openOutlook
Exit Sub
Else
For y = 0 To UBound(send)
If send(y) <> “” Then
With OLApp.CreateItem(olMailItem)
.To = send(y)
.Subject = “Complaint ” & frmComplaint.txtComplaintNum
.Attachments.Add ThisWorkbook.Path & “\temp.GIF”
.HTMLBody = “Attention: ” & Application.WorksheetFunction.Proper(Left(send(y), Application.WorksheetFunction.Find(“@”, send(y)) – 1)) _
& “
Complaint ” & frmComplaint.txtComplaintNum _
& ” has been recorded for ” & custName _
& ” regarding invoice ” & frmComplaint.txtInvoiceNo & “
” _
& custName & ” described the complaint as follows:
” _
& frmComplaint.txtDesc & “
” & “The issues below were recorded for the following products:
” & itemProbs _
& “
Please take the necessary action to resolve these concerns and record the resolution by: ” & Date + 3
.send
End With
End If
Next y
End If
‘to clear email recipient list
send(0) = “”
send(1) = “”
send(2) = “”
send(3) = “”
send(4) = “”
send(5) = “”
send(6) = “”
send(7) = “”
send(8) = “”
End Sub
Sub openOutlook()
Dim newSecond As Variant
Dim newMinute As Variant
Dim newHour As Variant
Dim waitTime As Variant
Shell (“Outlook”)
‘to wait while program opens
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 15
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
sendEmail
End Sub
Sub openForm()
formName = “New”
setup
frmComplaint.show
End Sub
Sub setup()
‘to initialize email addresses
sList(0) = “[email protected]”
sList(1) = “[email protected]”
sList(2) = “[email protected]”
sList(3) = “[email protected]”
sList(4) = “[email protected]”
sList(5) = “[email protected]”
sList(6) = “[email protected]”
‘to delete sheets if necessary
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error Resume Next
Sheets(“complaintHeader”).Delete
On Error Resume Next
Sheets(“complaintDetail”).Delete
On Error Resume Next
Sheets.Add.Name = “complaintHeader”
On Error Resume Next
Sheets.Add.Name = “complaintDetail”
loadCustomers
nextID
Application.ScreenUpdating = True
Application.DisplayAlerts = True
wksHomePage.Select
End Sub
Sub data()
Dim cn As New ADODB.Connection
Dim sql As String
Dim dCount As Integer
Dim p As Integer
Application.ScreenUpdating = False
On Error Resume Next ‘ these lines should allow you to connect. Depending on your version of windows, only one will execute without error.
cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
On Error GoTo 0
‘to insert data into complaint header table
sql = ” INSERT INTO ComplaintHeader(ComplaintNo, CustomerNo, ComplaintDate, InvoiceNo, ComplaintDesc, Status)” _
& ” values(‘” & Sheets(“complaintHeader”).Cells(1, 1).Value & “‘,'” & Sheets(“complaintHeader”).Cells(1, 2).Value & “‘,'” & Sheets(“complaintHeader”).Cells(1, 3).Value _
& “‘,'” & Sheets(“complaintHeader”).Cells(1, 4).Value & “‘,'” & Trim(Sheets(“complaintHeader”).Cells(1, 5).Value) & “‘, ‘Open’);”
sql = Replace(sql, Trim(Sheets(“complaintHeader”).Cells(1, 5).Value), Replace(Trim(Sheets(“complaintHeader”).Cells(1, 5).Value), “‘”, “””))
cn.Execute sql
Sheets(“complaintHeader”).Cells(1, 1).EntireRow.Delete
dCount = Sheets(“complaintDetail”).Cells(Rows.Count, 1).End(xlUp).Row
For p = 1 To dCount
‘to insert data into detail table
sql = ” INSERT INTO ComplaintDetail(ComplaintNo, ItemNo, ComplaintCategory, QuantityAffected, LotNumber)” _
& ” values(‘” & Sheets(“complaintDetail”).Cells(1, 1).Value & “‘,'” & Sheets(“complaintDetail”).Cells(1, 2).Value & “‘,'” & Sheets(“complaintDetail”).Cells(1, 5).Value _
& “‘,'” & Sheets(“complaintDetail”).Cells(1, 3).Value & “‘,'” & Sheets(“complaintDetail”).Cells(1, 4).Value & “‘);”
cn.Execute sql
Sheets(“complaintDetail”).Cells(1, 1).EntireRow.Delete
Next
cn.Close
Application.ScreenUpdating = True
End Sub
Sub edit()
Dim cn As New ADODB.Connection
Dim sql As String
Dim r As Integer
Dim rs As ADODB.Recordset
Dim rNum As Integer
Application.ScreenUpdating = False
wksUpdate.Visible = True
wksUpdate.Select
rNum = wksUpdate.Cells(Rows.Count, 1).End(xlUp).Row
If wksUpdate.Cells(2, 1).Value <> “” Then
Rows(“2:” & rNum).Delete
End If
On Error Resume Next ‘ these lines should allow you to connect. Depending on your version of windows, only one will execute without error.
cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
On Error GoTo 0
sql = ” SELECT * FROM ComplaintHeader;”
Set rs = cn.Execute(sql)
r = 2
rs.MoveFirst
Do Until rs.EOF
Cells(r, 1).Value = rs.Fields(“ComplaintNo”)
Cells(r, 2).Value = rs.Fields(“CustomerNo”)
Cells(r, 3).Value = rs.Fields(“ComplaintDate”)
Cells(r, 4).Value = rs.Fields(“InvoiceNo”)
Cells(r, 5).Value = rs.Fields(“ComplaintDesc”)
Cells(r, 6).Value = rs.Fields(“Status”)
Cells(r, 7).Value = rs.Fields(“Resolution”)
Cells(r, 8).Value = rs.Fields(“Initials”)
rs.MoveNext
r = r + 1
Loop
cn.Close
wksUpdate.Visible = False
Application.ScreenUpdating = True
End Sub
Sub openEdit()
Dim intCount As Integer
Dim y As Integer
formName = “Edit”
edit
loadCustomers
If editSource = “fromChart” Then
intCount = wksUpdate.Cells(Rows.Count, 1).End(xlUp).Row – 1
For y = 1 To intCount
If wksUpdate.Cells(y + 1, 1).Value = pdCompNo Then
frmEdit.txtComplaintNo = pdCompNo
frmEdit.txtCustomerNo = wksUpdate.Cells(y + 1, 2).Value
frmEdit.txtComplaintDate = wksUpdate.Cells(y + 1, 3).Value
frmEdit.txtInvoiceNo = wksUpdate.Cells(y + 1, 4).Value
frmEdit.txtDesc = wksUpdate.Cells(y + 1, 5).Value
frmEdit.txtStatus = wksUpdate.Cells(y + 1, 6).Value
frmEdit.txtResolution = wksUpdate.Cells(y + 1, 7).Value
frmEdit.txtInitials = wksUpdate.Cells(y + 1, 8).Value
End If
Next
If frmEdit.txtCustomerNo.Value = “” Then
MsgBox (“Enter a valid complaint number”)
End If
Sheets(“PastDue”).Select
End If
frmEdit.show
End Sub
Sub nextID()
Dim strStamp As String
Dim cn As New ADODB.Connection
Dim sql As String
‘Dim r As Integer
Dim rs As ADODB.Recordset
strStamp = “New”
On Error Resume Next ‘ these lines should allow you to connect. Depending on your version of windows, only one will execute without error.
cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
On Error GoTo 0
sql = ” INSERT INTO ComplaintID(Stamp)” _
& ” values(‘” & strStamp & “‘);”
cn.Execute sql
sql = ” SELECT MAX(ID) AS ID FROM ComplaintID;”
Set rs = cn.Execute(sql)
rs.MoveFirst
frmComplaint.txtComplaintNum.Value = “C” & rs.Fields(“ID”)
cn.Close
End Sub
Sub loadCustomers()
Dim intRowNumber As Integer
Dim strCustomers() As String
Dim x As Integer
Dim c As Integer
wksCustomerData.Visible = True
wksCustomerData.Select
intRowNumber = wksCustomerData.Cells(Rows.Count, 1).End(xlUp).Row – 1
ReDim strCustomers(intRowNumber, 2)
c = 1
For x = 0 To UBound(strCustomers)
strCustomers(x, 0) = Cells(c, 2).Value
strCustomers(x, 1) = Cells(c, 1).Value
c = c + 1
Next x
If formName = “New” Then
With frmComplaint.cmbSearch
.List = strCustomers
End With
Else
With frmEdit.cmbEditSearch
.List = strCustomers
End With
End If
wksCustomerData.Visible = False
Application.ScreenUpdating = True
wksHomePage.Select
End Sub
Sub viewPastDue()
Sheets(“PastDue”).Select
pdCompNo = ActiveCell.Value
editSource = “fromChart”
openEdit
editSource = “”
End Sub
Sub closePastDue()
wksHomePage.Activate
wksPastDue.Visible = xlSheetHidden
End Sub
Sub generateHistChart()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Sheets(“Analysis”).Delete
On Error Resume Next
Sheets(“Historical”).Delete
Application.DisplayAlerts = True
historical
loadChart
Application.ScreenUpdating = True
End Sub
Sub loadChart()
Dim CurrentChart As Chart
Dim Fname As String
Set CurrentChart = Sheets(“Historical”).ChartObjects(1).Chart
Fname = ThisWorkbook.Path & “\temp.gif”
CurrentChart.Export Filename:=Fname, FilterName:=”GIF”
frmHistoricalGraph.Image1.Picture = LoadPicture(Fname)
End Sub
Sub viewHistoricalChart()
Sheets(“Historical”).Visible = True
Sheets(“Historical”).Select
End Sub
Sub rNew(control As IRibbonControl)
openForm
End Sub
Sub rEdit(control As IRibbonControl)
openEdit
End Sub
Sub rPastDue(control As IRibbonControl)
pastDueComplaints
End Sub
Sub rHistorical(control As IRibbonControl)
viewHistoricalChart
End Sub
Sub rCloseHistorical(control As IRibbonControl)
ActiveWorkbook.SlicerCaches(“Slicer_ComplaintDate”).ClearManualFilter
ActiveWorkbook.SlicerCaches(“Slicer_ComplaintCategory”).ClearManualFilter
Sheets(“Historical”).Visible = False
End Sub
Sub rDeletePastDue(control As IRibbonControl)
Application.DisplayAlerts = False
On Error Resume Next
Sheets(“PastDue”).Delete
Application.DisplayAlerts = True
End Sub
mdlReports
Option Explicit
Sub pastDueComplaints()
Dim cn As New ADODB.Connection
Dim sql As String
Dim rs As Recordset
Dim intCount As Integer
Application.ScreenUpdating = False
On Error Resume Next ‘ these lines should allow you to connect. Depending on your version of windows, only one will execute without error.
cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
On Error GoTo 0
sql = ” SELECT ComplaintNo, ComplaintDate, Resolution, (DATE() – ComplaintDate) AS Days ” _
& ” FROM ComplaintHeader WHERE Resolution = “””” ORDER BY (DATE() – ComplaintDate) DESC; “
Set rs = cn.Execute(sql)
rs.MoveFirst
With wksPastDue
.Visible = xlSheetVisible
.Activate
.Columns.EntireColumn.Delete
.Cells(1, 1).Value = “ComplaintNo”
.Cells(1, 2).Value = ” Days Passed”
.Cells(1, 3).Value = “ComplaintDate”
End With
intCount = 2
Do Until rs.EOF
If rs.Fields(“Days”) > 5 Then
wksPastDue.Cells(intCount, 1).Value = rs.Fields(“ComplaintNo”)
wksPastDue.Cells(intCount, 2).Value = rs.Fields(“Days”)
wksPastDue.Cells(intCount, 3).Value = rs.Fields(“ComplaintDate”)
intCount = intCount + 1
End If
rs.MoveNext
Loop
cn.Close
dpChart
Application.ScreenUpdating = True
MsgBox (“To edit or view a complaint, select the cell in the table for the complaint that you want view and click the Edit Complaint button”)
End Sub
Sub historical()
Dim cn As New ADODB.Connection
Dim sql As String
Dim rs As Recordset
Dim intCount As Integer
On Error Resume Next ‘ these lines should allow you to connect. Depending on your version of windows, only one will execute without error.
cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”
On Error GoTo 0
sql = ” SELECT d.ComplaintNo AS ComplaintNo, h.CustomerNo AS CustomerNo, h.ComplaintDate AS ComplaintDate, ” _
& ” d.ItemNo AS ItemNo, d.ComplaintCategory AS ComplaintCategory, d.QuantityAffected AS QuantityAffected, ” _
& ” d.LotNumber AS LotNumber, h.Status AS Status ” _
& ” FROM ComplaintHeader h INNER JOIN ComplaintDetail d ON h.ComplaintNo = d.ComplaintNo; “
Set rs = cn.Execute(sql)
rs.MoveFirst
Sheets.Add.Name = “Analysis”
Cells(1, 1).Value = “ComplaintNo”
Cells(1, 2).Value = “CustomerNo”
Cells(1, 3).Value = “ComplaintDate”
Cells(1, 4).Value = “ItemNo”
Cells(1, 5).Value = “ComplaintCategory”
Cells(1, 6).Value = “QuantityAffected”
Cells(1, 7).Value = “LotNumber”
Cells(1, 8).Value = “Status”
intCount = 2
Do Until rs.EOF
Cells(intCount, 1).Value = rs.Fields(“ComplaintNo”)
Cells(intCount, 2).Value = rs.Fields(“CustomerNo”)
Cells(intCount, 3).Value = rs.Fields(“ComplaintDate”)
Cells(intCount, 4).Value = rs.Fields(“ItemNo”)
Cells(intCount, 5).Value = rs.Fields(“ComplaintCategory”)
Cells(intCount, 6).Value = rs.Fields(“QuantityAffected”)
Cells(intCount, 7).Value = rs.Fields(“LotNumber”)
Cells(intCount, 8).Value = rs.Fields(“Status”)
intCount = intCount + 1
rs.MoveNext
Loop
cn.Close
Columns(“C:C”).NumberFormat = “m/d/yyyy”
buildHistorical
Sheets(“Analysis”).Visible = False
Sheets(“Historical”).Visible = False
End Sub