VB对EXCEL文件的处理

最新推荐文章于 2022-10-31 13:51:24 发布

原创最新推荐文章于 2022-10-31 13:51:24 发布 · 782 阅读

0 ·

CC 4.0 BY-SA版权

本文分享了一位非IT背景的财务工作者如何使用VBA自动化处理财务凭证统计工作，包括月度和分时工作量统计，涉及Excel和Word文档操作、文件夹选择对话框、动态数组处理等技术。

身在FSSC，工作内容单调，就是SAP做财务凭证。因为部门有工作量考核，于是想着能够通过编程解决繁琐的工作量的统计，自己不是it专业出身，只有大学选修的vb和基本忘干净的谭浩强课本的c。前后折腾一个月，最后写出来如下的东西。

Dim xlA As New excel.Application '声明对象变量
Dim xlAbo As New excel.Workbook
Dim xlAsh As New excel.Worksheet
Dim xlB As New excel.Application
Dim xlBbo As New excel.Workbook
Dim xlBsh As New excel.Worksheet
Dim woA As New Word.Application
Public YWB As String 'YWB表示业务部
Public Path As String
'Option Explicit'强制声明变量
Private Type BrowseInfo
    hWndOwner As Long
    pIDLRoot As Long
    pszDisplayName As Long
    lpszTitle As Long
    ulFlags As Long
    lpfnCallback As Long
    lParam As Long
    iImage As Long
End Type
Const BIF_RETURNONLYFSDIRS = 1
Const BIF_NEWDIALOGSTYLE = &H40
Const BIF_EDITBOX = &H10
Const BIF_USENEWUI = BIF_NEWDIALOGSTYLE Or BIF_EDITBOX
Const MAX_PATH = 260
Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long


Public Function BrowseForFolder(Optional sTitle As String = "请选择文件夹") As String
    Dim iNull As Integer, lpIDList As Long, lResult As Long
    Dim sPath As String, udtBI As BrowseInfo
    With udtBI
        .hWndOwner = 0 ' Me.hWnd
        .lpszTitle = lstrcat(sTitle, "")
        .ulFlags = BIF_RETURNONLYFSDIRS Or BIF_USENEWUI
    End With
    lpIDList = SHBrowseForFolder(udtBI)
    If lpIDList Then
        sPath = String$(MAX_PATH, 0)
        SHGetPathFromIDList lpIDList, sPath
        CoTaskMemFree lpIDList
        iNull = InStr(sPath, vbNullChar)
        If iNull Then
          sPath = Left$(sPath, iNull - 1)
        End If
    End If
    BrowseForFolder = sPath
End Function

Private Sub Check1_Click() '业务部选择三联
Check2.Value = 0
Check3.Value = 0
YWB = "总账"
End Sub
Private Sub Check2_Click()
Check1.Value = 0
Check3.Value = 0
YWB = "成本"
End Sub
Private Sub Check3_Click()
Check1.Value = 0
Check2.Value = 0
YWB = "资金"
End Sub

Private Sub Command1_Click() '打开文件夹选择目录
Path = BrowseForFolder
Text1.Text = Path
End Sub

Private Sub Command3_Click() '月度统计代码
ScreenUpdating = False
Label6.Caption = "准备..."
Label4.Caption = "读取文件中..."
xlA.Workbooks.Open (CurDir + "\CoreDate\" + YWB + "月度.dat")
xlA.ActiveWorkbook.SaveAs (CurDir + "\" + YWB + "月度工作量统计.XLSX")
CreateBackup = True
xlA.Quit '关闭EXCELL
Set xlA = Nothing '释放资源
Set xlAbo = xlA.Workbooks.Open(CurDir + "\" + YWB + "月度工作量统计.XLSX")  'A为最终生成文件
Set xlAsh = xlAbo.Worksheets(1) '写入循环读取文件切入点，A文件不需要循环打开，B文件需要循环打开
Dim MyFile, Mypath
Mypath = Path + "\"
MyFile = Dir(Mypath & "*.XLSX")
Dim Wjs As Long
Wjs = 1
Do While MyFile <> ""  '该循环为遍历文件夹内所有excel文件用
    Label6.Caption = "正在读取第" & Wjs & "个Excel文件"
    Label5.Caption = ""
    Set xlBbo = xlB.Workbooks.Open(Mypath & MyFile)    'B为待处理文件，注：此处为非指定excel文件
    Set xlBsh = xlBbo.Worksheets(1) '定义 sheet
    xlA.ScreenUpdating = False
    xlB.ScreenUpdating = False
    Ew = xlBsh.Range("A1:P500000") '读取15*500000的sheet内容，并录入二维字符串组EW
    Dim i, j, k, Gsdm1, Pzbh1, Pzttwb1, Yhm1, Srrq1, Srsj1, TCode1, Zdrq1 As Long
    '这一步赋值16给默认取数维度列数，在于防止默认为0导致的下标越界，同时下方循环语句写入了只读前15列，确保16列为空
    Gsdm1 = 16: Pzbh = 16: Pzttwb1 = 16: Yhm1 = 16: Srrq1 = 16: Srsj1 = 16: TCode1 = 16: Zdrq1 = 16
    j = 1
    Label4.Caption = "数据归类中..."
    
        Do While Ew(1, j) <> "" Or j <> 16 '该循环用于判定sap导出的顺序杂乱的excel的每一列的属性
            If Ew(2, j) <> "" Then
                If Ew(1, j) = "公司代码" Then Gsdm1 = j
                If Ew(1, j) = "凭证编号" Then Pzbh1 = j
                If Ew(1, j) = "凭证抬头文本" Or Ew(1, j) = "摘    要" Then Pzttwb1 = j '在这里引入“摘    要”，用于AIC凭证的统计
                If Ew(1, j) = "用户名" Or Ew(1, j) = "用户名称" Then Yhm1 = j
                If Ew(1, j) = "输入日期" Then Srrq1 = j
                If Ew(1, j) = "输入时间" Then Srsj1 = j
                If Ew(1, j) = "制单日期" Then Zdrq1 = j '在这里引入一个维度，制单日期，用于AIC凭证的统计
                If Ew(1, j) = "TCode" Then TCode1 = j
            End If
        j = j + 1
        Loop
    '*********数据已经录入EW二维数组中，并已经确定对应列数，无需执行该抽取数据步骤，已删除，相关代码在3.3以前版本可见*************
    Label4.Caption = "数据分析中..."
    i = 2
    k = 4
    Dim M(400) As Long
    Dim Ygid As String
    Do While xlAsh.Cells(k, 4).Value <> "" '本循环用于逐行读取模板中的用户名
        Ygid = xlAsh.Cells(k, 4).Value 'ygid即为员工id的意思
        Do While Ew(i, Yhm1) <> ""  '本循环用于确定用户名后逐行读取待处理文件数据
            If Ew(i, Zdrq1) <> "" Then  '本判断用于判断SAP还是AIC系统导出,前者是AIC
                If Ew(i, Yhm1) = Ygid Then
                    M(7) = M(7) + 1
                    If Left(Ew(i, Pzttwb1), 2) = "冲销" Then M(11) = M(11) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/1 " Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/1 " Then M(15) = M(15) + 1 '以下为每日工作量取数
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/2 " Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/2 " Then M(16) = M(16) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/3 " Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/3 " Then M(17) = M(17) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/4 " Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/4 " Then M(18) = M(18) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/5 " Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/5 " Then M(19) = M(19) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/6 " Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/6 " Then M(20) = M(20) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/7 " Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/7 " Then M(21) = M(21) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/8 " Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/8 " Then M(22) = M(22) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/9 " Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/9 " Then M(23) = M(23) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/10" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/10" Then M(24) = M(24) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/11" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/11" Then M(25) = M(25) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/12" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/12" Then M(26) = M(26) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/13" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/13" Then M(27) = M(27) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/14" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/14" Then M(28) = M(28) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/15" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/15" Then M(29) = M(29) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/16" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/16" Then M(30) = M(30) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/17" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/17" Then M(31) = M(31) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/18" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/18" Then M(32) = M(32) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/19" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/19" Then M(33) = M(33) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/20" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/20" Then M(34) = M(34) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/21" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/21" Then M(35) = M(35) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/22" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/22" Then M(36) = M(36) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/23" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/23" Then M(37) = M(37) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/24" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/24" Then M(38) = M(38) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/25" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/25" Then M(39) = M(39) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/26" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/26" Then M(40) = M(40) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/27" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/27" Then M(41) = M(41) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/28" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/28" Then M(42) = M(42) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/29" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/29" Then M(43) = M(43) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/30" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/30" Then M(44) = M(44) + 1
                    If Mid(CDate(Ew(i, Zdrq1)), 7, 3) = "/31" Or Mid(CDate(Ew(i, Zdrq1)), 8, 3) = "/31" Then M(45) = M(45) + 1
                    If Left(Ew(i, Gsdm1), 3) = "900" Then M(74) = M(74) + 1 '以下为公司代码取数
                    If Left(Ew(i, Gsdm1), 3) = "638" Then M(141) = M(141) + 1
                    If Left(Ew(i, Gsdm1), 3) = "923" Then M(148) = M(148) + 1
                    If Left(Ew(i, Gsdm1), 3) = "248" Or Left(Ew(i, Gsdm1), 3) = "250" Or Left(Ew(i, Gsdm1), 3) = "251" Then M(160) = M(160) + 1
                    If Left(Ew(i, Gsdm1), 3) = "223" Or Left(Ew(i, Gsdm1), 3) = "224" Or Left(Ew(i, Gsdm1), 3) = "225" Then M(199) = M(202) + 1
                    If Left(Ew(i, Gsdm1), 3) = "650" Then M(214) = M(214) + 1
                    If Left(Ew(i, Gsdm1), 3) = "619" Then M(221) = M(221) + 1
                    If Left(Ew(i, Gsdm1), 3) = "961" Then M(228) = M(228) + 1
                    If Left(Ew(i, Gsdm1), 3) = "548" Then M(235) = M(235) + 1
                    If Left(Ew(i, Gsdm1), 3) = "111" Then M(242) = M(242) + 1
                    If Left(Ew(i, Gsdm1), 3) = "570" Then M(249) = M(249) + 1
                    If Left(Ew(i, Gsdm1), 3) = "644" Then M(256) = M(256) + 1
                    If Left(Ew(i, Gsdm1), 3) = "591" Then M(263) = M(263) + 1
                    If Left(Ew(i, Gsdm1), 3) = "661" Then M(270) = M(270) + 1
                    If Left(Ew(i, Gsdm1), 3) = "671" Then M(277) = M(277) + 1
                    If Left(Ew(i, Gsdm1), 3) = "601" Then M(284) = M(284) + 1
                    If Left(Ew(i, Gsdm1), 3) = "705" Then M(291) = M(291) + 1
                    If Left(Ew(i, Gsdm1), 3) = "689" Then M(298) = M(298) + 1
                    If Left(Ew(i, Gsdm1), 3) = "631" Then M(312) = M(312) + 1
                    If Left(Ew(i, Gsdm1), 3) = "675" Then M(319) = M(319) + 1
                    If Left(Ew(i, Gsdm1), 3) = "807" Then M(326) = M(326) + 1
                    If Left(Ew(i, Gsdm1), 3) = "626" Then M(333) = M(333) + 1
                    
                End If
            Else
                If Ew(i, Yhm1) = Ygid And HandTcode(Ew(i, TCode1)) And HandPzttwb(Ew(i, Pzttwb1)) And HandPzbh(Ew(i, Pzbh1)) Then
                    M(6) = M(6) + 1
                    If YWB = "总账" Then
                        If Ew(i, TCode1) = "ZFIEHQ40021_COPY" Or Ew(i, TCode1) = "ZFIEHQ40021" Or Ew(i, TCode1) = "ZTRRHQ00111" Then M(10) = M(10) + 1
                    End If
                    
                    If YWB = "资金" Then
                        If Left(Ew(i, Pzbh1), 2) = "23" Then M(10) = M(10) + 1
                        If Left(Ew(i, Pzbh1), 2) = "10" Then M(8) = M(8) + 1
                        If Left(Ew(i, Pzbh1), 2) = "15" Then M(9) = M(9) + 1
                    End If
                    
                    If Ew(i, TCode1) = "FB08" Or Ew(i, TCode1) = "FBRA" Or Ew(i, TCode1) = "MR8M" Then M(11) = M(11) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "/1" Then M(15) = M(15) + 1 '以下为每日工作量取数
                    If Right(CDate(Ew(i, Srrq1)), 2) = "/2" Then M(16) = M(16) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "/3" Then M(17) = M(17) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "/4" Then M(18) = M(18) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "/5" Then M(19) = M(19) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "/6" Then M(20) = M(20) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "/7" Then M(21) = M(21) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "/8" Then M(22) = M(22) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "/9" Then M(23) = M(23) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "10" Then M(24) = M(24) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "11" Then M(25) = M(25) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "12" Then M(26) = M(26) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "13" Then M(27) = M(27) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "14" Then M(28) = M(28) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "15" Then M(29) = M(29) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "16" Then M(30) = M(30) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "17" Then M(31) = M(31) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "18" Then M(32) = M(32) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "19" Then M(33) = M(33) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "20" Then M(34) = M(34) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "21" Then M(35) = M(35) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "22" Then M(36) = M(36) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "23" Then M(37) = M(37) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "24" Then M(38) = M(38) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "25" Then M(39) = M(39) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "26" Then M(40) = M(40) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "27" Then M(41) = M(41) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "28" Then M(42) = M(42) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "29" Then M(43) = M(43) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "30" Then M(44) = M(44) + 1
                    If Right(CDate(Ew(i, Srrq1)), 2) = "31" Then M(45) = M(45) + 1
                    If Left(Ew(i, Gsdm1), 3) = "188" Then M(46) = M(46) + 1 '以下为公司代码取数
                    If Left(Ew(i, Gsdm1), 3) = "178" Then M(51) = M(51) + 1
                    If Left(Ew(i, Gsdm1), 3) = "389" Then M(56) = M(56) + 1
                    If Left(Ew(i, Gsdm1), 3) = "233" Then M(61) = M(61) + 1
                    If Left(Ew(i, Gsdm1), 3) = "168" Then M(66) = M(66) + 1
                    If Left(Ew(i, Gsdm1), 3) = "900" Then M(71) = M(71) + 1
                    If Left(Ew(i, Gsdm1), 3) = "216" Then M(78) = M(78) + 1
                    If Left(Ew(i, Gsdm1), 3) = "183" Then M(83) = M(83) + 1
                    If Left(Ew(i, Gsdm1), 3) = "120" Or Left(Ew(i, Gsdm1), 3) = "114" Then M(88) = M(88) + 1
                    If Left(Ew(i, Gsdm1), 3) = "416" Then M(93) = M(93) + 1
                    If Left(Ew(i, Gsdm1), 3) = "253" Then M(98) = M(98) + 1
                    If Left(Ew(i, Gsdm1), 3) = "404" Then M(103) = M(103) + 1
                    If Left(Ew(i, Gsdm1), 3) = "415" Or Left(Ew(i, Gsdm1), 3) = "435" Then M(108) = M(108) + 1
                    If Left(Ew(i, Gsdm1), 3) = "171" Then M(113) = M(113) + 1
                    If Left(Ew(i, Gsdm1), 3) = "197" Then M(118) = M(118) + 1
                    If Left(Ew(i, Gsdm1), 3) = "200" Then M(123) = M(123) + 1
                    If Left(Ew(i, Gsdm1), 3) = "438" Then M(128) = M(128) + 1
                    If Left(Ew(i, Gsdm1), 3) = "175" Then M(133) = M(133) + 1
                    If Left(Ew(i, Gsdm1), 3) = "638" Then M(138) = M(138) + 1
                    If Left(Ew(i, Gsdm1), 3) = "923" Then M(145) = M(145) + 1
                    If Left(Ew(i, Gsdm1), 3) = "X94" Then M(152) = M(152) + 1
                    If Left(Ew(i, Gsdm1), 3) = "248" Or Left(Ew(i, Gsdm1), 3) = "250" Or Left(Ew(i, Gsdm1), 3) = "251" Then M(157) = M(157) + 1
                    If Left(Ew(i, Gsdm1), 3) = "208" Then M(164) = M(164) + 1
                    If Left(Ew(i, Gsdm1), 3) = "X04" Then M(169) = M(169) + 1
                    If Left(Ew(i, Gsdm1), 3) = "243" Then M(174) = M(174) + 1
                    If Left(Ew(i, Gsdm1), 3) = "262" Then M(179) = M(179) + 1
                    If Left(Ew(i, Gsdm1), 3) = "X60" Then M(184) = M(184) + 1
                    If Left(Ew(i, Gsdm1), 3) = "430" Then M(189) = M(189) + 1
                    If Left(Ew(i, Gsdm1), 3) = "180" Then M(194) = M(194) + 1
                    If Left(Ew(i, Gsdm1), 3) = "223" Or Left(Ew(i, Gsdm1), 3) = "224" Or Left(Ew(i, Gsdm1), 3) = "225" Then M(199) = M(199) + 1
                    If Left(Ew(i, Gsdm1), 3) = "440" Then M(206) = M(206) + 1
                    If Left(Ew(i, Gsdm1), 3) = "650" Then M(211) = M(211) + 1
                    If Left(Ew(i, Gsdm1), 3) = "619" Then M(218) = M(218) + 1
                    If Left(Ew(i, Gsdm1), 3) = "961" Then M(225) = M(225) + 1
                    If Left(Ew(i, Gsdm1), 3) = "548" Then M(232) = M(232) + 1
                    If Left(Ew(i, Gsdm1), 3) = "111" Then M(239) = M(239) + 1
                    If Left(Ew(i, Gsdm1), 3) = "570" Then M(246) = M(246) + 1
                    If Left(Ew(i, Gsdm1), 3) = "644" Then M(253) = M(253) + 1
                    If Left(Ew(i, Gsdm1), 3) = "591" Then M(260) = M(260) + 1
                    If Left(Ew(i, Gsdm1), 3) = "661" Then M(267) = M(267) + 1
                    If Left(Ew(i, Gsdm1), 3) = "671" Then M(274) = M(274) + 1
                    If Left(Ew(i, Gsdm1), 3) = "601" Then M(281) = M(281) + 1
                    If Left(Ew(i, Gsdm1), 3) = "705" Then M(288) = M(288) + 1
                    If Left(Ew(i, Gsdm1), 3) = "689" Then M(295) = M(295) + 1
                    If Left(Ew(i, Gsdm1), 3) = "614" Then M(302) = M(302) + 1
                    If Left(Ew(i, Gsdm1), 3) = "631" Then M(309) = M(309) + 1
                    If Left(Ew(i, Gsdm1), 3) = "675" Then M(316) = M(316) + 1
                    
                Else
                    If Ew(i, Yhm1) = Ygid Then
                        M(12) = M(12) + 1 '集成凭证筛选段
                        If Left(Ew(i, Gsdm1), 3) = "188" Then M(47) = M(47) + 1 '以下为公司代码取数
                        If Left(Ew(i, Gsdm1), 3) = "178" Then M(52) = M(52) + 1
                        If Left(Ew(i, Gsdm1), 3) = "389" Then M(57) = M(57) + 1
                        If Left(Ew(i, Gsdm1), 3) = "233" Then M(62) = M(62) + 1
                        If Left(Ew(i, Gsdm1), 3) = "168" Then M(67) = M(67) + 1
                        If Left(Ew(i, Gsdm1), 3) = "900" Then M(72) = M(72) + 1
                        If Left(Ew(i, Gsdm1), 3) = "216" Then M(79) = M(79) + 1
                        If Left(Ew(i, Gsdm1), 3) = "183" Then M(84) = M(84) + 1
                        If Left(Ew(i, Gsdm1), 3) = "120" Or Left(Ew(i, Gsdm1), 3) = "114" Then M(89) = M(89) + 1
                        If Left(Ew(i, Gsdm1), 3) = "416" Then M(94) = M(94) + 1
                        If Left(Ew(i, Gsdm1), 3) = "253" Then M(99) = M(99) + 1
                        If Left(Ew(i, Gsdm1), 3) = "404" Then M(104) = M(104) + 1
                        If Left(Ew(i, Gsdm1), 3) = "415" Or Left(Ew(i, Gsdm1), 3) = "435" Then M(109) = M(109) + 1
                        If Left(Ew(i, Gsdm1), 3) = "171" Then M(114) = M(114) + 1
                        If Left(Ew(i, Gsdm1), 3) = "197" Then M(119) = M(119) + 1
                        If Left(Ew(i, Gsdm1), 3) = "200" Then M(124) = M(124) + 1
                        If Left(Ew(i, Gsdm1), 3) = "438" Then M(129) = M(129) + 1
                        If Left(Ew(i, Gsdm1), 3) = "175" Then M(134) = M(134) + 1
                        If Left(Ew(i, Gsdm1), 3) = "638" Then M(139) = M(139) + 1
                        If Left(Ew(i, Gsdm1), 3) = "923" Then M(146) = M(146) + 1
                        If Left(Ew(i, Gsdm1), 3) = "X94" Then M(153) = M(153) + 1
                        If Left(Ew(i, Gsdm1), 3) = "248" Or Left(Ew(i, Gsdm1), 3) = "250" Or Left(Ew(i, Gsdm1), 3) = "251" Then M(158) = M(158) + 1
                        If Left(Ew(i, Gsdm1), 3) = "208" Then M(165) = M(165) + 1
                        If Left(Ew(i, Gsdm1), 3) = "X04" Then M(170) = M(170) + 1
                        If Left(Ew(i, Gsdm1), 3) = "243" Then M(175) = M(175) + 1
                        If Left(Ew(i, Gsdm1), 3) = "262" Then M(180) = M(180) + 1
                        If Left(Ew(i, Gsdm1), 3) = "X60" Then M(185) = M(185) + 1
                        If Left(Ew(i, Gsdm1), 3) = "430" Then M(190) = M(190) + 1
                        If Left(Ew(i, Gsdm1), 3) = "180" Then M(195) = M(195) + 1
                        If Left(Ew(i, Gsdm1), 3) = "223" Or Left(Ew(i, Gsdm1), 3) = "224" Or Left(Ew(i, Gsdm1), 3) = "225" Then M(200) = M(200) + 1
                        If Left(Ew(i, Gsdm1), 3) = "440" Then M(207) = M(207) + 1
                        If Left(Ew(i, Gsdm1), 3) = "650" Then M(212) = M(212) + 1
                        If Left(Ew(i, Gsdm1), 3) = "619" Then M(219) = M(219) + 1
                        If Left(Ew(i, Gsdm1), 3) = "961" Then M(226) = M(226) + 1
                        If Left(Ew(i, Gsdm1), 3) = "548" Then M(233) = M(233) + 1
                        If Left(Ew(i, Gsdm1), 3) = "111" Then M(240) = M(240) + 1
                        If Left(Ew(i, Gsdm1), 3) = "570" Then M(247) = M(247) + 1
                        If Left(Ew(i, Gsdm1), 3) = "644" Then M(254) = M(254) + 1
                        If Left(Ew(i, Gsdm1), 3) = "591" Then M(261) = M(261) + 1
                        If Left(Ew(i, Gsdm1), 3) = "661" Then M(268) = M(268) + 1
                        If Left(Ew(i, Gsdm1), 3) = "671" Then M(275) = M(275) + 1
                        If Left(Ew(i, Gsdm1), 3) = "601" Then M(282) = M(282) + 1
                        If Left(Ew(i, Gsdm1), 3) = "705" Then M(289) = M(289) + 1
                        If Left(Ew(i, Gsdm1), 3) = "689" Then M(296) = M(296) + 1
                        If Left(Ew(i, Gsdm1), 3) = "614" Then M(303) = M(303) + 1
                        If Left(Ew(i, Gsdm1), 3) = "631" Then M(310) = M(310) + 1
                        If Left(Ew(i, Gsdm1), 3) = "675" Then M(317) = M(317) + 1
                    End If
                End If
            End If
            i = i + 1
        Loop
            
        Dim Zi As Integer '为了使代码简洁，在此处采用循环完成赋值
        For Zi = 1 To 340
            If M(Zi) <> 0 Then
            xlAsh.Cells(k, Zi).Value = xlAsh.Cells(k, Zi).Value + M(Zi)
            M(Zi) = 0
            End If
        Next Zi
        i = 2
        k = k + 1
        Label5.Caption = "正在写入第" & k - 4 & "位员工数据..."
    Loop
    
    Erase Ew
    xlAbo.Save
    xlBbo.Save
    MyFile = Dir '抽取该文件夹内第二个excel
    Wjs = Wjs + 1
Loop '为文件循环结束

Label6.Caption = "累计完成读取" & Wjs - 1 & "个Excel文件"
Label4.Caption = "工作量统计完成！"
Label5.Caption = "员工数据已写入完成！"

xlAsh.Range("B4:ME200").Sort xlAsh.Range("E4"), 2
xlAbo.Save
xlAsh.Range("B4:ME200").Sort xlAsh.Range("B4"), 1
xlAbo.Save    '这四行语句用来给导出的工作量按组按工作量排序，缺点是公式固化，删改员工数量后会发生统计错误，后续再实现排序跟随员工信息维护变动而变动

xlA.Quit '关闭EXCELL
Set xlA = Nothing '释放资源
xlB.Quit '关闭EXCELL
Set xlB = Nothing '释放资源
xlA.Workbooks.Open (CurDir + "\" + YWB + "月度工作量统计.XLSX") '打开EXCEL模板，Curdir为当前目录
xlA.Visible = True '使Excel隐藏不可见 false
ScreenUpdating = True
End Sub

Private Sub Command4_Click() '分时统计代码
ScreenUpdating = False
Label6.Caption = "准备..."
Label4.Caption = "读取文件中..."
xlA.Workbooks.Open (CurDir + "\CoreDate\" + YWB + "分时.dat")
xlA.ActiveWorkbook.SaveAs (CurDir + "\" + YWB + "分时工作量统计.XLSX")
CreateBackup = True
xlA.Quit '关闭EXCELL
Set xlA = Nothing '释放资源
Set xlAbo = xlA.Workbooks.Open(CurDir + "\" + YWB + "分时工作量统计.XLSX") 'A为最终生成文件
Set xlAsh = xlAbo.Worksheets(1)
'写入循环读取文件切入点，A文件不需要循环打开，B文件需要循环打开
Dim MyFile, Mypath, MyName
Mypath = Path + "\"
MyFile = Dir(Mypath & "*.XLSX")
Dim Wjs As Long
Wjs = 1
Do While MyFile <> ""  '该循环为遍历文件夹内所有excel文件用
    Label6.Caption = "正在读取第" & Wjs & "个Excel文件"
    Label5.Caption = ""
    Set xlBbo = xlB.Workbooks.Open(Mypath & MyFile)    'B为待处理文件，注：此处需要改写为非指定excel文件
    Set xlBsh = xlBbo.Worksheets(1)
    xlA.ScreenUpdating = False
    xlB.ScreenUpdating = False
    Ew = xlBsh.Range("A1:P200000") '读取15*200000的sheet内容，并录入二维字符串组EW
    Dim i, j, k, Gsdm1, Pzbh1, Pzttwb1, Yhm1, Srrq1, Srsj1, TCode1, Zdrq1 As Long
    Gsdm1 = 16: Pzbh = 16: Pzttwb1 = 16: Yhm1 = 16: Srrq1 = 16: Srsj1 = 16: TCode1 = 16: Zdrq1 = 16
    j = 1
    Label4.Caption = "数据归类中..."
    
        Do While Ew(1, j) <> "" Or j <> 16 '该循环用于判定sap导出的顺序杂乱的excel的每一列的属性
            If Ew(2, j) <> "" Then
                If Ew(1, j) = "公司代码" Then Gsdm1 = j
                If Ew(1, j) = "凭证编号" Then Pzbh1 = j '暂时用不上改维度，优化程序速度，故屏蔽
                If Ew(1, j) = "凭证抬头文本" Or Ew(1, j) = "摘    要" Then Pzttwb1 = j '在这里引入“摘    要”，用于AIC凭证的统计
                If Ew(1, j) = "用户名" Or Ew(1, j) = "用户名称" Then Yhm1 = j
                If Ew(1, j) = "输入日期" Then Srrq1 = j
                If Ew(1, j) = "输入时间" Then Srsj1 = j
                If Ew(1, j) = "制单日期" Then Zdrq1 = j '在这里引入一个维度，制单日期，用于AIC凭证的统计
                If Ew(1, j) = "TCode" Then TCode1 = j
            End If
        j = j + 1
        Loop
    
    Label4.Caption = "数据分析中..."
    i = 2
    k = 4
    Dim M(400) As Long
    Dim Ygid As String
    Do While xlA.ActiveWorkbook.Sheets(1).Cells(k, 4).Value <> "" '本循环用于逐行读取模板中的用户名
    Ygid = xlA.ActiveWorkbook.Sheets(1).Cells(k, 4).Value
        Do While Ew(i, Yhm1) <> ""    '本循环用于确定用户名后逐行读取待处理文件数据
            If Ew(i, Zdrq1) <> "" Then '本判断用于判断SAP还是AIC系统导出
                If Ew(i, Yhm1) = Ygid Then
                    M(7) = M(7) + 1
                    If Left(Ew(i, Pzttwb1), 2) = "冲销" Then M(11) = M(11) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 0:") > 0 Then M(15) = M(15) + 1  '以下为每小时工作量取数，此处注意AIC中时间格式读入内存再读出，格式发生变化，“08：”变成“8：”
                    If InStr(CDate(Ew(i, Zdrq1)), " 1:") > 0 Then M(16) = M(16) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 2:") > 0 Then M(17) = M(17) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 3:") > 0 Then M(18) = M(18) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 4:") > 0 Then M(19) = M(19) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 5:") > 0 Then M(20) = M(20) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 6:") > 0 Then M(21) = M(21) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 7:") > 0 Then M(22) = M(22) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 8:") > 0 Then M(23) = M(23) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 9:") > 0 Then M(24) = M(24) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 10:") > 0 Then M(25) = M(25) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 11:") > 0 Then M(26) = M(26) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 12:") > 0 Then M(27) = M(27) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 13:") > 0 Then M(28) = M(28) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 14:") > 0 Then M(29) = M(29) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 15:") > 0 Then M(30) = M(30) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 16:") > 0 Then M(31) = M(31) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 17:") > 0 Then M(32) = M(32) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 18:") > 0 Then M(33) = M(33) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 19:") > 0 Then M(34) = M(34) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 20:") > 0 Then M(35) = M(35) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 21:") > 0 Then M(36) = M(36) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 22:") > 0 Then M(37) = M(37) + 1
                    If InStr(CDate(Ew(i, Zdrq1)), " 23:") > 0 Then M(38) = M(38) + 1
                    If Left(Ew(i, Gsdm1), 3) = "900" Then M(74) = M(74) + 1 '以下为公司代码取数
                    If Left(Ew(i, Gsdm1), 3) = "638" Then M(141) = M(141) + 1
                    If Left(Ew(i, Gsdm1), 3) = "923" Then M(148) = M(148) + 1
                    If Left(Ew(i, Gsdm1), 3) = "248" Or Left(Ew(i, Gsdm1), 3) = "250" Or Left(Ew(i, Gsdm1), 3) = "251" Then M(160) = M(160) + 1
                    If Left(Ew(i, Gsdm1), 3) = "223" Or Left(Ew(i, Gsdm1), 3) = "224" Or Left(Ew(i, Gsdm1), 3) = "225" Then M(199) = M(202) + 1
                    If Left(Ew(i, Gsdm1), 3) = "650" Then M(214) = M(214) + 1
                    If Left(Ew(i, Gsdm1), 3) = "619" Then M(221) = M(221) + 1
                    If Left(Ew(i, Gsdm1), 3) = "961" Then M(228) = M(228) + 1
                    If Left(Ew(i, Gsdm1), 3) = "548" Then M(235) = M(235) + 1
                    If Left(Ew(i, Gsdm1), 3) = "111" Then M(242) = M(242) + 1
                    If Left(Ew(i, Gsdm1), 3) = "570" Then M(249) = M(249) + 1
                    If Left(Ew(i, Gsdm1), 3) = "644" Then M(256) = M(256) + 1
                    If Left(Ew(i, Gsdm1), 3) = "591" Then M(263) = M(263) + 1
                    If Left(Ew(i, Gsdm1), 3) = "661" Then M(270) = M(270) + 1
                    If Left(Ew(i, Gsdm1), 3) = "671" Then M(277) = M(277) + 1
                    If Left(Ew(i, Gsdm1), 3) = "601" Then M(284) = M(284) + 1
                    If Left(Ew(i, Gsdm1), 3) = "705" Then M(291) = M(291) + 1
                    If Left(Ew(i, Gsdm1), 3) = "689" Then M(298) = M(298) + 1
                    If Left(Ew(i, Gsdm1), 3) = "631" Then M(312) = M(312) + 1
                    If Left(Ew(i, Gsdm1), 3) = "675" Then M(319) = M(319) + 1
                    If Left(Ew(i, Gsdm1), 3) = "807" Then M(326) = M(326) + 1
                    If Left(Ew(i, Gsdm1), 3) = "626" Then M(333) = M(333) + 1
                End If
            Else
                 If Ew(i, Yhm1) = Ygid And HandTcode(Ew(i, TCode1)) And HandPzttwb(Ew(i, Pzttwb1)) And HandPzbh(Ew(i, Pzbh1)) Then
                    M(6) = M(6) + 1
                    If YWB = "总账" Then
                        If Ew(i, TCode1) = "ZFIEHQ40021_COPY" Or Ew(i, TCode1) = "ZFIEHQ40021" Or Ew(i, TCode1) = "ZTRRHQ00111" Then M(10) = M(10) + 1
                    End If
                    
                    If YWB = "资金" Then
                        If Left(Ew(i, Pzbh1), 2) = "23" Then M(10) = M(10) + 1
                        If Left(Ew(i, Pzbh1), 2) = "10" Then M(8) = M(8) + 1
                        If Left(Ew(i, Pzbh1), 2) = "15" Then M(9) = M(9) + 1
                    End If
                    
                    If Ew(i, TCode1) = "FB08" Or Ew(i, TCode1) = "FBRA" Or Ew(i, TCode1) = "MR8M" Then M(11) = M(11) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "0:" Then M(15) = M(15) + 1 '以下为每小时工作量取数
                    If Left(CDate(Ew(i, Srsj1)), 2) = "1:" Then M(16) = M(16) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "2:" Then M(17) = M(17) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "3:" Then M(18) = M(18) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "4:" Then M(19) = M(19) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "5:" Then M(20) = M(20) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "6:" Then M(21) = M(21) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "7:" Then M(22) = M(22) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "8:" Then M(23) = M(23) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "9:" Then M(24) = M(24) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "10" Then M(25) = M(25) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "11" Then M(26) = M(26) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "12" Then M(27) = M(27) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "13" Then M(28) = M(28) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "14" Then M(29) = M(29) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "15" Then M(30) = M(30) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "16" Then M(31) = M(31) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "17" Then M(32) = M(32) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "18" Then M(33) = M(33) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "19" Then M(34) = M(34) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "20" Then M(35) = M(35) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "21" Then M(36) = M(36) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "22" Then M(37) = M(37) + 1
                    If Left(CDate(Ew(i, Srsj1)), 2) = "23" Then M(38) = M(38) + 1
                    If Left(Ew(i, Gsdm1), 3) = "188" Then M(46) = M(46) + 1 '以下为公司代码取数
                    If Left(Ew(i, Gsdm1), 3) = "178" Then M(51) = M(51) + 1
                    If Left(Ew(i, Gsdm1), 3) = "389" Then M(56) = M(56) + 1
                    If Left(Ew(i, Gsdm1), 3) = "233" Then M(61) = M(61) + 1
                    If Left(Ew(i, Gsdm1), 3) = "168" Then M(66) = M(66) + 1
                    If Left(Ew(i, Gsdm1), 3) = "900" Then M(71) = M(71) + 1
                    If Left(Ew(i, Gsdm1), 3) = "216" Then M(78) = M(78) + 1
                    If Left(Ew(i, Gsdm1), 3) = "183" Then M(83) = M(83) + 1
                    If Left(Ew(i, Gsdm1), 3) = "120" Or Left(Ew(i, Gsdm1), 3) = "114" Then M(88) = M(88) + 1
                    If Left(Ew(i, Gsdm1), 3) = "416" Then M(93) = M(93) + 1
                    If Left(Ew(i, Gsdm1), 3) = "253" Then M(98) = M(98) + 1
                    If Left(Ew(i, Gsdm1), 3) = "404" Then M(103) = M(103) + 1
                    If Left(Ew(i, Gsdm1), 3) = "415" Or Left(Ew(i, Gsdm1), 3) = "435" Then M(108) = M(108) + 1
                    If Left(Ew(i, Gsdm1), 3) = "171" Then M(113) = M(113) + 1
                    If Left(Ew(i, Gsdm1), 3) = "197" Then M(118) = M(118) + 1
                    If Left(Ew(i, Gsdm1), 3) = "200" Then M(123) = M(123) + 1
                    If Left(Ew(i, Gsdm1), 3) = "438" Then M(128) = M(128) + 1
                    If Left(Ew(i, Gsdm1), 3) = "175" Then M(133) = M(133) + 1
                    If Left(Ew(i, Gsdm1), 3) = "638" Then M(138) = M(138) + 1
                    If Left(Ew(i, Gsdm1), 3) = "923" Then M(145) = M(145) + 1
                    If Left(Ew(i, Gsdm1), 3) = "X94" Then M(152) = M(152) + 1
                    If Left(Ew(i, Gsdm1), 3) = "248" Or Left(Ew(i, Gsdm1), 3) = "250" Or Left(Ew(i, Gsdm1), 3) = "251" Then M(157) = M(157) + 1
                    If Left(Ew(i, Gsdm1), 3) = "208" Then M(164) = M(164) + 1
                    If Left(Ew(i, Gsdm1), 3) = "X04" Then M(169) = M(169) + 1
                    If Left(Ew(i, Gsdm1), 3) = "243" Then M(174) = M(174) + 1
                    If Left(Ew(i, Gsdm1), 3) = "262" Then M(179) = M(179) + 1
                    If Left(Ew(i, Gsdm1), 3) = "X60" Then M(184) = M(184) + 1
                    If Left(Ew(i, Gsdm1), 3) = "430" Then M(189) = M(189) + 1
                    If Left(Ew(i, Gsdm1), 3) = "180" Then M(194) = M(194) + 1
                    If Left(Ew(i, Gsdm1), 3) = "223" Or Left(Ew(i, Gsdm1), 3) = "224" Or Left(Ew(i, Gsdm1), 3) = "225" Then M(199) = M(199) + 1
                    If Left(Ew(i, Gsdm1), 3) = "440" Then M(206) = M(206) + 1
                    If Left(Ew(i, Gsdm1), 3) = "650" Then M(211) = M(211) + 1
                    If Left(Ew(i, Gsdm1), 3) = "619" Then M(218) = M(218) + 1
                    If Left(Ew(i, Gsdm1), 3) = "961" Then M(225) = M(225) + 1
                    If Left(Ew(i, Gsdm1), 3) = "548" Then M(232) = M(232) + 1
                    If Left(Ew(i, Gsdm1), 3) = "111" Then M(239) = M(239) + 1
                    If Left(Ew(i, Gsdm1), 3) = "570" Then M(246) = M(246) + 1
                    If Left(Ew(i, Gsdm1), 3) = "644" Then M(253) = M(253) + 1
                    If Left(Ew(i, Gsdm1), 3) = "591" Then M(260) = M(260) + 1
                    If Left(Ew(i, Gsdm1), 3) = "661" Then M(267) = M(267) + 1
                    If Left(Ew(i, Gsdm1), 3) = "671" Then M(274) = M(274) + 1
                    If Left(Ew(i, Gsdm1), 3) = "601" Then M(281) = M(281) + 1
                    If Left(Ew(i, Gsdm1), 3) = "705" Then M(288) = M(288) + 1
                    If Left(Ew(i, Gsdm1), 3) = "689" Then M(295) = M(295) + 1
                    If Left(Ew(i, Gsdm1), 3) = "614" Then M(302) = M(302) + 1
                    If Left(Ew(i, Gsdm1), 3) = "631" Then M(309) = M(309) + 1
                    If Left(Ew(i, Gsdm1), 3) = "675" Then M(316) = M(316) + 1
                    
                Else
                    If Ew(i, Yhm1) = Ygid Then
                        M(12) = M(12) + 1 '集成凭证筛选段
                        If Left(Ew(i, Gsdm1), 3) = "188" Then M(47) = M(47) + 1 '以下为公司代码取数
                        If Left(Ew(i, Gsdm1), 3) = "178" Then M(52) = M(52) + 1
                        If Left(Ew(i, Gsdm1), 3) = "389" Then M(57) = M(57) + 1
                        If Left(Ew(i, Gsdm1), 3) = "233" Then M(62) = M(62) + 1
                        If Left(Ew(i, Gsdm1), 3) = "168" Then M(67) = M(67) + 1
                        If Left(Ew(i, Gsdm1), 3) = "900" Then M(72) = M(72) + 1
                        If Left(Ew(i, Gsdm1), 3) = "216" Then M(79) = M(79) + 1
                        If Left(Ew(i, Gsdm1), 3) = "183" Then M(84) = M(84) + 1
                        If Left(Ew(i, Gsdm1), 3) = "120" Or Left(Ew(i, Gsdm1), 3) = "114" Then M(89) = M(89) + 1
                        If Left(Ew(i, Gsdm1), 3) = "416" Then M(94) = M(94) + 1
                        If Left(Ew(i, Gsdm1), 3) = "253" Then M(99) = M(99) + 1
                        If Left(Ew(i, Gsdm1), 3) = "404" Then M(104) = M(104) + 1
                        If Left(Ew(i, Gsdm1), 3) = "415" Or Left(Ew(i, Gsdm1), 3) = "435" Then M(109) = M(109) + 1
                        If Left(Ew(i, Gsdm1), 3) = "171" Then M(114) = M(114) + 1
                        If Left(Ew(i, Gsdm1), 3) = "197" Then M(119) = M(119) + 1
                        If Left(Ew(i, Gsdm1), 3) = "200" Then M(124) = M(124) + 1
                        If Left(Ew(i, Gsdm1), 3) = "438" Then M(129) = M(129) + 1
                        If Left(Ew(i, Gsdm1), 3) = "175" Then M(134) = M(134) + 1
                        If Left(Ew(i, Gsdm1), 3) = "638" Then M(139) = M(139) + 1
                        If Left(Ew(i, Gsdm1), 3) = "923" Then M(146) = M(146) + 1
                        If Left(Ew(i, Gsdm1), 3) = "X94" Then M(153) = M(153) + 1
                        If Left(Ew(i, Gsdm1), 3) = "248" Or Left(Ew(i, Gsdm1), 3) = "250" Or Left(Ew(i, Gsdm1), 3) = "251" Then M(158) = M(158) + 1
                        If Left(Ew(i, Gsdm1), 3) = "208" Then M(165) = M(165) + 1
                        If Left(Ew(i, Gsdm1), 3) = "X04" Then M(170) = M(170) + 1
                        If Left(Ew(i, Gsdm1), 3) = "243" Then M(175) = M(175) + 1
                        If Left(Ew(i, Gsdm1), 3) = "262" Then M(180) = M(180) + 1
                        If Left(Ew(i, Gsdm1), 3) = "X60" Then M(185) = M(185) + 1
                        If Left(Ew(i, Gsdm1), 3) = "430" Then M(190) = M(190) + 1
                        If Left(Ew(i, Gsdm1), 3) = "180" Then M(195) = M(195) + 1
                        If Left(Ew(i, Gsdm1), 3) = "223" Or Left(Ew(i, Gsdm1), 3) = "224" Or Left(Ew(i, Gsdm1), 3) = "225" Then M(200) = M(200) + 1
                        If Left(Ew(i, Gsdm1), 3) = "440" Then M(207) = M(207) + 1
                        If Left(Ew(i, Gsdm1), 3) = "650" Then M(212) = M(212) + 1
                        If Left(Ew(i, Gsdm1), 3) = "619" Then M(219) = M(219) + 1
                        If Left(Ew(i, Gsdm1), 3) = "961" Then M(226) = M(226) + 1
                        If Left(Ew(i, Gsdm1), 3) = "548" Then M(233) = M(233) + 1
                        If Left(Ew(i, Gsdm1), 3) = "111" Then M(240) = M(240) + 1
                        If Left(Ew(i, Gsdm1), 3) = "570" Then M(247) = M(247) + 1
                        If Left(Ew(i, Gsdm1), 3) = "644" Then M(254) = M(254) + 1
                        If Left(Ew(i, Gsdm1), 3) = "591" Then M(261) = M(261) + 1
                        If Left(Ew(i, Gsdm1), 3) = "661" Then M(268) = M(268) + 1
                        If Left(Ew(i, Gsdm1), 3) = "671" Then M(275) = M(275) + 1
                        If Left(Ew(i, Gsdm1), 3) = "601" Then M(282) = M(282) + 1
                        If Left(Ew(i, Gsdm1), 3) = "705" Then M(289) = M(289) + 1
                        If Left(Ew(i, Gsdm1), 3) = "689" Then M(296) = M(296) + 1
                        If Left(Ew(i, Gsdm1), 3) = "614" Then M(303) = M(303) + 1
                        If Left(Ew(i, Gsdm1), 3) = "631" Then M(310) = M(310) + 1
                        If Left(Ew(i, Gsdm1), 3) = "675" Then M(317) = M(317) + 1
                    End If
                End If
            End If
            i = i + 1
        Loop
        
        Dim Zi As Integer '为了使代码简洁，在此处采用循环完成赋值
        For Zi = 1 To 340
            If M(Zi) <> 0 Then
            xlA.ActiveWorkbook.Sheets(1).Cells(k, Zi).Value = xlA.ActiveWorkbook.Sheets(1).Cells(k, Zi).Value + M(Zi)
            M(Zi) = 0
            End If
        Next Zi
    
        i = 2
        k = k + 1
        Label5.Caption = "正在写入第" & k - 4 & "位员工数据..."
    Loop
    
    Erase Ew
    
    xlAbo.Save
    xlBbo.Save
    MyFile = Dir '抽取该文件夹内第二个excel
    Wjs = Wjs + 1
Loop '为文件循环结束
Label6.Caption = "累计完成读取" & Wjs - 1 & "个Excel文件"
Label4.Caption = "工作量统计完成！"
Label5.Caption = "员工数据已写入完成！"
xlAsh.Range("B4:ME200").Sort xlAsh.Range("E4"), 2
xlAbo.Save
xlAsh.Range("B4:ME200").Sort xlAsh.Range("B4"), 1
xlAbo.Save    '这四行语句用来给导出的工作量按组按工作量排序

xlA.Quit '关闭EXCELL
Set xlA = Nothing '释放资源
xlB.Quit '关闭EXCELL
Set xlB = Nothing '释放资源
xlA.Workbooks.Open (CurDir + "\" + YWB + "分时工作量统计.XLSX") '打开EXCEL模板，Curdir为当前目录
xlA.Visible = True '使Excel隐藏不可见 false
End Sub

Private Sub Command2_Click()
End
End Sub

Private Sub Form_Load()
YWB = "总账"
End Sub

Private Sub Mbwh1_Click(Index As Integer)
xlA.Workbooks.Open (CurDir + "\CoreDate\" + YWB + "月度.dat") '打开EXCEL模板，Curdir为当前目录
xlA.Visible = True '使Excel隐藏不可见 false
End Sub

Private Sub Mbwh2_Click(Index As Integer)
xlA.Workbooks.Open (CurDir + "\CoreDate\" + YWB + "分时.dat") '打开EXCEL模板，Curdir为当前目录
xlA.Visible = True '使Excel隐藏不可见 false
End Sub

Private Sub Sysm1_Click(Index As Integer)
woA.Documents.Open (CurDir + "\CoreDate\操作手册.DOCX")
woA.Visible = True
End Sub

Private Sub Sysm2_Click(Index As Integer)
Form2.Show
End Sub

还有个模块的代码，如下

'******手工凭证判定函数******
Public Function HandPzbh(x) As Boolean '凭证编号判定
HandPzbh = True
If Left(x, 2) = "47" Then
HandPzbh = False
ElseIf Left(x, 2) = "22" Then HandPzbh = False '资金部门筛选字段
End If

End Function

Public Function HandPzttwb(x) As Boolean
HandPzttwb = True
If x = "自动清帐 SAPF124" Then
HandPzttwb = False '总账自动清账字段
ElseIf x = "自动清帐" Then HandPzttwb = False

End If
End Function

Public Function HandTcode(x) As Boolean
HandTcode = True '若是手工凭证，会返回True
If x = "FBB1" Then
HandTcode = False '总账自动清账字段
ElseIf x = "ZFIEXS00900" Then HandTcode = False
ElseIf x = "CJ88" Then HandTcode = False '成本自动清账字段
ElseIf x = "CJ8G" Then HandTcode = False
ElseIf x = "CKME" Then HandTcode = False
ElseIf x = "CKMLCP" Then HandTcode = False
ElseIf x = "CO88" Then HandTcode = False
ElseIf x = "CON2" Then HandTcode = False
ElseIf x = "KO88" Then HandTcode = False
ElseIf x = "KO8G" Then HandTcode = False
ElseIf x = "KSU5" Then HandTcode = False
ElseIf x = "KSV5" Then HandTcode = False
ElseIf x = "ZCO88" Then HandTcode = False
ElseIf x = "ZCOELH10080" Then HandTcode = False
ElseIf x = "ZCOELH10081" Then HandTcode = False
ElseIf x = "ZCOELH10082" Then HandTcode = False
ElseIf x = "ZCOELH10100" Then HandTcode = False
ElseIf x = "ZCOELH10110" Then HandTcode = False
ElseIf x = "ZCOELH10170" Then HandTcode = False
ElseIf x = "ZCOELH10180" Then HandTcode = False
ElseIf x = "ZCOELH10190" Then HandTcode = False
ElseIf x = "ZCOEYT00010" Then HandTcode = False
ElseIf x = "ZCORJL10070" Then HandTcode = False
ElseIf x = "ZCORYZ10280" Then HandTcode = False
ElseIf x = "ZFIELH10020" Then HandTcode = False
ElseIf x = "ZFIELH10030" Then HandTcode = False
ElseIf x = "ZFIEYS10040" Then HandTcode = False
ElseIf x = "ZFIRGX10030" Then HandTcode = False
ElseIf x = "ZFIRLH00100" Then HandTcode = False
ElseIf x = "ZFIRZQ10010" Then HandTcode = False
ElseIf x = "ZFIRZQ10030" Then HandTcode = False
ElseIf x = "ZFIEXS00030" Then HandTcode = False
ElseIf x = "ZFIEDQ10070" Then HandTcode = False
End If
End Function