相机客观参数整理软件需求报告自动化统计

魅族和TCL手机的相机项目，需要通过几十项的客观参数验证，每一类参数最终都会获得一个excel表格文件，每个表格文件又包含若干工作薄，要在几十个表格文件的几十个工作薄中统计计算某些参数的值并把结果写入新的表格文件中，手动操作是一件十分枯燥且易出错的，其实这部分工作可以使用宏来解决。下面是为驱动部门开发的excel宏文件，略作记录，以备后用。

Sub 自动统计()Dim myPath$Dim AK As WorkbookmyPath = ThisWorkbook.Path '把文件路径定义给变量Application.ScreenUpdating = False '冻结屏幕， 以防屏幕抖动Set AK = Workbooks.Open(myPath & "\1 (6)_summary.csv")Workbooks("D65_summary.csv").Sheets("A_summary").Range("B137:B137").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D3:D3")Workbooks("D65_summary.csv").Sheets("A_summary").Range("B138").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D10")Workbooks("D65_summary.csv").Sheets("A_summary").Range("B144").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D11")Workbooks("D65_summary.csv").Sheets("A_summary").Range("B136:E136").Copy Workbooks("report.xlsm").Sheets("RGBY Noise（明亮）").Range("D3:G3")Workbooks("D65_summary.csv").Sheets("A_summary").Range("J7:J10").Copy Workbooks("report.xlsm").Sheets("AWB").Range("D3:D6")If Range("C3:C5").MergeCells ThenWorkbooks("D65_summary.csv").Sheets("A_summary").Range("B50:B50").Copy Workbooks("report.xlsm").Sheets("SNR").Range("C3:C5")ElseWorkbooks("report.xlsm").Sheets("SNR").Range("C3:C5").MergeCells = TrueWorkbooks("D65_summary.csv").Sheets("A_summary").Range("B50:B50").Copy Workbooks("report.xlsm").Sheets("SNR").Range("C3:C5")End IfWorkbooks("report.xlsm").Sheets("SNR").Range("C6:C8").MergeCells = TrueWorkbooks("D65_summary.csv").Sheets("A_summary").Range("C50:C50").Copy Workbooks("report.xlsm").Sheets("SNR").Range("C6:C8")Workbooks("report.xlsm").Sheets("SNR").Range("C9:C11").MergeCells = TrueWorkbooks("D65_summary.csv").Sheets("A_summary").Range("D50:D50").Copy Workbooks("report.xlsm").Sheets("SNR").Range("C9:C11")Workbooks("report.xlsm").Sheets("SNR").Range("C12:C14").MergeCells = TrueWorkbooks("D65_summary.csv").Sheets("A_summary").Range("E50:E50").Copy Workbooks("report.xlsm").Sheets("SNR").Range("C12:C14")AK.Close FalseSet AK = Workbooks.Open(myPath & "\D50_summary.csv")Workbooks("D50_summary.csv").Sheets("A_summary").Range("B137:B137").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D4:D4")Workbooks("D50_summary.csv").Sheets("A_summary").Range("B138").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D16")Workbooks("D50_summary.csv").Sheets("A_summary").Range("B144").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D17")Workbooks("D50_summary.csv").Sheets("A_summary").Range("B136:E136").Copy Workbooks("report.xlsm").Sheets("RGBY Noise（明亮）").Range("D4:G4")Workbooks("D50_summary.csv").Sheets("A_summary").Range("J7:J10").Copy Workbooks("report.xlsm").Sheets("AWB").Range("D7:D10")AK.Close FalseSet AK = Workbooks.Open(myPath & "\CWF_summary.csv")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("B137").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D5")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("B138").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D12")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("B144").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D13")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("B136:E136").Copy Workbooks("report.xlsm").Sheets("RGBY Noise（明亮）").Range("D6:G6")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("J7:J10").Copy Workbooks("report.xlsm").Sheets("AWB").Range("D15:D18")AK.Close FalseSet AK = Workbooks.Open(myPath & "\HOR_summary.csv")Workbooks("HOR_summary.csv").Sheets("A_summary").Range("B137").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D6")Workbooks("HOR_summary.csv").Sheets("A_summary").Range("B138").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D18")Workbooks("HOR_summary.csv").Sheets("A_summary").Range("B144").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D19")Workbooks("HOR_summary.csv").Sheets("A_summary").Range("B136:E136").Copy Workbooks("report.xlsm").Sheets("RGBY Noise（明亮）").Range("D7:G7")Workbooks("HOR_summary.csv").Sheets("A_summary").Range("J7:J10").Copy Workbooks("report.xlsm").Sheets("AWB").Range("D19:D22")AK.Close FalseSet AK = Workbooks.Open(myPath & "\TL84_summary.csv")Workbooks("TL84_summary.csv").Sheets("A_summary").Range("B137").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D7")Workbooks("TL84_summary.csv").Sheets("A_summary").Range("B138").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D20")Workbooks("TL84_summary.csv").Sheets("A_summary").Range("B144").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D21")Workbooks("TL84_summary.csv").Sheets("A_summary").Range("B136:E136").Copy Workbooks("report.xlsm").Sheets("RGBY Noise（明亮）").Range("D8:G8")Workbooks("TL84_summary.csv").Sheets("A_summary").Range("J7:J10").Copy Workbooks("report.xlsm").Sheets("AWB").Range("D23:D26")AK.Close FalseSet AK = Workbooks.Open(myPath & "\D75_summary.csv")Workbooks("D75_summary.csv").Sheets("A_summary").Range("B137").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D8")Workbooks("D75_summary.csv").Sheets("A_summary").Range("B138").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D14")Workbooks("D75_summary.csv").Sheets("A_summary").Range("B144").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D15")Workbooks("D75_summary.csv").Sheets("A_summary").Range("B136:E136").Copy Workbooks("report.xlsm").Sheets("RGBY Noise（明亮）").Range("D5:G5")Workbooks("D75_summary.csv").Sheets("A_summary").Range("J7:J10").Copy Workbooks("report.xlsm").Sheets("AWB").Range("D11:D14")AK.Close FalseSet AK = Workbooks.Open(myPath & "\A_summary.csv")Workbooks("A_summary.csv").Sheets("A_summary").Range("B137").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D9")Workbooks("A_summary.csv").Sheets("A_summary").Range("B138").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D22")Workbooks("A_summary.csv").Sheets("A_summary").Range("B144").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D23")Workbooks("A_summary.csv").Sheets("A_summary").Range("B136:E136").Copy Workbooks("report.xlsm").Sheets("RGBY Noise（明亮）").Range("D9:G9")Workbooks("A_summary.csv").Sheets("A_summary").Range("J7:J10").Copy Workbooks("report.xlsm").Sheets("AWB").Range("D27:D30")AK.Close FalseSet AK = Workbooks.Open(myPath & "\step_summary.csv")Dim i As Integer, count As Integercount = 0For i = 9 To 27    If (Abs(Workbooks("step_summary.csv").Sheets("step_summary").Cells(i + 1, 2).Value - Workbooks("step_summary.csv").Sheets("step_summary").Cells(i, 2).Value) > 8) Then    count = count + 1    End IfNextWorkbooks("report.xlsm").Sheets("动态范围（Gamma）").Cells(3, 3).Value = countAK.Close FalseApplication.ScreenUpdating = True '冻结屏幕,此类语句一般成对使用End SubSub test()Set AK = Workbooks.Open(myPath & "\CWF_summary.csv")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("B137").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D5")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("B138").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D12")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("B144").Copy Workbooks("report.xlsm").Sheets("饱和度&色差").Range("D13")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("B136:E136").Copy Workbooks("report.xlsm").Sheets("RGBY Noise（明亮）").Range("D6:G6")Workbooks("CWF_summary.csv").Sheets("A_summary").Range("J7:J10").Copy Workbooks("report.xlsm").Sheets("AWB").Range("D15:D18")AK.Close FalseEnd Sub