Hello,
I am writing a program that generates Product reports in excel by pulling data from Access.
There are 5 different companies, and within each company there are more than 2 products. I would like for each company report to be created in the same excel file. So in Company 1 file there will be 3 worksheets each worksheet is for a different product. In Company 2 there will be 5 worksheets each worksheet for a different product, etc.
So far this is what I have:
Sub A_Startup()
Dim rs As Recordset 'this holds our spinner that we created
Dim Active As String
Dim Ans As String ' the Report Month as entered by the user
Dim Ans2 As Integer ' the Hotspot percentile value entered by the user as a number between 1 and 100
Dim Ans3 As Integer ' the Report Year as entered by the user
Dim i As Integer
Dim strDir As String
Dim strLoc As String
Dim RowCount As Long ' to count records so that you can run batches of 50 or 100 etc..
Const RunThisZoneName As String = ""
Application.ScreenUpdating = False 'this allows the screen not to be updated and speeds up the action of the code, you can see your code as it is working
Application.DisplayAlerts = False 'turn displays/warning off
Set wbMaster = ActiveWorkbook 'set the active workbook as the master product advisor workbook
Ans = InputBox("What is the current month? Please type full month name, no abbreviations.", "Report Date SetUp - Enter Month", "December")
wbMaster.Worksheets("Product Report").Cells(1, 19).Value = Ans ' places the report month in the cell R4
Ans3 = InputBox("What is the current year? Please type full year, no abbreviations.", "Report Date SetUp - Enter Year", "2008")
wbMaster.Worksheets("Product Report").Cells(1, 20).Value = Ans3 ' places the report year in the cell R5
Ans2 = InputBox("What is the HOTSPOT percentile for this report?", "HOTSPOT Percentile", "95")
wbMaster.Worksheets("Product Report").Cells(3, 22).Value = Ans2 ' places the HOTSPOT VALUE in the cell S3
If ActiveWorkbook.Path = "C:\PROJECTS\2008\Product Tracking" Then
strLoc = "C:\PROJECTS\2008\Product Tracking\"
ElseIf ActiveWorkbook.Path = "C:\2008\Product Tracking" Then
strLoc = "C:\2008\Product Tracking\"
Else
strLoc = "C:\2008\08_Projects\08_Product Tracking\Development\Product Report\"
End If
Set db = DBEngine(0).OpenDatabase(Name:=strLoc & "Product Sales Matrix.mdb") 'opens access database
Set rs = db.OpenRecordset("SELECT * FROM Spinner_Query WHERE Active = 'True'" & IIf(RunThisZoneName = "", "", " AND [Zone Name] = '" & RunThisZoneName & "'")) 'opens the query in access that pulls the data
With rs
.MoveFirst 'move to the first record
'.Move 2 ' to start at a specific record - based on the spinner_query order
'change the value in the ROWCOUNT IF-THEN statement at end of Startup to set number of records to run from this point forward
RowCount = 0
Do Until .EOF 'run through the record one at a time until it reaches the end
Spinner = .Fields("Product / Sales Name") 'set the spinner to the Advisor name
SpinnerCategory = .Fields("Category Name")
SpinnerCompany = .Fields("Company Name")
SpinnerZone = .Fields("Zone Name")
SpinnerCode = .Fields("Product / Sales Code")
SpinnerCompanyCode = .Fields("Company Code")
SpinnerCount = .Fields("Internal Count")
If Spinner <> "UNKNOWN" Then ' tells it to continue only if the Spinner does not read as UNKNOWN
Set wbReport = Workbooks.Add
wbReport.Colors = wbMaster.Colors ' copies the colors
Call Template_Intro(Spinner) 'place Company name into the intro excel cell
Call Template_Advisor(Spinner) 'call routine from ModB_Template
For i = 1 To Application.SheetsInNewWorkbook ' deletes the first default sheets at the front of the workbook
wbReport.Worksheets("Sheet" & i).Delete
Next i
' ===== this section saves as Excel in the correct directory which is created ==========
' ========== by the CreateDir sub - Be sure to use the naming convention shown and =======
' ========== change the month sub sirectory to reflect current month ====================
strDir = strLoc & "December Reports\LOPN_E\Excel files\'& SpinnerDealer & "
Call CreateDir(strDir) ' this Function is located in ModF_Functions and is a standard function to create directories when they don't already exist
wbReport.SaveAs strDir & "LOPN_" & Ans & Ans3 & "_" & "E" & "_" & SpinnerCompanyCode & "_" & Spinner & "_" & SpinnerCount & ".xls"
Code for Template_Advisor(Spinner) routine
Sub Template_Advisor(Spinner As String)
Dim rs As Recordset
Dim strSQL As String
Dim i As Integer
Application.Calculation = xlCalculationManual
wbMaster.Worksheets("Product Report").Copy After:=wbReport.Worksheets(wbReport.Worksheets.Count) 'copies the pages
'strSQL is the statement that pulls the data into the worksheet.
' This top section adds Company Name, Company Code, Month, Zone Name and Category Code to the worksheet
strSQL = "Select [Company Name],[Company Code],[Month],[Zone Name],[Category Code],[Product / Sales Code] " & _
"FROM [Product Sales Matrix] " & _
"WHERE [Product / Sales Name] = '" & Spinner & "'" & _
"AND [Product / Sales Code] = '" & SpinnerCode & "'" & _
" AND TIME = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(3, 16).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'==========================================================================
'========== DATA FOR ADVISOR : RATING QUESTIONS ===========================
'==========================================================================
'ADVISOR: 1 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Product / Sales Name] = '" & Spinner & "'" & _
"AND [Product / Sales Code] = '" & SpinnerCode & "'" & _
" AND TIME = '1 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(9, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'ADVISOR : 3 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii, " & _
"Q13a, Q13c, Q13b, Q13f, Q13e, Q13g, Q13d, Q15a, Q15c " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Product / Sales Name] = '" & Spinner & "'" & _
"AND [Product / Sales Code] = '" & SpinnerCode & "'" & _
" AND TIME = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(10, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
' The following ReplacePie subroutine will delete the default pie chart and arrow Iff the value for Q15A is zero.
' The pie chart is replaced with a text box indicating that there is No Data To Report.
' The text box was created on the Excel template outside the printable area and is merely moved into position by the macro
Calculate
If Range("AW10").Value = 0 Then ReplacePie
'========================================================================================================
'============================== DATA FOR COMPANY : RATING QUESTIONS ======================================
'========================================================================================================
'COMPANY : 1 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Company Name] = '" & SpinnerCompany & "'" & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [Zone Name] = '" & SpinnerZone & "'" & _
" AND [Product / Sales Name] Is Null " & _
" AND TIME = '1 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(11, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'COMPANY : 3 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii, " & _
"Q13a, Q13c, Q13b, Q13f, Q13e, Q13g, Q13d, Q15a, Q15c " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Company Name] = '" & SpinnerCompany & "'" & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [Zone Name] = '" & SpinnerZone & "'" & _
" AND [Product / Sales Name] Is Null " & _
" AND TIME = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(12, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'========================================================================================================
'============================== DATA FOR CATEGORY : RATING QUESTIONS ===============================
'========================================================================================================
'different from Toyota in that this is the Category Value only (CAT 1 or 2) there is no further ZONE breakdown in Lexus db
'CATEGORY : 1 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Company Name] Is Null " & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [Zone Name] Is Null " & _
" AND [Product / Sales Name] Is Null " & _
" AND TIME = '1 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(13, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'CATEGORY : 3 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii, " & _
"Q13a, Q13c, Q13b, Q13f, Q13e, Q13g, Q13d, Q15a, Q15c " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Company Name] Is Null " & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [Zone Name] Is Null " & _
" AND [Product / Sales Name] Is Null " & _
" AND TIME = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(14, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'==============================================================================
'=========== RANKING : Pulling in Advisor Index data by Cat ==============
'=========== to calculate individual rankings =================================
'==============================================================================
strSQL = "Select [Product / Sales Name],[LPE - PRODUCT ADVISOR],[Product / Sales Code] " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Product / Sales Name] is not null " & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [TIME] = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
i = 149
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(i, 72).CopyFromRecordset rs
End If
End With
Call CloseObject(rs)
'=====================NEW FOR SEPTEMBER 2008 ========================================
'=========== DELIVERY RANKING : Pulling in DELIVERY Index data by Cat ===========
'=========== to calculate individual rankings =======================================
'====================================================================================
strSQL = "Select [Product / Sales Name],[LPE - DELIVERY PROCESS],[Product / Sales Code] " & _
"FROM [Prodcut Sales Matrix] " & _
" WHERE [Product / Sales Name] is not null " & _
" AND [Category Name] = '" & SpinnerCategory & "' " & _
" AND [TIME] = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
i = 149
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(i, 84).CopyFromRecordset rs
End If
End With
I am writing a program that generates Product reports in excel by pulling data from Access.
There are 5 different companies, and within each company there are more than 2 products. I would like for each company report to be created in the same excel file. So in Company 1 file there will be 3 worksheets each worksheet is for a different product. In Company 2 there will be 5 worksheets each worksheet for a different product, etc.
So far this is what I have:
Sub A_Startup()
Dim rs As Recordset 'this holds our spinner that we created
Dim Active As String
Dim Ans As String ' the Report Month as entered by the user
Dim Ans2 As Integer ' the Hotspot percentile value entered by the user as a number between 1 and 100
Dim Ans3 As Integer ' the Report Year as entered by the user
Dim i As Integer
Dim strDir As String
Dim strLoc As String
Dim RowCount As Long ' to count records so that you can run batches of 50 or 100 etc..
Const RunThisZoneName As String = ""
Application.ScreenUpdating = False 'this allows the screen not to be updated and speeds up the action of the code, you can see your code as it is working
Application.DisplayAlerts = False 'turn displays/warning off
Set wbMaster = ActiveWorkbook 'set the active workbook as the master product advisor workbook
Ans = InputBox("What is the current month? Please type full month name, no abbreviations.", "Report Date SetUp - Enter Month", "December")
wbMaster.Worksheets("Product Report").Cells(1, 19).Value = Ans ' places the report month in the cell R4
Ans3 = InputBox("What is the current year? Please type full year, no abbreviations.", "Report Date SetUp - Enter Year", "2008")
wbMaster.Worksheets("Product Report").Cells(1, 20).Value = Ans3 ' places the report year in the cell R5
Ans2 = InputBox("What is the HOTSPOT percentile for this report?", "HOTSPOT Percentile", "95")
wbMaster.Worksheets("Product Report").Cells(3, 22).Value = Ans2 ' places the HOTSPOT VALUE in the cell S3
If ActiveWorkbook.Path = "C:\PROJECTS\2008\Product Tracking" Then
strLoc = "C:\PROJECTS\2008\Product Tracking\"
ElseIf ActiveWorkbook.Path = "C:\2008\Product Tracking" Then
strLoc = "C:\2008\Product Tracking\"
Else
strLoc = "C:\2008\08_Projects\08_Product Tracking\Development\Product Report\"
End If
Set db = DBEngine(0).OpenDatabase(Name:=strLoc & "Product Sales Matrix.mdb") 'opens access database
Set rs = db.OpenRecordset("SELECT * FROM Spinner_Query WHERE Active = 'True'" & IIf(RunThisZoneName = "", "", " AND [Zone Name] = '" & RunThisZoneName & "'")) 'opens the query in access that pulls the data
With rs
.MoveFirst 'move to the first record
'.Move 2 ' to start at a specific record - based on the spinner_query order
'change the value in the ROWCOUNT IF-THEN statement at end of Startup to set number of records to run from this point forward
RowCount = 0
Do Until .EOF 'run through the record one at a time until it reaches the end
Spinner = .Fields("Product / Sales Name") 'set the spinner to the Advisor name
SpinnerCategory = .Fields("Category Name")
SpinnerCompany = .Fields("Company Name")
SpinnerZone = .Fields("Zone Name")
SpinnerCode = .Fields("Product / Sales Code")
SpinnerCompanyCode = .Fields("Company Code")
SpinnerCount = .Fields("Internal Count")
If Spinner <> "UNKNOWN" Then ' tells it to continue only if the Spinner does not read as UNKNOWN
Set wbReport = Workbooks.Add
wbReport.Colors = wbMaster.Colors ' copies the colors
Call Template_Intro(Spinner) 'place Company name into the intro excel cell
Call Template_Advisor(Spinner) 'call routine from ModB_Template
For i = 1 To Application.SheetsInNewWorkbook ' deletes the first default sheets at the front of the workbook
wbReport.Worksheets("Sheet" & i).Delete
Next i
' ===== this section saves as Excel in the correct directory which is created ==========
' ========== by the CreateDir sub - Be sure to use the naming convention shown and =======
' ========== change the month sub sirectory to reflect current month ====================
strDir = strLoc & "December Reports\LOPN_E\Excel files\'& SpinnerDealer & "
Call CreateDir(strDir) ' this Function is located in ModF_Functions and is a standard function to create directories when they don't already exist
wbReport.SaveAs strDir & "LOPN_" & Ans & Ans3 & "_" & "E" & "_" & SpinnerCompanyCode & "_" & Spinner & "_" & SpinnerCount & ".xls"
Code for Template_Advisor(Spinner) routine
Sub Template_Advisor(Spinner As String)
Dim rs As Recordset
Dim strSQL As String
Dim i As Integer
Application.Calculation = xlCalculationManual
wbMaster.Worksheets("Product Report").Copy After:=wbReport.Worksheets(wbReport.Worksheets.Count) 'copies the pages
'strSQL is the statement that pulls the data into the worksheet.
' This top section adds Company Name, Company Code, Month, Zone Name and Category Code to the worksheet
strSQL = "Select [Company Name],[Company Code],[Month],[Zone Name],[Category Code],[Product / Sales Code] " & _
"FROM [Product Sales Matrix] " & _
"WHERE [Product / Sales Name] = '" & Spinner & "'" & _
"AND [Product / Sales Code] = '" & SpinnerCode & "'" & _
" AND TIME = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(3, 16).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'==========================================================================
'========== DATA FOR ADVISOR : RATING QUESTIONS ===========================
'==========================================================================
'ADVISOR: 1 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Product / Sales Name] = '" & Spinner & "'" & _
"AND [Product / Sales Code] = '" & SpinnerCode & "'" & _
" AND TIME = '1 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(9, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'ADVISOR : 3 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii, " & _
"Q13a, Q13c, Q13b, Q13f, Q13e, Q13g, Q13d, Q15a, Q15c " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Product / Sales Name] = '" & Spinner & "'" & _
"AND [Product / Sales Code] = '" & SpinnerCode & "'" & _
" AND TIME = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(10, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
' The following ReplacePie subroutine will delete the default pie chart and arrow Iff the value for Q15A is zero.
' The pie chart is replaced with a text box indicating that there is No Data To Report.
' The text box was created on the Excel template outside the printable area and is merely moved into position by the macro
Calculate
If Range("AW10").Value = 0 Then ReplacePie
'========================================================================================================
'============================== DATA FOR COMPANY : RATING QUESTIONS ======================================
'========================================================================================================
'COMPANY : 1 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Company Name] = '" & SpinnerCompany & "'" & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [Zone Name] = '" & SpinnerZone & "'" & _
" AND [Product / Sales Name] Is Null " & _
" AND TIME = '1 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(11, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'COMPANY : 3 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii, " & _
"Q13a, Q13c, Q13b, Q13f, Q13e, Q13g, Q13d, Q15a, Q15c " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Company Name] = '" & SpinnerCompany & "'" & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [Zone Name] = '" & SpinnerZone & "'" & _
" AND [Product / Sales Name] Is Null " & _
" AND TIME = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(12, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'========================================================================================================
'============================== DATA FOR CATEGORY : RATING QUESTIONS ===============================
'========================================================================================================
'different from Toyota in that this is the Category Value only (CAT 1 or 2) there is no further ZONE breakdown in Lexus db
'CATEGORY : 1 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Company Name] Is Null " & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [Zone Name] Is Null " & _
" AND [Product / Sales Name] Is Null " & _
" AND TIME = '1 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(13, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'CATEGORY : 3 Month Average
strSQL = "Select [Number of Returns (sample size)],[LPE - PRODUCT ADVISOR],[LPE - DELIVERY PROCESS], " & _
"Q8i, Q8j, Q8k, Q8l, Q8m, Q8n, Q8o, Q8p, Q8q, Q8r, Q8s, Q8t, Q8u, Q8y, Q8pp, Q8rr, Q8dd, Q8ee, Q8ff, Q8gg, Q8hh, Q8ii, " & _
"Q13a, Q13c, Q13b, Q13f, Q13e, Q13g, Q13d, Q15a, Q15c " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Company Name] Is Null " & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [Zone Name] Is Null " & _
" AND [Product / Sales Name] Is Null " & _
" AND TIME = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(14, 17).CopyFromRecordset rs, .RecordCount
End If
End With
Call CloseObject(rs)
'==============================================================================
'=========== RANKING : Pulling in Advisor Index data by Cat ==============
'=========== to calculate individual rankings =================================
'==============================================================================
strSQL = "Select [Product / Sales Name],[LPE - PRODUCT ADVISOR],[Product / Sales Code] " & _
"FROM [Prodcut Sales Matrix] " & _
"WHERE [Product / Sales Name] is not null " & _
" AND [Category Name] = '" & SpinnerCategory & "'" & _
" AND [TIME] = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
i = 149
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(i, 72).CopyFromRecordset rs
End If
End With
Call CloseObject(rs)
'=====================NEW FOR SEPTEMBER 2008 ========================================
'=========== DELIVERY RANKING : Pulling in DELIVERY Index data by Cat ===========
'=========== to calculate individual rankings =======================================
'====================================================================================
strSQL = "Select [Product / Sales Name],[LPE - DELIVERY PROCESS],[Product / Sales Code] " & _
"FROM [Prodcut Sales Matrix] " & _
" WHERE [Product / Sales Name] is not null " & _
" AND [Category Name] = '" & SpinnerCategory & "' " & _
" AND [TIME] = '3 Month Average' "
Set rs = db.OpenRecordset(strSQL)
i = 149
With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(i, 84).CopyFromRecordset rs
End If
End With
