E
EAB1977
Hi everyone,
I ma having some difficulty with generating a secondary axis in Excel
07. The code worked in Excel 2003 but when I upgraded to 07, most of
this appears to work.
The code is below. I commented on the section that it errors on.
Sub GetData(datStart As Date, datEnd As Date, strTL As String)
Dim db As DAO.Database, rst As DAO.Recordset, qdfReports As
DAO.QueryDef
Dim strWHERE As String, x As Variant, intRow As Integer, fld As
Variant
Dim strSQL As String, strFROM As String, strStaffID As String, strFile
As String
Dim Name As String, y As Integer, myRange As Object, RowDel As Boolean
Dim ColMax As Integer, intRow2 As Integer
Dim r As Integer, PctDone As Single, msg As Integer, b As Integer
Dim intCol As Integer, c As Integer, strLetter As String, intRow3 As
Integer
Dim intColumns As Integer, s As Variant, intCol2 As Integer
'Dim xl As Object
Dim xl As Excel.Application
On Error GoTo GetData_Err
'This macro needs to run to distingusih HAVI testing from regular
testing
'Macro: macDoFirst
DoCmd.RunMacro "macDoFirst"
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Max(tmpReports.CompleteDate) AS
MaxOfCompleteDate" _
& " FROM tmpReports;")
If Forms!frmCriteria!txtEndDate > rst!MaxOfCompleteDate Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * from tmpReports"
Set qdfReports = db.QueryDefs("qryReports2")
qdfReports.Execute
DoCmd.SetWarnings True
Set qdfReports = Nothing
End If
rst.Close
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tmpReports SET tmpReports.UserName =" _
& " 'Susan Skidmore' WHERE (((tmpReports.UserName)='Sue
Skidmore'));"
DoCmd.RunSQL "UPDATE tmpReports SET tmpReports.UserName =" _
& " 'Kimberly Van Valkenburgh' WHERE (((tmpReports.UserName)
='Kim Van Valkenburgh'));"
DoCmd.SetWarnings True
Set xl = CreateObject("Excel.Application")
'Set xl = Excel.Application
With xl
.Visible = True
.Workbooks.Open "\\files-2k1\ENG\QA\Productivity Reports
\IndividualProductReport.xlt"
.Interactive = True
.DisplayAlerts = True
.ScreenUpdating = True
.Sheets("Sheet1").Select
'Select the correct query from what matches frmCriteria
If Not IsNull(Forms!frmCriteria!cboPlant.Value) Then
If Not IsNull(Forms!frmCriteria!cboPerson.Value) Then
If Not IsNull(Forms!frmCriteria!cboProdLine.Value)
Then
Set rst = db.OpenRecordset("SELECT * FROM
tmpReports WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & " AND UserName
= '" & Forms!frmCriteria!cboPerson.Value & "'" _
& " AND ProductLineCode = '" & Forms!frmCriteria!
cboProdLine.Value & "' AND CompleteDate" _
& " BETWEEN #" & datStart & "# AND #" & datEnd &
"#")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Complated Production Dates: " _
& Forms!frmCriteria!txtStartDate.Value &
" And " & Forms!frmCriteria!txtEndDate.Value & ";" _
& " Employee - " & Forms!frmCriteria!
cboPerson.Value
GoTo Information
Else
Set rst = db.OpenRecordset("SELECT * FROM
tmpReports WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "' AND
UserName = '" & Forms!frmCriteria!cboPerson.Value & "'" _
& " AND CompleteDate BETWEEN #" & datStart & "#
AND #" & datEnd & "#")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!txtStartDate.Value &
" And " & Forms!frmCriteria!txtEndDate.Value
GoTo Information
End If
ElseIf Forms!frmCriteria!cboTL.Value <> "" Then
If Not IsNull(Forms!frmCriteria!cboProdLine.Value)
Then
Select Case strTL
Case "Annette Hornbeck"
db.Execute "DELETE * FROM tblReportsAH"
db.Execute "INSERT INTO tblReportsAH
( ProductLineCode, Name, ID," _
& " NumOfSets, StartedDate,
StartedTime, LabStartDate, LabStartTime," _
& " CompleteDate, CompletedTime,
UserName ) SELECT tmpReports.ProductLineCode," _
& " tmpReports.Name, tmpReports.ID,
tmpReports.NumOfSets," _
& " tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime,
tmpReports.CompleteDate, tmpReports.CompletedTime," _
& " tmpReports.UserName FROM
tmpReports WHERE (((tmpReports.UserName) In" _
& " ('Eric Brenner','James
Kring','Tammy Latimer','Archana Patel'" _
& ",'Kim Van Valkenburgh','Duc
Vo')));"
Set rst = db.OpenRecordset("SELECT * FROM
tblReportsAH WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "'
AND ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "'
AND StartedDate" _
& " BETWEEN #" & datStart & "# AND #"
& datEnd & "# ORDER BY UserName, StartedDate")
GoTo Information
Case "Dennis Connolly"
db.Execute "DELETE * FROM tblReportsDC"
db.Execute "INSERT INTO tblReportsDC
( ProductLineCode, Name, ID," _
& " NumOfSets, StartedDate,
StartedTime, LabStartDate, LabStartTime," _
& " CompleteDate, CompletedTime,
UserName ) SELECT tmpReports.ProductLineCode," _
& " tmpReports.Name, tmpReports.ID,
tmpReports.NumOfSets," _
& " tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime,
tmpReports.CompleteDate, tmpReports.CompletedTime," _
& " tmpReports.UserName FROM
tmpReports WHERE (((tmpReports.UserName) In" _
& " ('Ron Erwin','Jim Fairly','Tara
Franks','Kelly Hull','Sandra Mullen'" _
& ",'Jeff Shirk','Sue Skidmore')));"
Set rst = db.OpenRecordset("SELECT * FROM
tblReportsDC WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "'
AND ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "'
AND StartedDate" _
& " BETWEEN #" & datStart & "# AND #"
& datEnd & "# ORDER BY UserName, StartedDate")
GoTo Information
Case "Martin Finucane"
db.Execute "DELETE * FROM tblReportsMF"
db.Execute "INSERT INTO tblReportsMF
( ProductLineCode, Name, ID," _
& " NumOfSets, StartedDate,
StartedTime, LabStartDate, LabStartTime," _
& " CompleteDate, CompletedTime,
UserName ) SELECT tmpReports.ProductLineCode," _
& " tmpReports.Name, tmpReports.ID,
tmpReports.NumOfSets," _
& " tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime,
tmpReports.CompleteDate, tmpReports.CompletedTime," _
& " tmpReports.UserName FROM
tmpReports WHERE (((tmpReports.UserName) In" _
& " ('Claude Morey','Linda
Worgess')));"
Set rst = db.OpenRecordset("SELECT * FROM
tblReportsMF WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "'
AND ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "'
AND StartedDate" _
& " BETWEEN #" & datStart & "# AND #"
& datEnd & "# ORDER BY UserName, StartedDate")
GoTo Information
Case "Sharon Larsen"
db.Execute "DELETE * FROM tblReportsSL"
db.Execute "INSERT INTO tblReportsSL
( ProductLineCode, Name, ID," _
& " NumOfSets, StartedDate,
StartedTime, LabStartDate, LabStartTime," _
& " CompleteDate, CompletedTime,
UserName ) SELECT tmpReports.ProductLineCode," _
& " tmpReports.Name, tmpReports.ID,
tmpReports.NumOfSets," _
& " tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime,
tmpReports.CompleteDate, tmpReports.CompletedTime," _
& " tmpReports.UserName FROM
tmpReports WHERE (((tmpReports.UserName) In" _
& " ('Sue Fisher','Brian Krohn','Gary
Marchand','Penny Pittman'," _
& "'Saskia Riley','Chue Vang')));"
Set rst = db.OpenRecordset("SELECT * FROM
tblReportsSL WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "'
AND ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "'
AND StartedDate" _
& " BETWEEN #" & datStart & "# AND #"
& datEnd & "# ORDER BY UserName, StartedDate")
GoTo Information
End Select
Else
Select Case strTL
Case "Annette Hornbeck"
Set qdfReports = db.QueryDefs
("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name," _
& " tmpReports.ID, tmpReports.NumOfSets,
tmpReports.StartedDate," _
& " tmpReports.StartedTime,
tmpReports.LabStartDate, tmpReports.LabStartTime," _
& " tmpReports.CompleteDate,
tmpReports.CompletedTime, tblEmployee.UserName" _
& " FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE
tmpReports.Name = '" & Forms!frmCriteria!cboPlant.Value _
& "' AND tmpReports.CompleteDate Between
#" & datStart & "# And #" & datEnd & "#" _
& " AND tblEmployee.AH = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID,
qryReportsAll.NumOfSets, qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN
qryReportsAll ON tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.AH = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!
txtStartDate.Value & " And " & Forms!frmCriteria!txtEndDate.Value &
";" _
& " Team Leader - " & Forms!
frmCriteria!cboTL.Value
GoTo Information
Case "Dennis Connolly"
Set qdfReports = db.QueryDefs
("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name," _
& " tmpReports.ID, tmpReports.NumOfSets,
tmpReports.StartedDate," _
& " tmpReports.StartedTime,
tmpReports.LabStartDate, tmpReports.LabStartTime," _
& " tmpReports.CompleteDate,
tmpReports.CompletedTime, tblEmployee.UserName" _
& " FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE
tmpReports.Name = '" & Forms!frmCriteria!cboPlant.Value _
& "' AND tmpReports.CompleteDate Between
#" & datStart & "# And #" & datEnd & "#" _
& " AND tblEmployee.DC = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID,
qryReportsAll.NumOfSets, qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN
qryReportsAll ON tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.DC = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!
txtStartDate.Value & " And " & Forms!frmCriteria!txtEndDate.Value &
";" _
& " Team Leader - " & Forms!
frmCriteria!cboTL.Value
GoTo Information
Case "Martin Finucane"
Set qdfReports = db.QueryDefs
("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name," _
& " tmpReports.ID, tmpReports.NumOfSets,
tmpReports.StartedDate," _
& " tmpReports.StartedTime,
tmpReports.LabStartDate, tmpReports.LabStartTime," _
& " tmpReports.CompleteDate,
tmpReports.CompletedTime, tblEmployee.UserName" _
& " FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE
tmpReports.Name = '" & Forms!frmCriteria!cboPlant.Value _
& "' AND tmpReports.CompleteDate Between
#" & datStart & "# And #" & datEnd & "#" _
& " AND tblEmployee.MF= True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID,
qryReportsAll.NumOfSets, qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN
qryReportsAll ON tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.MF = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!
txtStartDate.Value & " And " & Forms!frmCriteria!txtEndDate.Value &
";" _
& " Team Leader - " & Forms!
frmCriteria!cboTL.Value
GoTo Information
Case "Sharon Larsen"
Set qdfReports = db.QueryDefs
("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name," _
& " tmpReports.ID, tmpReports.NumOfSets,
tmpReports.StartedDate," _
& " tmpReports.StartedTime,
tmpReports.LabStartDate, tmpReports.LabStartTime," _
& " tmpReports.CompleteDate,
tmpReports.CompletedTime, tblEmployee.UserName" _
& " FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE
tmpReports.Name = '" & Forms!frmCriteria!cboPlant.Value _
& "' AND tmpReports.CompleteDate Between
#" & datStart & "# And #" & datEnd & "#" _
& " AND tblEmployee.SL = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID,
qryReportsAll.NumOfSets, qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN
qryReportsAll ON tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.SL = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!
txtStartDate.Value & " And " & Forms!frmCriteria!txtEndDate.Value &
";" _
& " Team Leader - " & Forms!
frmCriteria!cboTL.Value
GoTo Information
End Select
End If
ElseIf Not IsNull(Forms!frmCriteria!cboProdLine.Value)
Then
Set rst = db.OpenRecordset("SELECT * FROM tmpReports
WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "' AND
ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "' AND
StartedDate" _
& " BETWEEN #" & datStart & "# AND #" & datEnd &
"# ORDER BY tmpReports.UserName, tmpReports.StartedDate")
Else
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT tmpReports.ProductLineCode,
tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate, tmpReports.LabStartTime,
tmpReports.CompleteDate," _
& " tmpReports.CompletedTime, tblEmployee.UserName,
tblEmployee.AH FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.Name = '" & Forms!frmCriteria!
cboPlant.Value & "' AND tmpReports.CompleteDate" _
& " Between #" & datStart & "# And #" & datEnd & "#
And tblEmployee.IsCQATech = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.IsCQATech = True ORDER BY" _
& " tblemployee.UserName, qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!frmCriteria!
cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!txtStartDate.Value &
" and " & Forms!frmCriteria!txtEndDate.Value
GoTo Information
End If
End If
If Not IsNull(Forms!frmCriteria!cboPerson.Value) Then
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT tmpReports.ProductLineCode,
tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime, tmpReports.CompleteDate,
tmpReports.CompletedTime," _
& " tblEmployee.UserName FROM tblEmployee LEFT JOIN
tmpReports ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE tmpReports.CompleteDate
Between #" & datStart & "# And" _
& " #" & datEnd & "# AND tblEmployee.UserName = '" &
Forms!frmCriteria!cboPerson.Value & "'" _
& " ORDER BY tblEmployee.UserName,
tmpReports.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("qryReportsAll")
GoTo Information
End If
If IsNull(Forms!frmCriteria!cboProdLine.Value) Then
Select Case strTL
Case "Annette Hornbeck"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.AH FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.LabStartDate Is Not Null AND
tmpReports.CompleteDate Between #" & datStart & "# And #" & datEnd &
"# And" _
& " tblEmployee.AH=True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.AH
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Dennis Connolly"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.DC FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName" _
& " WHERE tmpReports.CompleteDate Between #" &
datStart & "# And #" & datEnd & "# And" _
& " tblEmployee.DC = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.DC
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Martin Finucane"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.MF FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.CompleteDate Between #" &
datStart & "# And #" & datEnd & "# And" _
& " tblEmployee.MF=True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.MF
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Sharon Larsen"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.SL FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.CompleteDate Between #" &
datStart & "# And #" & datEnd & "# And" _
& " tblEmployee.SL=True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.SL
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case Else
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.CompleteDate Between #" &
datStart & "# And #" & datEnd & "# And" _
& " tblEmployee.IsCQATech=True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblemployee.IsCQATech = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
End Select
.Range("A2").Value = "Completed Production Dates: " &
datStart & " and " & datEnd
Else
Select Case strTL
Case "Annette Hornbeck"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = " & Forms!
frmCriteria!cboProdLine.Value & " AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.AH = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.AH
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Dennis Connolly"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = " & Forms!
frmCriteria!cboProdLine.Value & " AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.DC = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.DC
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Martin Finucane"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = " & Forms!
frmCriteria!cboProdLine.Value & " AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.MF = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.MF
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Sharon Larsen"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = " & Forms!
frmCriteria!cboProdLine.Value & " AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.SL = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.SL
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case Else
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = '" & Forms!
frmCriteria!cboProdLine.Value & "' AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.IsCQATech = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblemployee.IsCQATech = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
End Select
.Range("A2").Value = "Product Line - " & Forms!
frmCriteria!cboProdLine.Value & "; Completed Production Dates: " _
& datStart & " and " & datEnd
End If
Information: 'Load the information into the Excel sheet
DoCmd.OpenForm "frmProgressBar"
DoEvents
.Range("A4").Select
rst.MoveLast
ColMax = 13
rst.MoveFirst
Do Until rst.EOF
.ActiveCell.Value = rst!Name
.ActiveCell.Offset(0, 1).Value = rst!ProductLineCode
.ActiveCell.Offset(0, 2).Value = rst!ID
.ActiveCell.Offset(0, 3).Value = 1
.ActiveCell.Offset(0, 3).HorizontalAlignment = -4108
.ActiveCell.Offset(0, 4).Value = rst!NumOfSets
.ActiveCell.Offset(0, 4).HorizontalAlignment = -4108
.ActiveCell.Offset(0, 5).Value = rst!StartedDate
.ActiveCell.Offset(0, 6).Value = rst!StartedTime
If rst!ProductLineCode = "0EPS" Then
.ActiveCell.Offset(0, 7).Value = rst!LabStartDate
.ActiveCell.Offset(0, 8).Value = rst!LabStartTime
Else
.ActiveCell.Offset(0, 7).Value = rst!CompleteDate
.ActiveCell.Offset(0, 8).Value = rst!CompletedTime
End If
.ActiveCell.Offset(0, 9).Value = rst!UserName
If rst!ProductLineCode = "0EPS" Then
If IsNull(rst!LabStartDate) Or rst!LabStartDate = ""
Then
.ActiveCell.Offset(0, 10).Value = 0
.ActiveCell.Offset(0, 11).FormulaR1C1 = 0
Else
.ActiveCell.Offset(0, 10).Value = TestTime(rst!
StartedDate, rst!StartedTime, rst!LabStartDate, rst!LabStartTime)
.ActiveCell.Offset(0, 11).FormulaR1C1 = "=INT(RC
[-1]/RC[-7])"
End If
Else
If IsNull(rst!CompleteDate) Or rst!CompleteDate = ""
Then
.ActiveCell.Offset(0, 10).Value = 0
.ActiveCell.Offset(0, 11).FormulaR1C1 = 0
Else
.ActiveCell.Offset(0, 10).Value = TestTime(rst!
StartedDate, rst!StartedTime, rst!CompleteDate, rst!CompletedTime)
.ActiveCell.Offset(0, 11).FormulaR1C1 = "=INT(RC
[-1]/RC[-7])"
End If
End If
.ActiveCell.Offset(0, 10).HorizontalAlignment = -4108
.ActiveCell.Offset(0, 11).HorizontalAlignment = -4108
Select Case .ActiveCell.Offset(0, 1).Value
Case "0EPS"
Select Case rst!Name
Case "Argentina", "Australia", "Campbellford",
"Lodi", "Tijuana", "Tumwater", "United Kingdom"
.ActiveCell.Offset(0, 12).Value = 3
Case "Atlacomulco", "Aurora", "Lancaster",
"Mason", "Plant City", "Quitman"
.ActiveCell.Offset(0, 12).Value = 5
Case "Corona", "Horse Cave", "Leola", "Leola
South", "Lithonia", "Waxahachie"
.ActiveCell.Offset(0, 12).Value = 10
Case Else
.ActiveCell.Offset(0, 12).Value = "No Days
Mentioned"
End Select
Case "0LID"
Select Case rst!Name
Case "Corona"
.ActiveCell.Offset(0, 12).Value = 2
Case "Leola"
.ActiveCell.Offset(0, 12).Value = 11
Case "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 12
Case "Randleman"
.ActiveCell.Offset(0, 12).Value = 5
Case "Tijuana"
.ActiveCell.Offset(0, 12).Value = 8
End Select
Case "0OPS"
Select Case rst!Name
Case "Corona"
.ActiveCell.Offset(0, 12).Value = 3
Case "Leola"
.ActiveCell.Offset(0, 12).Value = 7
Case "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 8
End Select
Case "0PET"
Select Case rst!Name
Case "Randleman"
.ActiveCell.Offset(0, 12).Value = 5
End Select
Case "0PPC"
Select Case rst!Name
Case "Leola", "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 4
End Select
Case "CCUP"
Select Case rst!Name
Case "Leola"
.ActiveCell.Offset(0, 12).Value = 4
Case "Randleman"
.ActiveCell.Offset(0, 12).Value = 6
End Select
Case "PCCP"
.ActiveCell.Offset(0, 12).Value = 2
Case "DELI"
Select Case rst!Name
Case "Leola"
.ActiveCell.Offset(0, 12).Value = 2
End Select
Case "IDIN"
Select Case rst!Name
Case "Leola", "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 2
End Select
Case "EXTF"
.ActiveCell.Offset(0, 12).Value = 4
Case "PEPS"
Select Case rst!Name
Case "Argentina", "Atlacomulco", "Aurora",
"Australia", "Campbellford", _
"Lancaster", "Leola", "Mason", "Plant
City", "Tijuana", "Tumwater", _
"United Kingdom", "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 2
Case "Corona", "Lodi", "Quitman",
"Waxahachie", "Lithonia"
.ActiveCell.Offset(0, 12).Value = 3
End Select
Case "HAVI"
.ActiveCell.Offset(0, 12).Value = 5
Case "FILM"
.ActiveCell.Offset(0, 12).Value = 0
Case Else
.ActiveCell.Offset(0, 12).Value = "Not Mentioned"
End Select
.ActiveCell.Offset(0, 12).HorizontalAlignment = -4108
If .ActiveCell.Offset(0, 7).Value = "" Then
.ActiveCell.Offset(0, 13).Value = 0
Else
'Divide by 480 because it = 8 hours * 60 minutes
.ActiveCell.Offset(0, 13).FormulaR1C1 = "=INT(RC[-3]/
480)"
.ActiveCell.Offset(0, 13).NumberFormat = "0"
End If
If .ActiveCell.Offset(0, 13).Value > .ActiveCell.Offset(0,
12).Value Then
.ActiveCell.Offset(0, 13).Interior.ColorIndex = 6
.ActiveCell.Offset(0, 13).Interior.Pattern = 1
End If
.ActiveCell.Offset(0, 13).HorizontalAlignment = -4108
.ActiveCell.Offset(0, 13).NumberFormat = 0
.ActiveCell.Offset(1, 0).Select
'Update the ProgressBar
Forms!frmProgressBar!ProgressBarB.Width = _
(Forms!frmProgressBar!ProgressBarA.Width /
rst.RecordCount) * rst.AbsolutePosition
Forms!frmProgressBar!lblPercent.Caption = Format
(rst.AbsolutePosition / rst.RecordCount, "Percent")
Forms("frmProgressBar").Repaint
DoEvents
rst.MoveNext
Loop
Forms!frmProgressBar!ProgressBarB.Width = _
(Forms!frmProgressBar!ProgressBarA.Width /
rst.RecordCount) * rst.RecordCount
Forms!frmProgressBar!lblPercent.Caption = "100.00%"
'Disclaimer
.ActiveCell.Offset(1, 0).Select
.ActiveCell.Value = "Note: End Dates that are before the
Completed Dates are" _
& " Lab End Dates for Plain Foam Cup"
intRow = .ActiveCell.Row
.Range("A" & intRow & ":N" & intRow).Select
.Selection.Font.Italic = True
.Selection.Merge
If Forms!frmCriteria!cboPerson.Value <> "" Then
Call WeightFactor
GoTo EndGetData
End If
rst.Close
.Cells.Select
.Cells.EntireColumn.AutoFit
.Range("J4").Select
'Sum each persons productions
Do
If .ActiveCell.Value = "" And .ActiveCell.Offset(1,
0).Value = "" Then
Exit Do
ElseIf .ActiveCell.Value = .ActiveCell.Offset(1, 0).Value
Then
.ActiveCell.Offset(1, 0).Select
Else
.ActiveCell.Offset(1, 0).Select
.Rows(.ActiveCell.Row & ":" & .ActiveCell.Offset(1,
0).Row).Select
.Selection.Insert Shift:=1
.ActiveCell.Select
If .ActiveCell.Offset(0, 13).Interior.ColorIndex = 6
Then
.ActiveCell.Offset(0, 13).Interior.ColorIndex =
-4142
.ActiveCell.Offset(1, 13).Interior.ColorIndex =
-4142
End If
.ActiveCell.Offset(0, 3).Select
.Selection.Borders(5).LineStyle = -4142
.Selection.Borders(6).LineStyle = -4142
.Selection.Borders(7).LineStyle = -4142
With .Selection.Borders(8)
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
.Selection.Borders(9).LineStyle = -4142
.Selection.Borders(10).LineStyle = -4142
.ActiveCell.Offset(0, 1).Select
.Selection.Borders(5).LineStyle = -4142
.Selection.Borders(6).LineStyle = -4142
.Selection.Borders(7).LineStyle = -4142
With .Selection.Borders(8)
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
.Selection.Borders(9).LineStyle = -4142
.Selection.Borders(10).LineStyle = -4142
.ActiveCell.Offset(0, -1).Select
'Find out how many cells to go up.
x = -1
Do
If IsNumeric(.ActiveCell.Offset(x, 0).Value) And
Not .ActiveCell.Offset(x, 0).Value = "" Then
x = x - 1
Else
Exit Do
End If
Loop
.ActiveCell.FormulaR1C1 = "=SUM(R[" & x + 1 & "]C:R[-1]
C)"
.ActiveCell.Font.Color = 255
.ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(R[" & x +
1 & "]C:R[-1]C)"
.ActiveCell.Offset(0, 1).Copy
.Sheets("Sheet2").Select
.Range("A2").Select
Do
If .ActiveCell.Value <> "" Then
.ActiveCell.Offset(1, 0).Select
Else
Exit Do
End If
Loop
.ActiveCell.PasteSpecial -4163
.Sheets("Sheet1").Select
.ActiveCell.Offset(-1, 6).Copy
.Sheets("Sheet2").Select
.ActiveCell.Offset(0, 2).Select
.ActiveCell.PasteSpecial -4163
.Sheets("Sheet1").Select
.ActiveCell.Offset(0, 1).Font.Color = 255
.ActiveCell.Offset(0, 10).Select
.Selection.Borders(5).LineStyle = -4142
.Selection.Borders(6).LineStyle = -4142
.Selection.Borders(7).LineStyle = -4142
With .Selection.Borders(8)
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
.Selection.Borders(9).LineStyle = -4142
.Selection.Borders(10).LineStyle = -4142
x = -1
Do
If IsNumeric(.ActiveCell.Offset(x, 0).Value) And
Not .ActiveCell.Offset(x, 0).Value = "" Then
x = x - 1
Else
Exit Do
End If
Loop
.ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & x & "]C:R[-1]
C)"
.ActiveCell.NumberFormat = "0.0"
.ActiveCell.Font.Color = 255
.ActiveCell.Offset(2, -4).Select
End If
Loop
'Sum Sheet2 and sort in decending order (Z-A)
.Sheets("Sheet2").Select
.Range("C2").Select
Do
If .ActiveCell.Value <> "" Then
.ActiveCell.Offset(1, 0).Select
Else
.ActiveCell.Offset(-1, 0).Select
intRow = .ActiveCell.Row
Exit Do
End If
Loop
.Range("A1:C" & intRow).Select
.Range("A1:C" & intRow).Sort .Cells(1, 1), 2
DoEvents
.Range("B2").Select
x = 1
Do
If .ActiveCell.Offset(0, -1).Value <> "" Then
.ActiveCell.Value = x
x = x + 1
.ActiveCell.Offset(1, 0).Select
Else
Exit Do
End If
Loop
.Range("A2").Select
Do
If .ActiveCell.Value = "" Then
intRow = .ActiveCell.Row
Exit Do
Else
.ActiveCell.Offset(1, 0).Select
End If
Loop
.ActiveCell.FormulaR1C1 = "=SUM(R[-" & intRow - 2 & "]C:R[-1]
C)"
.Selection.Font.Bold = True
.Sheets("Sheet1").Select
.Range("A4").Select
'Stacked Bar Chart
.Sheets("Sheet3").Select
.Rows("1:9").Select
.Selection.Delete -4162
Set rst = db.OpenRecordset("TRANSFORM Sum(Query2.NumOfSets) AS
SumOfNumOfSets SELECT" _
& " Query2.ProductLineCode FROM Query2 GROUP BY
Query2.ProductLineCode PIVOT Query2.UserName;")
.Range("A1").Select
rst.MoveFirst
For Each fld In rst.Fields
.Selection.Borders(5).LineStyle = -4142
.Selection.Borders(6).LineStyle = -4142
With .Selection.Borders(7)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With .Selection.Borders(8)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With .Selection.Borders(9)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With .Selection.Borders(10)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With .Selection.Interior
.ColorIndex = 15
.Pattern = 1
End With
.ActiveCell.Value = fld.Name
.ActiveCell.Offset(0, 1).Select
x = x + 1
Next fld
intCol = .ActiveCell.Offset(0, -1).Column
.ActiveCell.Offset(1, -intCol).Select
Do Until rst.EOF
For Each fld In rst.Fields
.ActiveCell.Value = fld.Value
.ActiveCell.Offset(0, 1).Select
DoEvents
Next fld
.ActiveCell.Offset(1, -intCol).Select
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
RowDel = False
If .Range("A2").Value = "" Then
.Rows("2:2").Delete
RowDel = True
End If
intRow = .ActiveCell.Offset(0, 0).Row
.Range("B" & intRow).Select
Do
.ActiveCell.FormulaR1C1 = "=SUM(R[-" & intRow - 2 & "]C:R
[-1]C)"
.ActiveCell.Offset(0, 1).Select
y = y + 1
Loop Until .ActiveCell.Offset(-intRow + 1, 0).Value = ""
.Sheets.Add
.Sheets("Sheet4").Move After:=.Sheets(4)
.Sheets("Sheet3").Select
.Range("B" & intRow).Select
.ActiveCell.Range(.Cells(1, 1), .Cells(1, y)).Select
.Selection.Copy
.Sheets("Sheet4").Select
.ActiveCell.Offset(0, 1).Range("A1").Select
.Selection.PasteSpecial Paste:=-4163, Operation:=-4142,
SkipBlanks _
:=False, Transpose:=True
.Application.CutCopyMode = False
.Selection.Sort Key1:=.ActiveCell, Order1:=2, Header:=0, _
OrderCustom:=1, MatchCase:=False, Orientation:=1, _
DataOption1:=0
.Sheets.Add
.Sheets("Sheet5").Move After:=.Sheets(5)
.Sheets("Sheet3").Select
.Columns("A:A").Select
.Selection.Copy
.Sheets("Sheet5").Select
.Range("A1").Select
.ActiveSheet.Paste
.Sheets("Sheet2").Select
.Range("C2").Select
Do
If .ActiveCell.Value = "" Then Exit Do
Name = .ActiveCell.Value
.Sheets("Sheet3").Select
.Range("B1").Select
Do
If .ActiveCell.Value = Name Then
.ActiveCell.EntireColumn.Select
.Selection.Copy
.Sheets("Sheet5").Select
.Range("B1").Select
Do
If .ActiveCell.Value = "" Then
.ActiveSheet.Paste
Exit Do
Else
.ActiveCell.Offset(0, 1).Select
End If
Loop
Exit Do
Else
.ActiveCell.Offset(0, 1).Select
End If
Loop
.Sheets("Sheet2").Select
.ActiveCell.Offset(1, 0).Select
Loop
.Sheets("Sheet3").Select
.Rows(intRow).Select
.Selection.Delete Shift:=-4162
.Application.DisplayAlerts = False
.Sheets("Sheet4").Delete
.Application.DisplayAlerts = True
.Sheets("Sheet5").Select
.Range("B1").Select
c = 0
Do Until .ActiveCell.Value = ""
c = c + 1
.ActiveCell.Value = c
.ActiveCell.Offset(0, 1).Select
Loop
intCol2 = .ActiveCell.Offset(0, -1).Column
.Range("B1").Select
If RowDel = True Then
Set myRange = .Worksheets("Sheet5").Range(.Cells(1,
1), .Cells(intRow - 2, c + 1))
Else
Set myRange = .Worksheets("Sheet5").Range(.Cells(1,
1), .Cells(intRow - 1, c + 1))
End If
.Charts.Add
.ActiveChart.ChartType = 52
.ActiveChart.SetSourceData Source:=myRange, PlotBy:=1
.ActiveChart.Location WHERE:=1
With .ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Individual Parts Inspected
by Product Line - " & Forms!frmCriteria!txtStartDate.Value & " to " &
Forms!frmCriteria!txtEndDate.Value
.Axes(1, 1).HasTitle = True
.Axes(1, 1).AxisTitle.Characters.Text = "Employee"
.Axes(2, 1).HasTitle = True
.Axes(2, 1).AxisTitle.Characters.Text = "# of Parts"
End With
.ActiveChart.HasDataTable = False
.Sheets("Chart1").Select
.ActiveChart.Legend.Select
.ActiveChart.Legend.LegendEntries
(.ActiveChart.SeriesCollection.Count).Select
.Selection.Delete
.ActiveChart.Legend.Select
For x = 1 To .ActiveChart.Legend.LegendEntries.Count
.ActiveChart.Legend.LegendEntries(x).LegendKey.Select
With .Selection.Border
.Weight = 2
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
.Selection.Interior.Pattern = 1
Next x
.Charts("Chart1").Select
.ActiveChart.ChartArea.Select
.Sheets("Sheet5").Select
.Range("A2").Select
x = 1
Do
Select Case .ActiveCell.Value
Case "0EPS"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 1
.Pattern = 1
End With
Case "0LID"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 39
.Pattern = 1
End With
Case "0OPS"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 19
.Pattern = 1
End With
Case "DELI"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 35
.Pattern = 1
End With
Case "PEPS"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 8
.Pattern = 1
End With
.Selection.Interior.ColorIndex = 8
Case "0PPC"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 7
.Pattern = 1
End With
Case "CCUP"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 41
.Pattern = 1
End With
Case "0PET"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 4
.Pattern = 1
End With
Case "IDIN"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 27
.Pattern = 1
End With
Case "EXTF"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 3
.Pattern = 1
End With
Case "HAVI"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 43
.Pattern = 1
End With
Case "FILM"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 53
.Pattern = 1
End With
Case "Gloss"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 10
.Pattern = 1
End With
End Select
x = x + 1
.Sheets("Sheet5").Select
.ActiveCell.Offset(1, 0).Select
Loop Until .ActiveCell.Value = ""
'Add Data Totals & Percent to the Stacked Bar Chart
.Charts("Chart1").Select
.ActiveChart.ChartArea.Select
.ActiveChart.SeriesCollection.NewSeries
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).Values = _
"=Sheet5!R" & intRow &
"C2: R" & intRow & "C" & c + 1
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).Name = "=""Totals"""
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).Select
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).ChartType = 4
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).Select
With .Selection.Border
.Weight = 2
.LineStyle = -4142
End With
With .Selection
.MarkerBackgroundColorIndex = -4142
.MarkerForegroundColorIndex = -4142
.MarkerStyle = -4142
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).ApplyDataLabels _
AutoText:=True, LegendKey:=False, ShowSeriesName:=False,
ShowCategoryName:=False, _
ShowValue:=True, ShowPercentage:=False,
ShowBubbleSize:=False
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).DataLabels.Select
With .Selection
.HorizontalAlignment = -4108
.VerticalAlignment = -4108
.ReadingOrder = -5002
.Position = 0
.Orientation = -4128
End With
.ActiveChart.PlotArea.Select
With .Selection.Border
.ColorIndex = 2
.Weight = 2
.LineStyle = 1
End With
.ActiveChart.PlotArea.Fill.OneColorGradient
msoGradientHorizontal, 1, 0.36
With .Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 2
End With
.ActiveChart.Legend.Select
y = .ActiveChart.SeriesCollection.Count - 1
.ActiveChart.Legend.LegendEntries(y).Select
.Selection.Delete
.ActiveChart.Axes(2).MajorGridlines.Select
.Selection.Delete
'Creating the background and average line
If IsNull(Forms!frmCriteria!cboPlant.Value) And IsNull(Forms!
frmCriteria!cboPerson.Value) _
And IsNull(Forms!frmCriteria!cboProdLine.Value) And strTL
= "" Then
.ActiveChart.PlotArea.Select
With .ActiveChart
.HasAxis(1, 1) = True
.HasAxis(1, 2) = True
.HasAxis(2, 1) = True
.HasAxis(2, 2) = True
End With
.ActiveChart.Axes(1, 1).Select
.ActiveChart.Axes(1, 1).CategoryType = -4105
.ActiveChart.Axes(1, 2).Select 'Here is where the code
blows up.
.ActiveChart.Axes(1, 1).CategoryType = -4105
.Sheets("Sheet5").Select
.Range("B" & intRow + 1).Select
.Sheets("Sheet2").Select
.Range("A1").Select
Do
If .ActiveCell.Value = "" Then
intRow3 = .ActiveCell.Offset(-1, 0).Row
Exit Do
End If
.ActiveCell.Offset(1, 0).Select
Loop
.Sheets("Sheet5").Select
.ActiveCell.FormulaR1C1 = "=R[-1]C/Sheet2!R" & intRow3 &
"C1"
.ActiveCell.Select
.Selection.AutoFill Destination:=.ActiveCell.Range("A1:" &
GetColumnLetter(intCol2 - 1) & "1"), Type:=0
.ActiveCell.Range("A1:" & GetColumnLetter(intCol2 - 1) &
"1").Select
.Selection.Style = "Percent"
.Selection.NumberFormat = "0.00%"
.Sheets("Chart1").Select
.ActiveChart.Axes(1, 2).Select
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).XValues = "=Sheet5!R" &
intRow + 1 & "C2:R" & intRow + 1 & "C" & intCol
.Sheets("Sheet5").Select
.ActiveCell.Offset(1, 0).Select
.ActiveCell.FormulaR1C1 = "=INT(Sheet2!R" & intRow3 &
"C1/17)"
intRow = .ActiveCell.Row
.Selection.AutoFill Destination:=.ActiveCell.Range
("A1:Q1"), Type:=0
y = 0
.Sheets("Chart1").Select
.ActiveChart.PlotArea.Select
.ActiveChart.SeriesCollection.NewSeries
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).Values = "=Sheet5!R" &
intRow & "C2:R" & intRow & "C18"
.ActiveChart.SeriesCollection(y).Name = "=""Average"""
.ActiveChart.SeriesCollection(y).Select
.ActiveChart.SeriesCollection(y).AxisGroup = 2
.ActiveChart.SeriesCollection(y).ChartType = 4
.ActiveChart.SeriesCollection(y).Select
With .Selection.Border
.ColorIndex = 56
.Weight = 2
.LineStyle = 1
End With
With .Selection
.MarkerBackgroundColorIndex = -4142
.MarkerForegroundColorIndex = -4142
.MarkerStyle = -4142
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
y = 0
.Charts("Chart1").Select
.ActiveChart.Legend.Select
y = .ActiveChart.Legend.LegendEntries.Count
.ActiveChart.Legend.LegendEntries(y).Select
.Selection.Delete
ElseIf IsNull(Forms!frmCriteria!cboPlant.Value) And IsNull
(Forms!frmCriteria!cboPerson.Value) _
And IsNull(Forms!frmCriteria!cboProdLine.Value) And strTL
<> "" Then
.ActiveChart.PlotArea.Select
With .ActiveChart
.HasAxis(1, 1) = True
.HasAxis(1, 2) = True
.HasAxis(2, 1) = True
.HasAxis(2, 2) = False
End With
.ActiveChart.Axes(1, 1).CategoryType = -4105
.ActiveChart.Axes(1, 2).CategoryType = -4105
.Sheets("Sheet5").Select
.Range("B" & intRow + 1).Select
.Sheets("Sheet2").Select
.Range("A2").Select
Do Until .ActiveCell.Value = ""
.ActiveCell.Offset(1, 0).Select
Loop
intRow2 = .ActiveCell.Offset(-1, 0).Row
.Sheets("Sheet5").Select
.ActiveCell.FormulaR1C1 = "=R[-1]C/Sheet2!R" & intRow2 &
"C1"
.Range("B1").Select
Do Until .ActiveCell.Value = ""
.ActiveCell.Offset(0, 1).Select
Loop
b = .ActiveCell.Offset(0, -1).Value
Select Case b
Case 1
strLetter = "A"
Case 2
strLetter = "B"
Case 3
strLetter = "C"
Case 4
strLetter = "D"
Case 5
strLetter = "E"
Case 6
strLetter = "F"
Case 7
strLetter = "G"
Case 8
strLetter = "H"
Case 9
strLetter = "I"
Case 10
strLetter = "J"
Case 11
strLetter = "K"
Case 12
strLetter = "L"
Case 13
strLetter = "M"
Case 14
strLetter = "N"
Case 15
strLetter = "O"
Case 16
strLetter = "P"
Case 17
strLetter = "Q"
Case 18
strLetter = "R"
Case 19
strLetter = "S"
Case 20
strLetter = "T"
Case 21
strLetter = "U"
Case 22
strLetter = "V"
Case 23
strLetter = "W"
Case 24
strLetter = "X"
Case 25
strLetter = "Y"
Case 26
strLetter = "Z"
End Select
.Range("B" & intRow + 1).Select
.Selection.AutoFill Destination:=.ActiveCell.Range("A1:" &
strLetter & "1"), Type:= _
0
.ActiveCell.Range("A1:" & strLetter & "1").Select
.Selection.Style = "Percent"
.Selection.NumberFormat = "0.00%"
.Sheets("Chart1").Select
.ActiveChart.Axes(1, 2).Select
.ActiveChart.PlotArea.Select
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).XValues = "=Sheet5!R" &
intRow + 1 & "C2:R" & intRow + 1 & "C" & intCol
.Sheets("Sheet5").Select
.ActiveCell.Offset(1, 0).Select
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Int(Sum
(tmpReports.NumOfSets)/21) AS AverageSets" _
& " FROM tmpReports WHERE tmpReports.StartedDate
Between #" & _
[Forms]![frmCriteria]![txtStartDate] & "# And #" &
[Forms]![frmCriteria]![txtEndDate] & "#;")
.ActiveCell.FormulaR1C1 = "=INT(" & rst!AverageSets & ")"
intRow = .ActiveCell.Row
.Selection.AutoFill Destination:=.ActiveCell.Range("A1:" &
strLetter & "1"), Type:=0
y = 0
.Sheets("Chart1").Select
.ActiveChart.PlotArea.Select
.ActiveChart.SeriesCollection.NewSeries
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).Values = "=Sheet5!R" &
intRow & "C2:R" & intRow & "C" & b + 1
.ActiveChart.SeriesCollection(y).Name = "=""Average"""
.ActiveChart.SeriesCollection(y).Select
.ActiveChart.SeriesCollection(y).AxisGroup = 2
.ActiveChart.SeriesCollection(y).ChartType = 4
.ActiveChart.SeriesCollection(y).Select
With .Selection.Border
.ColorIndex = 56
.Weight = 2
.LineStyle = 1
End With
With .Selection
.MarkerBackgroundColorIndex = -4142
.MarkerForegroundColorIndex = -4142
.MarkerStyle = -4142
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
y = 0
.Charts("Chart1").Select
.ActiveChart.Legend.Select
y = .ActiveChart.Legend.LegendEntries.Count
.ActiveChart.Legend.LegendEntries(y).Select
.Selection.Delete
End If
End With
msg = MsgBox("Do you wish to run the Weight Factor Report?",
vbYesNo, _
"Run Weight Factor Report?")
xl.Sheets("Sheet1").Name = "Data"
xl.Sheets("Sheet2").Name = "Rank"
xl.Sheets("Chart1").Name = "Ind. Parts Chart"
xl.Sheets("Sheet5").Name = "Ind. Parts Table"
If msg = vbYes Then
Call WeightFactor
'Add Weight Factor Values to the report
xl.Workbooks.Open FileName:="J:\QA\Productivity Reports
\WeightFactorValues.xls"
xl.Sheets("Sheet1").Select
xl.Sheets("Sheet1").Copy After:=xl.Workbooks
("IndividualProductReport1").Sheets(9)
xl.Windows("WeightFactorValues.xls").Activate
xl.ActiveWindow.Close
'Delete sheets that are no longer needed
xl.Sheets("Sheet6").Delete
xl.Sheets("Sheet3").Delete
'Rename the sheets
xl.Sheets("Sheet7").Name = "Weight Factor Rank"
xl.Sheets("Sheet8").Name = "Weight Factor Table"
xl.Sheets("Chart2").Name = "Weight Factor Chart"
xl.Sheets("Sheet1").Name = "Weight Factor Conv. Sheet"
End If
DoEvents
xl.Sheets("Data").Select
EndGetData:
msg = MsgBox("Do you wish to save the file at this time?",
vbYesNo, "Save File")
If msg = vbYes Then
xl.ActiveWorkbook.SaveAs "J:\QA\Productivity Reports\" & _
InputBox("Please type a filename...", "Save As") & ".xls"
MsgBox "File saved as " & xl.ActiveWorkbook.FullName
End If
GetData_Err_Exit:
xl.Application.Visible = True
xl.Application.Interactive = True
xl.Application.DisplayAlerts = True
xl.Application.ScreenUpdating = True
DoCmd.Close acForm, "frmProgressBar", acSaveNo
Set rst = Nothing
db.Close
Set db = Nothing
Exit Sub
GetData_Err:
If Err = 2100 Then
Err.Clear
Resume Next
ElseIf Err = 91 Then
DoCmd.Close acForm, "frmProgressBar", acSaveNo
Set rst = Nothing
Set db = Nothing
Exit Sub
Else
MsgBox Err.Description, , Err.Number
Err.Clear
Resume GetData_Err_Exit
End If
End Sub
I ma having some difficulty with generating a secondary axis in Excel
07. The code worked in Excel 2003 but when I upgraded to 07, most of
this appears to work.
The code is below. I commented on the section that it errors on.
Sub GetData(datStart As Date, datEnd As Date, strTL As String)
Dim db As DAO.Database, rst As DAO.Recordset, qdfReports As
DAO.QueryDef
Dim strWHERE As String, x As Variant, intRow As Integer, fld As
Variant
Dim strSQL As String, strFROM As String, strStaffID As String, strFile
As String
Dim Name As String, y As Integer, myRange As Object, RowDel As Boolean
Dim ColMax As Integer, intRow2 As Integer
Dim r As Integer, PctDone As Single, msg As Integer, b As Integer
Dim intCol As Integer, c As Integer, strLetter As String, intRow3 As
Integer
Dim intColumns As Integer, s As Variant, intCol2 As Integer
'Dim xl As Object
Dim xl As Excel.Application
On Error GoTo GetData_Err
'This macro needs to run to distingusih HAVI testing from regular
testing
'Macro: macDoFirst
DoCmd.RunMacro "macDoFirst"
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Max(tmpReports.CompleteDate) AS
MaxOfCompleteDate" _
& " FROM tmpReports;")
If Forms!frmCriteria!txtEndDate > rst!MaxOfCompleteDate Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * from tmpReports"
Set qdfReports = db.QueryDefs("qryReports2")
qdfReports.Execute
DoCmd.SetWarnings True
Set qdfReports = Nothing
End If
rst.Close
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tmpReports SET tmpReports.UserName =" _
& " 'Susan Skidmore' WHERE (((tmpReports.UserName)='Sue
Skidmore'));"
DoCmd.RunSQL "UPDATE tmpReports SET tmpReports.UserName =" _
& " 'Kimberly Van Valkenburgh' WHERE (((tmpReports.UserName)
='Kim Van Valkenburgh'));"
DoCmd.SetWarnings True
Set xl = CreateObject("Excel.Application")
'Set xl = Excel.Application
With xl
.Visible = True
.Workbooks.Open "\\files-2k1\ENG\QA\Productivity Reports
\IndividualProductReport.xlt"
.Interactive = True
.DisplayAlerts = True
.ScreenUpdating = True
.Sheets("Sheet1").Select
'Select the correct query from what matches frmCriteria
If Not IsNull(Forms!frmCriteria!cboPlant.Value) Then
If Not IsNull(Forms!frmCriteria!cboPerson.Value) Then
If Not IsNull(Forms!frmCriteria!cboProdLine.Value)
Then
Set rst = db.OpenRecordset("SELECT * FROM
tmpReports WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & " AND UserName
= '" & Forms!frmCriteria!cboPerson.Value & "'" _
& " AND ProductLineCode = '" & Forms!frmCriteria!
cboProdLine.Value & "' AND CompleteDate" _
& " BETWEEN #" & datStart & "# AND #" & datEnd &
"#")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Complated Production Dates: " _
& Forms!frmCriteria!txtStartDate.Value &
" And " & Forms!frmCriteria!txtEndDate.Value & ";" _
& " Employee - " & Forms!frmCriteria!
cboPerson.Value
GoTo Information
Else
Set rst = db.OpenRecordset("SELECT * FROM
tmpReports WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "' AND
UserName = '" & Forms!frmCriteria!cboPerson.Value & "'" _
& " AND CompleteDate BETWEEN #" & datStart & "#
AND #" & datEnd & "#")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!txtStartDate.Value &
" And " & Forms!frmCriteria!txtEndDate.Value
GoTo Information
End If
ElseIf Forms!frmCriteria!cboTL.Value <> "" Then
If Not IsNull(Forms!frmCriteria!cboProdLine.Value)
Then
Select Case strTL
Case "Annette Hornbeck"
db.Execute "DELETE * FROM tblReportsAH"
db.Execute "INSERT INTO tblReportsAH
( ProductLineCode, Name, ID," _
& " NumOfSets, StartedDate,
StartedTime, LabStartDate, LabStartTime," _
& " CompleteDate, CompletedTime,
UserName ) SELECT tmpReports.ProductLineCode," _
& " tmpReports.Name, tmpReports.ID,
tmpReports.NumOfSets," _
& " tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime,
tmpReports.CompleteDate, tmpReports.CompletedTime," _
& " tmpReports.UserName FROM
tmpReports WHERE (((tmpReports.UserName) In" _
& " ('Eric Brenner','James
Kring','Tammy Latimer','Archana Patel'" _
& ",'Kim Van Valkenburgh','Duc
Vo')));"
Set rst = db.OpenRecordset("SELECT * FROM
tblReportsAH WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "'
AND ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "'
AND StartedDate" _
& " BETWEEN #" & datStart & "# AND #"
& datEnd & "# ORDER BY UserName, StartedDate")
GoTo Information
Case "Dennis Connolly"
db.Execute "DELETE * FROM tblReportsDC"
db.Execute "INSERT INTO tblReportsDC
( ProductLineCode, Name, ID," _
& " NumOfSets, StartedDate,
StartedTime, LabStartDate, LabStartTime," _
& " CompleteDate, CompletedTime,
UserName ) SELECT tmpReports.ProductLineCode," _
& " tmpReports.Name, tmpReports.ID,
tmpReports.NumOfSets," _
& " tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime,
tmpReports.CompleteDate, tmpReports.CompletedTime," _
& " tmpReports.UserName FROM
tmpReports WHERE (((tmpReports.UserName) In" _
& " ('Ron Erwin','Jim Fairly','Tara
Franks','Kelly Hull','Sandra Mullen'" _
& ",'Jeff Shirk','Sue Skidmore')));"
Set rst = db.OpenRecordset("SELECT * FROM
tblReportsDC WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "'
AND ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "'
AND StartedDate" _
& " BETWEEN #" & datStart & "# AND #"
& datEnd & "# ORDER BY UserName, StartedDate")
GoTo Information
Case "Martin Finucane"
db.Execute "DELETE * FROM tblReportsMF"
db.Execute "INSERT INTO tblReportsMF
( ProductLineCode, Name, ID," _
& " NumOfSets, StartedDate,
StartedTime, LabStartDate, LabStartTime," _
& " CompleteDate, CompletedTime,
UserName ) SELECT tmpReports.ProductLineCode," _
& " tmpReports.Name, tmpReports.ID,
tmpReports.NumOfSets," _
& " tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime,
tmpReports.CompleteDate, tmpReports.CompletedTime," _
& " tmpReports.UserName FROM
tmpReports WHERE (((tmpReports.UserName) In" _
& " ('Claude Morey','Linda
Worgess')));"
Set rst = db.OpenRecordset("SELECT * FROM
tblReportsMF WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "'
AND ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "'
AND StartedDate" _
& " BETWEEN #" & datStart & "# AND #"
& datEnd & "# ORDER BY UserName, StartedDate")
GoTo Information
Case "Sharon Larsen"
db.Execute "DELETE * FROM tblReportsSL"
db.Execute "INSERT INTO tblReportsSL
( ProductLineCode, Name, ID," _
& " NumOfSets, StartedDate,
StartedTime, LabStartDate, LabStartTime," _
& " CompleteDate, CompletedTime,
UserName ) SELECT tmpReports.ProductLineCode," _
& " tmpReports.Name, tmpReports.ID,
tmpReports.NumOfSets," _
& " tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime,
tmpReports.CompleteDate, tmpReports.CompletedTime," _
& " tmpReports.UserName FROM
tmpReports WHERE (((tmpReports.UserName) In" _
& " ('Sue Fisher','Brian Krohn','Gary
Marchand','Penny Pittman'," _
& "'Saskia Riley','Chue Vang')));"
Set rst = db.OpenRecordset("SELECT * FROM
tblReportsSL WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "'
AND ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "'
AND StartedDate" _
& " BETWEEN #" & datStart & "# AND #"
& datEnd & "# ORDER BY UserName, StartedDate")
GoTo Information
End Select
Else
Select Case strTL
Case "Annette Hornbeck"
Set qdfReports = db.QueryDefs
("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name," _
& " tmpReports.ID, tmpReports.NumOfSets,
tmpReports.StartedDate," _
& " tmpReports.StartedTime,
tmpReports.LabStartDate, tmpReports.LabStartTime," _
& " tmpReports.CompleteDate,
tmpReports.CompletedTime, tblEmployee.UserName" _
& " FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE
tmpReports.Name = '" & Forms!frmCriteria!cboPlant.Value _
& "' AND tmpReports.CompleteDate Between
#" & datStart & "# And #" & datEnd & "#" _
& " AND tblEmployee.AH = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID,
qryReportsAll.NumOfSets, qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN
qryReportsAll ON tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.AH = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!
txtStartDate.Value & " And " & Forms!frmCriteria!txtEndDate.Value &
";" _
& " Team Leader - " & Forms!
frmCriteria!cboTL.Value
GoTo Information
Case "Dennis Connolly"
Set qdfReports = db.QueryDefs
("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name," _
& " tmpReports.ID, tmpReports.NumOfSets,
tmpReports.StartedDate," _
& " tmpReports.StartedTime,
tmpReports.LabStartDate, tmpReports.LabStartTime," _
& " tmpReports.CompleteDate,
tmpReports.CompletedTime, tblEmployee.UserName" _
& " FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE
tmpReports.Name = '" & Forms!frmCriteria!cboPlant.Value _
& "' AND tmpReports.CompleteDate Between
#" & datStart & "# And #" & datEnd & "#" _
& " AND tblEmployee.DC = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID,
qryReportsAll.NumOfSets, qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN
qryReportsAll ON tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.DC = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!
txtStartDate.Value & " And " & Forms!frmCriteria!txtEndDate.Value &
";" _
& " Team Leader - " & Forms!
frmCriteria!cboTL.Value
GoTo Information
Case "Martin Finucane"
Set qdfReports = db.QueryDefs
("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name," _
& " tmpReports.ID, tmpReports.NumOfSets,
tmpReports.StartedDate," _
& " tmpReports.StartedTime,
tmpReports.LabStartDate, tmpReports.LabStartTime," _
& " tmpReports.CompleteDate,
tmpReports.CompletedTime, tblEmployee.UserName" _
& " FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE
tmpReports.Name = '" & Forms!frmCriteria!cboPlant.Value _
& "' AND tmpReports.CompleteDate Between
#" & datStart & "# And #" & datEnd & "#" _
& " AND tblEmployee.MF= True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID,
qryReportsAll.NumOfSets, qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN
qryReportsAll ON tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.MF = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!
txtStartDate.Value & " And " & Forms!frmCriteria!txtEndDate.Value &
";" _
& " Team Leader - " & Forms!
frmCriteria!cboTL.Value
GoTo Information
Case "Sharon Larsen"
Set qdfReports = db.QueryDefs
("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name," _
& " tmpReports.ID, tmpReports.NumOfSets,
tmpReports.StartedDate," _
& " tmpReports.StartedTime,
tmpReports.LabStartDate, tmpReports.LabStartTime," _
& " tmpReports.CompleteDate,
tmpReports.CompletedTime, tblEmployee.UserName" _
& " FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE
tmpReports.Name = '" & Forms!frmCriteria!cboPlant.Value _
& "' AND tmpReports.CompleteDate Between
#" & datStart & "# And #" & datEnd & "#" _
& " AND tblEmployee.SL = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID,
qryReportsAll.NumOfSets, qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN
qryReportsAll ON tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.SL = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!
frmCriteria!cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!
txtStartDate.Value & " And " & Forms!frmCriteria!txtEndDate.Value &
";" _
& " Team Leader - " & Forms!
frmCriteria!cboTL.Value
GoTo Information
End Select
End If
ElseIf Not IsNull(Forms!frmCriteria!cboProdLine.Value)
Then
Set rst = db.OpenRecordset("SELECT * FROM tmpReports
WHERE Name = '" & _
Forms!frmCriteria!cboPlant.Value & "' AND
ProductLineCode = '" & Forms!frmCriteria!cboProdLine.Value & "' AND
StartedDate" _
& " BETWEEN #" & datStart & "# AND #" & datEnd &
"# ORDER BY tmpReports.UserName, tmpReports.StartedDate")
Else
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT tmpReports.ProductLineCode,
tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate, tmpReports.LabStartTime,
tmpReports.CompleteDate," _
& " tmpReports.CompletedTime, tblEmployee.UserName,
tblEmployee.AH FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.Name = '" & Forms!frmCriteria!
cboPlant.Value & "' AND tmpReports.CompleteDate" _
& " Between #" & datStart & "# And #" & datEnd & "#
And tblEmployee.IsCQATech = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblEmployee.IsCQATech = True ORDER BY" _
& " tblemployee.UserName, qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
.Range("A2").Value = "Plant - " & Forms!frmCriteria!
cboPlant.Value & "; Completed Production Dates: " _
& Forms!frmCriteria!txtStartDate.Value &
" and " & Forms!frmCriteria!txtEndDate.Value
GoTo Information
End If
End If
If Not IsNull(Forms!frmCriteria!cboPerson.Value) Then
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT tmpReports.ProductLineCode,
tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime, tmpReports.LabStartDate," _
& " tmpReports.LabStartTime, tmpReports.CompleteDate,
tmpReports.CompletedTime," _
& " tblEmployee.UserName FROM tblEmployee LEFT JOIN
tmpReports ON tblEmployee.UserName" _
& " = tmpReports.UserName WHERE tmpReports.CompleteDate
Between #" & datStart & "# And" _
& " #" & datEnd & "# AND tblEmployee.UserName = '" &
Forms!frmCriteria!cboPerson.Value & "'" _
& " ORDER BY tblEmployee.UserName,
tmpReports.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("qryReportsAll")
GoTo Information
End If
If IsNull(Forms!frmCriteria!cboProdLine.Value) Then
Select Case strTL
Case "Annette Hornbeck"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.AH FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.LabStartDate Is Not Null AND
tmpReports.CompleteDate Between #" & datStart & "# And #" & datEnd &
"# And" _
& " tblEmployee.AH=True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.AH
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Dennis Connolly"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.DC FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName" _
& " WHERE tmpReports.CompleteDate Between #" &
datStart & "# And #" & datEnd & "# And" _
& " tblEmployee.DC = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.DC
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Martin Finucane"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.MF FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.CompleteDate Between #" &
datStart & "# And #" & datEnd & "# And" _
& " tblEmployee.MF=True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.MF
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Sharon Larsen"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.SL FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.CompleteDate Between #" &
datStart & "# And #" & datEnd & "# And" _
& " tblEmployee.SL=True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.SL
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case Else
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName=tmpReports.UserName" _
& " WHERE tmpReports.CompleteDate Between #" &
datStart & "# And #" & datEnd & "# And" _
& " tblEmployee.IsCQATech=True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblemployee.IsCQATech = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
End Select
.Range("A2").Value = "Completed Production Dates: " &
datStart & " and " & datEnd
Else
Select Case strTL
Case "Annette Hornbeck"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = " & Forms!
frmCriteria!cboProdLine.Value & " AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.AH = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.AH
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Dennis Connolly"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = " & Forms!
frmCriteria!cboProdLine.Value & " AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.DC = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.DC
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Martin Finucane"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = " & Forms!
frmCriteria!cboProdLine.Value & " AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.MF = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.MF
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case "Sharon Larsen"
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = " & Forms!
frmCriteria!cboProdLine.Value & " AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.SL = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE tblemployee.SL
= True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
Case Else
Set qdfReports = db.QueryDefs("qryReportsAll")
qdfReports.SQL = "SELECT
tmpReports.ProductLineCode, tmpReports.Name, tmpReports.ID," _
& " tmpReports.NumOfSets, tmpReports.StartedDate,
tmpReports.StartedTime," _
& " tmpReports.LabStartDate,
tmpReports.LabStartTime, tmpReports.CompleteDate," _
& " tmpReports.CompletedTime,
tblEmployee.UserName, tblEmployee.IsCQATech FROM" _
& " tblEmployee LEFT JOIN tmpReports ON
tblEmployee.UserName = tmpReports.UserName WHERE" _
& " tmpReports.ProductLineCode = '" & Forms!
frmCriteria!cboProdLine.Value & "' AND" _
& " tmpReports.CompleteDate Between #" & datStart
& "# And #" & datEnd & "# AND" _
& " tblEmployee.IsCQATech = True;"
Set qdfReports = Nothing
Set qdfReports = db.QueryDefs("Query2")
qdfReports.SQL = "SELECT
qryReportsAll.ProductLineCode, qryReportsAll.Name," _
& " qryReportsAll.ID, qryReportsAll.NumOfSets,
qryReportsAll.StartedDate," _
& " qryReportsAll.StartedTime,
qryReportsAll.LabStartDate, qryReportsAll.LabStartTime," _
& " qryReportsAll.CompleteDate,
qryReportsAll.CompletedTime, tblemployee.UserName" _
& " FROM tblemployee LEFT JOIN qryReportsAll ON
tblemployee.UserName" _
& " = qryReportsAll.UserName WHERE
tblemployee.IsCQATech = True ORDER BY" _
& " tblemployee.UserName,
qryReportsAll.StartedDate;"
Set qdfReports = Nothing
Set rst = db.OpenRecordset("Query2")
End Select
.Range("A2").Value = "Product Line - " & Forms!
frmCriteria!cboProdLine.Value & "; Completed Production Dates: " _
& datStart & " and " & datEnd
End If
Information: 'Load the information into the Excel sheet
DoCmd.OpenForm "frmProgressBar"
DoEvents
.Range("A4").Select
rst.MoveLast
ColMax = 13
rst.MoveFirst
Do Until rst.EOF
.ActiveCell.Value = rst!Name
.ActiveCell.Offset(0, 1).Value = rst!ProductLineCode
.ActiveCell.Offset(0, 2).Value = rst!ID
.ActiveCell.Offset(0, 3).Value = 1
.ActiveCell.Offset(0, 3).HorizontalAlignment = -4108
.ActiveCell.Offset(0, 4).Value = rst!NumOfSets
.ActiveCell.Offset(0, 4).HorizontalAlignment = -4108
.ActiveCell.Offset(0, 5).Value = rst!StartedDate
.ActiveCell.Offset(0, 6).Value = rst!StartedTime
If rst!ProductLineCode = "0EPS" Then
.ActiveCell.Offset(0, 7).Value = rst!LabStartDate
.ActiveCell.Offset(0, 8).Value = rst!LabStartTime
Else
.ActiveCell.Offset(0, 7).Value = rst!CompleteDate
.ActiveCell.Offset(0, 8).Value = rst!CompletedTime
End If
.ActiveCell.Offset(0, 9).Value = rst!UserName
If rst!ProductLineCode = "0EPS" Then
If IsNull(rst!LabStartDate) Or rst!LabStartDate = ""
Then
.ActiveCell.Offset(0, 10).Value = 0
.ActiveCell.Offset(0, 11).FormulaR1C1 = 0
Else
.ActiveCell.Offset(0, 10).Value = TestTime(rst!
StartedDate, rst!StartedTime, rst!LabStartDate, rst!LabStartTime)
.ActiveCell.Offset(0, 11).FormulaR1C1 = "=INT(RC
[-1]/RC[-7])"
End If
Else
If IsNull(rst!CompleteDate) Or rst!CompleteDate = ""
Then
.ActiveCell.Offset(0, 10).Value = 0
.ActiveCell.Offset(0, 11).FormulaR1C1 = 0
Else
.ActiveCell.Offset(0, 10).Value = TestTime(rst!
StartedDate, rst!StartedTime, rst!CompleteDate, rst!CompletedTime)
.ActiveCell.Offset(0, 11).FormulaR1C1 = "=INT(RC
[-1]/RC[-7])"
End If
End If
.ActiveCell.Offset(0, 10).HorizontalAlignment = -4108
.ActiveCell.Offset(0, 11).HorizontalAlignment = -4108
Select Case .ActiveCell.Offset(0, 1).Value
Case "0EPS"
Select Case rst!Name
Case "Argentina", "Australia", "Campbellford",
"Lodi", "Tijuana", "Tumwater", "United Kingdom"
.ActiveCell.Offset(0, 12).Value = 3
Case "Atlacomulco", "Aurora", "Lancaster",
"Mason", "Plant City", "Quitman"
.ActiveCell.Offset(0, 12).Value = 5
Case "Corona", "Horse Cave", "Leola", "Leola
South", "Lithonia", "Waxahachie"
.ActiveCell.Offset(0, 12).Value = 10
Case Else
.ActiveCell.Offset(0, 12).Value = "No Days
Mentioned"
End Select
Case "0LID"
Select Case rst!Name
Case "Corona"
.ActiveCell.Offset(0, 12).Value = 2
Case "Leola"
.ActiveCell.Offset(0, 12).Value = 11
Case "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 12
Case "Randleman"
.ActiveCell.Offset(0, 12).Value = 5
Case "Tijuana"
.ActiveCell.Offset(0, 12).Value = 8
End Select
Case "0OPS"
Select Case rst!Name
Case "Corona"
.ActiveCell.Offset(0, 12).Value = 3
Case "Leola"
.ActiveCell.Offset(0, 12).Value = 7
Case "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 8
End Select
Case "0PET"
Select Case rst!Name
Case "Randleman"
.ActiveCell.Offset(0, 12).Value = 5
End Select
Case "0PPC"
Select Case rst!Name
Case "Leola", "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 4
End Select
Case "CCUP"
Select Case rst!Name
Case "Leola"
.ActiveCell.Offset(0, 12).Value = 4
Case "Randleman"
.ActiveCell.Offset(0, 12).Value = 6
End Select
Case "PCCP"
.ActiveCell.Offset(0, 12).Value = 2
Case "DELI"
Select Case rst!Name
Case "Leola"
.ActiveCell.Offset(0, 12).Value = 2
End Select
Case "IDIN"
Select Case rst!Name
Case "Leola", "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 2
End Select
Case "EXTF"
.ActiveCell.Offset(0, 12).Value = 4
Case "PEPS"
Select Case rst!Name
Case "Argentina", "Atlacomulco", "Aurora",
"Australia", "Campbellford", _
"Lancaster", "Leola", "Mason", "Plant
City", "Tijuana", "Tumwater", _
"United Kingdom", "Horse Cave"
.ActiveCell.Offset(0, 12).Value = 2
Case "Corona", "Lodi", "Quitman",
"Waxahachie", "Lithonia"
.ActiveCell.Offset(0, 12).Value = 3
End Select
Case "HAVI"
.ActiveCell.Offset(0, 12).Value = 5
Case "FILM"
.ActiveCell.Offset(0, 12).Value = 0
Case Else
.ActiveCell.Offset(0, 12).Value = "Not Mentioned"
End Select
.ActiveCell.Offset(0, 12).HorizontalAlignment = -4108
If .ActiveCell.Offset(0, 7).Value = "" Then
.ActiveCell.Offset(0, 13).Value = 0
Else
'Divide by 480 because it = 8 hours * 60 minutes
.ActiveCell.Offset(0, 13).FormulaR1C1 = "=INT(RC[-3]/
480)"
.ActiveCell.Offset(0, 13).NumberFormat = "0"
End If
If .ActiveCell.Offset(0, 13).Value > .ActiveCell.Offset(0,
12).Value Then
.ActiveCell.Offset(0, 13).Interior.ColorIndex = 6
.ActiveCell.Offset(0, 13).Interior.Pattern = 1
End If
.ActiveCell.Offset(0, 13).HorizontalAlignment = -4108
.ActiveCell.Offset(0, 13).NumberFormat = 0
.ActiveCell.Offset(1, 0).Select
'Update the ProgressBar
Forms!frmProgressBar!ProgressBarB.Width = _
(Forms!frmProgressBar!ProgressBarA.Width /
rst.RecordCount) * rst.AbsolutePosition
Forms!frmProgressBar!lblPercent.Caption = Format
(rst.AbsolutePosition / rst.RecordCount, "Percent")
Forms("frmProgressBar").Repaint
DoEvents
rst.MoveNext
Loop
Forms!frmProgressBar!ProgressBarB.Width = _
(Forms!frmProgressBar!ProgressBarA.Width /
rst.RecordCount) * rst.RecordCount
Forms!frmProgressBar!lblPercent.Caption = "100.00%"
'Disclaimer
.ActiveCell.Offset(1, 0).Select
.ActiveCell.Value = "Note: End Dates that are before the
Completed Dates are" _
& " Lab End Dates for Plain Foam Cup"
intRow = .ActiveCell.Row
.Range("A" & intRow & ":N" & intRow).Select
.Selection.Font.Italic = True
.Selection.Merge
If Forms!frmCriteria!cboPerson.Value <> "" Then
Call WeightFactor
GoTo EndGetData
End If
rst.Close
.Cells.Select
.Cells.EntireColumn.AutoFit
.Range("J4").Select
'Sum each persons productions
Do
If .ActiveCell.Value = "" And .ActiveCell.Offset(1,
0).Value = "" Then
Exit Do
ElseIf .ActiveCell.Value = .ActiveCell.Offset(1, 0).Value
Then
.ActiveCell.Offset(1, 0).Select
Else
.ActiveCell.Offset(1, 0).Select
.Rows(.ActiveCell.Row & ":" & .ActiveCell.Offset(1,
0).Row).Select
.Selection.Insert Shift:=1
.ActiveCell.Select
If .ActiveCell.Offset(0, 13).Interior.ColorIndex = 6
Then
.ActiveCell.Offset(0, 13).Interior.ColorIndex =
-4142
.ActiveCell.Offset(1, 13).Interior.ColorIndex =
-4142
End If
.ActiveCell.Offset(0, 3).Select
.Selection.Borders(5).LineStyle = -4142
.Selection.Borders(6).LineStyle = -4142
.Selection.Borders(7).LineStyle = -4142
With .Selection.Borders(8)
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
.Selection.Borders(9).LineStyle = -4142
.Selection.Borders(10).LineStyle = -4142
.ActiveCell.Offset(0, 1).Select
.Selection.Borders(5).LineStyle = -4142
.Selection.Borders(6).LineStyle = -4142
.Selection.Borders(7).LineStyle = -4142
With .Selection.Borders(8)
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
.Selection.Borders(9).LineStyle = -4142
.Selection.Borders(10).LineStyle = -4142
.ActiveCell.Offset(0, -1).Select
'Find out how many cells to go up.
x = -1
Do
If IsNumeric(.ActiveCell.Offset(x, 0).Value) And
Not .ActiveCell.Offset(x, 0).Value = "" Then
x = x - 1
Else
Exit Do
End If
Loop
.ActiveCell.FormulaR1C1 = "=SUM(R[" & x + 1 & "]C:R[-1]
C)"
.ActiveCell.Font.Color = 255
.ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(R[" & x +
1 & "]C:R[-1]C)"
.ActiveCell.Offset(0, 1).Copy
.Sheets("Sheet2").Select
.Range("A2").Select
Do
If .ActiveCell.Value <> "" Then
.ActiveCell.Offset(1, 0).Select
Else
Exit Do
End If
Loop
.ActiveCell.PasteSpecial -4163
.Sheets("Sheet1").Select
.ActiveCell.Offset(-1, 6).Copy
.Sheets("Sheet2").Select
.ActiveCell.Offset(0, 2).Select
.ActiveCell.PasteSpecial -4163
.Sheets("Sheet1").Select
.ActiveCell.Offset(0, 1).Font.Color = 255
.ActiveCell.Offset(0, 10).Select
.Selection.Borders(5).LineStyle = -4142
.Selection.Borders(6).LineStyle = -4142
.Selection.Borders(7).LineStyle = -4142
With .Selection.Borders(8)
.LineStyle = 1
.Weight = -4138
.ColorIndex = -4105
End With
.Selection.Borders(9).LineStyle = -4142
.Selection.Borders(10).LineStyle = -4142
x = -1
Do
If IsNumeric(.ActiveCell.Offset(x, 0).Value) And
Not .ActiveCell.Offset(x, 0).Value = "" Then
x = x - 1
Else
Exit Do
End If
Loop
.ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & x & "]C:R[-1]
C)"
.ActiveCell.NumberFormat = "0.0"
.ActiveCell.Font.Color = 255
.ActiveCell.Offset(2, -4).Select
End If
Loop
'Sum Sheet2 and sort in decending order (Z-A)
.Sheets("Sheet2").Select
.Range("C2").Select
Do
If .ActiveCell.Value <> "" Then
.ActiveCell.Offset(1, 0).Select
Else
.ActiveCell.Offset(-1, 0).Select
intRow = .ActiveCell.Row
Exit Do
End If
Loop
.Range("A1:C" & intRow).Select
.Range("A1:C" & intRow).Sort .Cells(1, 1), 2
DoEvents
.Range("B2").Select
x = 1
Do
If .ActiveCell.Offset(0, -1).Value <> "" Then
.ActiveCell.Value = x
x = x + 1
.ActiveCell.Offset(1, 0).Select
Else
Exit Do
End If
Loop
.Range("A2").Select
Do
If .ActiveCell.Value = "" Then
intRow = .ActiveCell.Row
Exit Do
Else
.ActiveCell.Offset(1, 0).Select
End If
Loop
.ActiveCell.FormulaR1C1 = "=SUM(R[-" & intRow - 2 & "]C:R[-1]
C)"
.Selection.Font.Bold = True
.Sheets("Sheet1").Select
.Range("A4").Select
'Stacked Bar Chart
.Sheets("Sheet3").Select
.Rows("1:9").Select
.Selection.Delete -4162
Set rst = db.OpenRecordset("TRANSFORM Sum(Query2.NumOfSets) AS
SumOfNumOfSets SELECT" _
& " Query2.ProductLineCode FROM Query2 GROUP BY
Query2.ProductLineCode PIVOT Query2.UserName;")
.Range("A1").Select
rst.MoveFirst
For Each fld In rst.Fields
.Selection.Borders(5).LineStyle = -4142
.Selection.Borders(6).LineStyle = -4142
With .Selection.Borders(7)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With .Selection.Borders(8)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With .Selection.Borders(9)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With .Selection.Borders(10)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With .Selection.Interior
.ColorIndex = 15
.Pattern = 1
End With
.ActiveCell.Value = fld.Name
.ActiveCell.Offset(0, 1).Select
x = x + 1
Next fld
intCol = .ActiveCell.Offset(0, -1).Column
.ActiveCell.Offset(1, -intCol).Select
Do Until rst.EOF
For Each fld In rst.Fields
.ActiveCell.Value = fld.Value
.ActiveCell.Offset(0, 1).Select
DoEvents
Next fld
.ActiveCell.Offset(1, -intCol).Select
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
RowDel = False
If .Range("A2").Value = "" Then
.Rows("2:2").Delete
RowDel = True
End If
intRow = .ActiveCell.Offset(0, 0).Row
.Range("B" & intRow).Select
Do
.ActiveCell.FormulaR1C1 = "=SUM(R[-" & intRow - 2 & "]C:R
[-1]C)"
.ActiveCell.Offset(0, 1).Select
y = y + 1
Loop Until .ActiveCell.Offset(-intRow + 1, 0).Value = ""
.Sheets.Add
.Sheets("Sheet4").Move After:=.Sheets(4)
.Sheets("Sheet3").Select
.Range("B" & intRow).Select
.ActiveCell.Range(.Cells(1, 1), .Cells(1, y)).Select
.Selection.Copy
.Sheets("Sheet4").Select
.ActiveCell.Offset(0, 1).Range("A1").Select
.Selection.PasteSpecial Paste:=-4163, Operation:=-4142,
SkipBlanks _
:=False, Transpose:=True
.Application.CutCopyMode = False
.Selection.Sort Key1:=.ActiveCell, Order1:=2, Header:=0, _
OrderCustom:=1, MatchCase:=False, Orientation:=1, _
DataOption1:=0
.Sheets.Add
.Sheets("Sheet5").Move After:=.Sheets(5)
.Sheets("Sheet3").Select
.Columns("A:A").Select
.Selection.Copy
.Sheets("Sheet5").Select
.Range("A1").Select
.ActiveSheet.Paste
.Sheets("Sheet2").Select
.Range("C2").Select
Do
If .ActiveCell.Value = "" Then Exit Do
Name = .ActiveCell.Value
.Sheets("Sheet3").Select
.Range("B1").Select
Do
If .ActiveCell.Value = Name Then
.ActiveCell.EntireColumn.Select
.Selection.Copy
.Sheets("Sheet5").Select
.Range("B1").Select
Do
If .ActiveCell.Value = "" Then
.ActiveSheet.Paste
Exit Do
Else
.ActiveCell.Offset(0, 1).Select
End If
Loop
Exit Do
Else
.ActiveCell.Offset(0, 1).Select
End If
Loop
.Sheets("Sheet2").Select
.ActiveCell.Offset(1, 0).Select
Loop
.Sheets("Sheet3").Select
.Rows(intRow).Select
.Selection.Delete Shift:=-4162
.Application.DisplayAlerts = False
.Sheets("Sheet4").Delete
.Application.DisplayAlerts = True
.Sheets("Sheet5").Select
.Range("B1").Select
c = 0
Do Until .ActiveCell.Value = ""
c = c + 1
.ActiveCell.Value = c
.ActiveCell.Offset(0, 1).Select
Loop
intCol2 = .ActiveCell.Offset(0, -1).Column
.Range("B1").Select
If RowDel = True Then
Set myRange = .Worksheets("Sheet5").Range(.Cells(1,
1), .Cells(intRow - 2, c + 1))
Else
Set myRange = .Worksheets("Sheet5").Range(.Cells(1,
1), .Cells(intRow - 1, c + 1))
End If
.Charts.Add
.ActiveChart.ChartType = 52
.ActiveChart.SetSourceData Source:=myRange, PlotBy:=1
.ActiveChart.Location WHERE:=1
With .ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Individual Parts Inspected
by Product Line - " & Forms!frmCriteria!txtStartDate.Value & " to " &
Forms!frmCriteria!txtEndDate.Value
.Axes(1, 1).HasTitle = True
.Axes(1, 1).AxisTitle.Characters.Text = "Employee"
.Axes(2, 1).HasTitle = True
.Axes(2, 1).AxisTitle.Characters.Text = "# of Parts"
End With
.ActiveChart.HasDataTable = False
.Sheets("Chart1").Select
.ActiveChart.Legend.Select
.ActiveChart.Legend.LegendEntries
(.ActiveChart.SeriesCollection.Count).Select
.Selection.Delete
.ActiveChart.Legend.Select
For x = 1 To .ActiveChart.Legend.LegendEntries.Count
.ActiveChart.Legend.LegendEntries(x).LegendKey.Select
With .Selection.Border
.Weight = 2
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
.Selection.Interior.Pattern = 1
Next x
.Charts("Chart1").Select
.ActiveChart.ChartArea.Select
.Sheets("Sheet5").Select
.Range("A2").Select
x = 1
Do
Select Case .ActiveCell.Value
Case "0EPS"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 1
.Pattern = 1
End With
Case "0LID"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 39
.Pattern = 1
End With
Case "0OPS"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 19
.Pattern = 1
End With
Case "DELI"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 35
.Pattern = 1
End With
Case "PEPS"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 8
.Pattern = 1
End With
.Selection.Interior.ColorIndex = 8
Case "0PPC"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 7
.Pattern = 1
End With
Case "CCUP"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 41
.Pattern = 1
End With
Case "0PET"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 4
.Pattern = 1
End With
Case "IDIN"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 27
.Pattern = 1
End With
Case "EXTF"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 3
.Pattern = 1
End With
Case "HAVI"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 43
.Pattern = 1
End With
Case "FILM"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 53
.Pattern = 1
End With
Case "Gloss"
.Charts("Chart1").Select
.ActiveChart.Legend.LegendEntries
(x).LegendKey.Select
With .Selection.Border
.Weight = 1
.LineStyle = -4142
End With
.Selection.Shadow = False
.Selection.InvertIfNegative = False
With .Selection.Interior
.ColorIndex = 10
.Pattern = 1
End With
End Select
x = x + 1
.Sheets("Sheet5").Select
.ActiveCell.Offset(1, 0).Select
Loop Until .ActiveCell.Value = ""
'Add Data Totals & Percent to the Stacked Bar Chart
.Charts("Chart1").Select
.ActiveChart.ChartArea.Select
.ActiveChart.SeriesCollection.NewSeries
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).Values = _
"=Sheet5!R" & intRow &
"C2: R" & intRow & "C" & c + 1
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).Name = "=""Totals"""
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).Select
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).ChartType = 4
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).Select
With .Selection.Border
.Weight = 2
.LineStyle = -4142
End With
With .Selection
.MarkerBackgroundColorIndex = -4142
.MarkerForegroundColorIndex = -4142
.MarkerStyle = -4142
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).ApplyDataLabels _
AutoText:=True, LegendKey:=False, ShowSeriesName:=False,
ShowCategoryName:=False, _
ShowValue:=True, ShowPercentage:=False,
ShowBubbleSize:=False
.ActiveChart.SeriesCollection
(.ActiveChart.SeriesCollection.Count).DataLabels.Select
With .Selection
.HorizontalAlignment = -4108
.VerticalAlignment = -4108
.ReadingOrder = -5002
.Position = 0
.Orientation = -4128
End With
.ActiveChart.PlotArea.Select
With .Selection.Border
.ColorIndex = 2
.Weight = 2
.LineStyle = 1
End With
.ActiveChart.PlotArea.Fill.OneColorGradient
msoGradientHorizontal, 1, 0.36
With .Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 2
End With
.ActiveChart.Legend.Select
y = .ActiveChart.SeriesCollection.Count - 1
.ActiveChart.Legend.LegendEntries(y).Select
.Selection.Delete
.ActiveChart.Axes(2).MajorGridlines.Select
.Selection.Delete
'Creating the background and average line
If IsNull(Forms!frmCriteria!cboPlant.Value) And IsNull(Forms!
frmCriteria!cboPerson.Value) _
And IsNull(Forms!frmCriteria!cboProdLine.Value) And strTL
= "" Then
.ActiveChart.PlotArea.Select
With .ActiveChart
.HasAxis(1, 1) = True
.HasAxis(1, 2) = True
.HasAxis(2, 1) = True
.HasAxis(2, 2) = True
End With
.ActiveChart.Axes(1, 1).Select
.ActiveChart.Axes(1, 1).CategoryType = -4105
.ActiveChart.Axes(1, 2).Select 'Here is where the code
blows up.
.ActiveChart.Axes(1, 1).CategoryType = -4105
.Sheets("Sheet5").Select
.Range("B" & intRow + 1).Select
.Sheets("Sheet2").Select
.Range("A1").Select
Do
If .ActiveCell.Value = "" Then
intRow3 = .ActiveCell.Offset(-1, 0).Row
Exit Do
End If
.ActiveCell.Offset(1, 0).Select
Loop
.Sheets("Sheet5").Select
.ActiveCell.FormulaR1C1 = "=R[-1]C/Sheet2!R" & intRow3 &
"C1"
.ActiveCell.Select
.Selection.AutoFill Destination:=.ActiveCell.Range("A1:" &
GetColumnLetter(intCol2 - 1) & "1"), Type:=0
.ActiveCell.Range("A1:" & GetColumnLetter(intCol2 - 1) &
"1").Select
.Selection.Style = "Percent"
.Selection.NumberFormat = "0.00%"
.Sheets("Chart1").Select
.ActiveChart.Axes(1, 2).Select
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).XValues = "=Sheet5!R" &
intRow + 1 & "C2:R" & intRow + 1 & "C" & intCol
.Sheets("Sheet5").Select
.ActiveCell.Offset(1, 0).Select
.ActiveCell.FormulaR1C1 = "=INT(Sheet2!R" & intRow3 &
"C1/17)"
intRow = .ActiveCell.Row
.Selection.AutoFill Destination:=.ActiveCell.Range
("A1:Q1"), Type:=0
y = 0
.Sheets("Chart1").Select
.ActiveChart.PlotArea.Select
.ActiveChart.SeriesCollection.NewSeries
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).Values = "=Sheet5!R" &
intRow & "C2:R" & intRow & "C18"
.ActiveChart.SeriesCollection(y).Name = "=""Average"""
.ActiveChart.SeriesCollection(y).Select
.ActiveChart.SeriesCollection(y).AxisGroup = 2
.ActiveChart.SeriesCollection(y).ChartType = 4
.ActiveChart.SeriesCollection(y).Select
With .Selection.Border
.ColorIndex = 56
.Weight = 2
.LineStyle = 1
End With
With .Selection
.MarkerBackgroundColorIndex = -4142
.MarkerForegroundColorIndex = -4142
.MarkerStyle = -4142
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
y = 0
.Charts("Chart1").Select
.ActiveChart.Legend.Select
y = .ActiveChart.Legend.LegendEntries.Count
.ActiveChart.Legend.LegendEntries(y).Select
.Selection.Delete
ElseIf IsNull(Forms!frmCriteria!cboPlant.Value) And IsNull
(Forms!frmCriteria!cboPerson.Value) _
And IsNull(Forms!frmCriteria!cboProdLine.Value) And strTL
<> "" Then
.ActiveChart.PlotArea.Select
With .ActiveChart
.HasAxis(1, 1) = True
.HasAxis(1, 2) = True
.HasAxis(2, 1) = True
.HasAxis(2, 2) = False
End With
.ActiveChart.Axes(1, 1).CategoryType = -4105
.ActiveChart.Axes(1, 2).CategoryType = -4105
.Sheets("Sheet5").Select
.Range("B" & intRow + 1).Select
.Sheets("Sheet2").Select
.Range("A2").Select
Do Until .ActiveCell.Value = ""
.ActiveCell.Offset(1, 0).Select
Loop
intRow2 = .ActiveCell.Offset(-1, 0).Row
.Sheets("Sheet5").Select
.ActiveCell.FormulaR1C1 = "=R[-1]C/Sheet2!R" & intRow2 &
"C1"
.Range("B1").Select
Do Until .ActiveCell.Value = ""
.ActiveCell.Offset(0, 1).Select
Loop
b = .ActiveCell.Offset(0, -1).Value
Select Case b
Case 1
strLetter = "A"
Case 2
strLetter = "B"
Case 3
strLetter = "C"
Case 4
strLetter = "D"
Case 5
strLetter = "E"
Case 6
strLetter = "F"
Case 7
strLetter = "G"
Case 8
strLetter = "H"
Case 9
strLetter = "I"
Case 10
strLetter = "J"
Case 11
strLetter = "K"
Case 12
strLetter = "L"
Case 13
strLetter = "M"
Case 14
strLetter = "N"
Case 15
strLetter = "O"
Case 16
strLetter = "P"
Case 17
strLetter = "Q"
Case 18
strLetter = "R"
Case 19
strLetter = "S"
Case 20
strLetter = "T"
Case 21
strLetter = "U"
Case 22
strLetter = "V"
Case 23
strLetter = "W"
Case 24
strLetter = "X"
Case 25
strLetter = "Y"
Case 26
strLetter = "Z"
End Select
.Range("B" & intRow + 1).Select
.Selection.AutoFill Destination:=.ActiveCell.Range("A1:" &
strLetter & "1"), Type:= _
0
.ActiveCell.Range("A1:" & strLetter & "1").Select
.Selection.Style = "Percent"
.Selection.NumberFormat = "0.00%"
.Sheets("Chart1").Select
.ActiveChart.Axes(1, 2).Select
.ActiveChart.PlotArea.Select
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).XValues = "=Sheet5!R" &
intRow + 1 & "C2:R" & intRow + 1 & "C" & intCol
.Sheets("Sheet5").Select
.ActiveCell.Offset(1, 0).Select
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Int(Sum
(tmpReports.NumOfSets)/21) AS AverageSets" _
& " FROM tmpReports WHERE tmpReports.StartedDate
Between #" & _
[Forms]![frmCriteria]![txtStartDate] & "# And #" &
[Forms]![frmCriteria]![txtEndDate] & "#;")
.ActiveCell.FormulaR1C1 = "=INT(" & rst!AverageSets & ")"
intRow = .ActiveCell.Row
.Selection.AutoFill Destination:=.ActiveCell.Range("A1:" &
strLetter & "1"), Type:=0
y = 0
.Sheets("Chart1").Select
.ActiveChart.PlotArea.Select
.ActiveChart.SeriesCollection.NewSeries
y = .ActiveChart.SeriesCollection.Count
.ActiveChart.SeriesCollection(y).Values = "=Sheet5!R" &
intRow & "C2:R" & intRow & "C" & b + 1
.ActiveChart.SeriesCollection(y).Name = "=""Average"""
.ActiveChart.SeriesCollection(y).Select
.ActiveChart.SeriesCollection(y).AxisGroup = 2
.ActiveChart.SeriesCollection(y).ChartType = 4
.ActiveChart.SeriesCollection(y).Select
With .Selection.Border
.ColorIndex = 56
.Weight = 2
.LineStyle = 1
End With
With .Selection
.MarkerBackgroundColorIndex = -4142
.MarkerForegroundColorIndex = -4142
.MarkerStyle = -4142
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
y = 0
.Charts("Chart1").Select
.ActiveChart.Legend.Select
y = .ActiveChart.Legend.LegendEntries.Count
.ActiveChart.Legend.LegendEntries(y).Select
.Selection.Delete
End If
End With
msg = MsgBox("Do you wish to run the Weight Factor Report?",
vbYesNo, _
"Run Weight Factor Report?")
xl.Sheets("Sheet1").Name = "Data"
xl.Sheets("Sheet2").Name = "Rank"
xl.Sheets("Chart1").Name = "Ind. Parts Chart"
xl.Sheets("Sheet5").Name = "Ind. Parts Table"
If msg = vbYes Then
Call WeightFactor
'Add Weight Factor Values to the report
xl.Workbooks.Open FileName:="J:\QA\Productivity Reports
\WeightFactorValues.xls"
xl.Sheets("Sheet1").Select
xl.Sheets("Sheet1").Copy After:=xl.Workbooks
("IndividualProductReport1").Sheets(9)
xl.Windows("WeightFactorValues.xls").Activate
xl.ActiveWindow.Close
'Delete sheets that are no longer needed
xl.Sheets("Sheet6").Delete
xl.Sheets("Sheet3").Delete
'Rename the sheets
xl.Sheets("Sheet7").Name = "Weight Factor Rank"
xl.Sheets("Sheet8").Name = "Weight Factor Table"
xl.Sheets("Chart2").Name = "Weight Factor Chart"
xl.Sheets("Sheet1").Name = "Weight Factor Conv. Sheet"
End If
DoEvents
xl.Sheets("Data").Select
EndGetData:
msg = MsgBox("Do you wish to save the file at this time?",
vbYesNo, "Save File")
If msg = vbYes Then
xl.ActiveWorkbook.SaveAs "J:\QA\Productivity Reports\" & _
InputBox("Please type a filename...", "Save As") & ".xls"
MsgBox "File saved as " & xl.ActiveWorkbook.FullName
End If
GetData_Err_Exit:
xl.Application.Visible = True
xl.Application.Interactive = True
xl.Application.DisplayAlerts = True
xl.Application.ScreenUpdating = True
DoCmd.Close acForm, "frmProgressBar", acSaveNo
Set rst = Nothing
db.Close
Set db = Nothing
Exit Sub
GetData_Err:
If Err = 2100 Then
Err.Clear
Resume Next
ElseIf Err = 91 Then
DoCmd.Close acForm, "frmProgressBar", acSaveNo
Set rst = Nothing
Set db = Nothing
Exit Sub
Else
MsgBox Err.Description, , Err.Number
Err.Clear
Resume GetData_Err_Exit
End If
End Sub