[VBA] 엑셀 -> MS SQL

엑셀 -> MS SQL [大阪府、時間差]

Private Const TAGET_SHEET       As String = "通計対象_CTI着信ログ"  '通計対象シート
Private Const DIALBEGIN_ROW      As String = "G2"                    'DialBeginDatetimeの最初のデータ位置
Private Const OPCONNECT_ROW      As String = "K2"                    'OpConnectDateTimeの最初のデータ位置
Private Const PRINT_ROW          As String = "A6"                    '出力基準位置
Private Const NOT_CONNECT        As String = "1970/01/01"            '未応対
Private Const RESULT_ARR         As Long = 9                         '通計区分数

Private Const RESULT_TIME_ROW_1  As String = "B6"                    '通計結果
Private Const RESULT_TIME_ROW_2  As String = "C6"
Private Const RESULT_TIME_ROW_3  As String = "D6"
Private Const RESULT_TIME_ROW_4  As String = "E6"
Private Const RESULT_TIME_ROW_5  As String = "F6"
Private Const RESULT_TIME_ROW_6  As String = "G6"
Private Const RESULT_TIME_ROW_7  As String = "H6"
Private Const RESULT_TIME_ROW_8  As String = "I6"

'比較時間設定
Private Enum RESULT_TIME
    A = 3
    B = 5
    C = 10
    D = 15
    E = 20
    F = 25
    G = 30
End Enum

Private Sub CommandButton1_Click()

Dim dDialBegin                  As Date     '受信時間
Dim dOpConnect                  As Date     '応対時間
Dim iTimeResult                 As Integer  '待ち時間
Dim alResult(RESULT_ARR)        As Long     '時間別通計
Dim alTotResult(RESULT_ARR)     As Long     '総計
Dim cntRow                      As Long     '対象シートのデータ数
Dim dBasicTime                  As Date     '比較する日付
Dim lPrintCnt                   As Long     '出力カウンタ
Dim iColorFlag                  As Integer  '表示色

cntRow = CLng(Sheets(TAGET_SHEET).Range("A1").End(xlDown).Row) - 1

'最初データの時間
dBasicTime = Sheets(TAGET_SHEET).Range(DIALBEGIN_ROW).Offset(0)

'最初色設定
iColorFlag = 15

For i = 0 To cntRow

    dDialBegin = Sheets(TAGET_SHEET).Range(DIALBEGIN_ROW).Offset(i)
    dOpConnect = Sheets(TAGET_SHEET).Range(OPCONNECT_ROW).Offset(i)
      
    '出力
    If Not Format(dBasicTime, "YYYY/MM/DD") = Format(dDialBegin, "YYYY/MM/DD") Or Not Format(dBasicTime, "HH") = Format(dDialBegin, "HH") Then
       
        '日時出力
        Range(PRINT_ROW).Offset(lPrintCnt) = Format(dBasicTime, "YYYY/MM/DD") & "  " & Format(dBasicTime, "HH") & " 時"
        Range(PRINT_ROW).Offset(lPrintCnt).Interior.ColorIndex = iColorFlag
       
        'データ出力
        For j = 0 To RESULT_ARR
            Range(PRINT_ROW).Offset(lPrintCnt, j + 1) = alResult(j)
            Range(PRINT_ROW).Offset(lPrintCnt, j + 1).Interior.ColorIndex = iColorFlag
            alTotResult(j) = alTotResult(j) + alResult(j)

        Next j
               
        '表示色設定
        If Not Format(dBasicTime, "YYYY/MM/DD") = Format(dDialBegin, "YYYY/MM/DD") Then
            If iColorFlag = 15 Then
                iColorFlag = 48
            Else
                iColorFlag = 15
            End If
        End If
       
        'データカウンタ
        lPrintCnt = lPrintCnt + 1
       
        '比較する時間
        dBasicTime = dDialBegin
       
        'データ配列初期化
        Erase alResult
       
    End If
   
    'カウンター
    If Format(dOpConnect, "YYYY/MM/DD") = NOT_CONNECT Then
        '応対外
        alResult(8) = alResult(8) + 1
    Else
        '待ち時間カウント
        iTimeResult = Format(dOpConnect - dDialBegin, "SS")
   
       
        Select Case iTimeResult
       
            Case Is < RESULT_TIME.A
                alResult(0) = alResult(0) + 1
       
            Case RESULT_TIME.A To RESULT_TIME.B
                alResult(1) = alResult(1) + 1
           
            Case (RESULT_TIME.B + 1) To RESULT_TIME.C
                alResult(2) = alResult(2) + 1
               
            Case (RESULT_TIME.C + 1) To RESULT_TIME.D
                alResult(3) = alResult(3) + 1
                           
            Case (RESULT_TIME.D + 1) To RESULT_TIME.E
                alResult(4) = alResult(4) + 1
             
            Case (RESULT_TIME.E + 1) To RESULT_TIME.F
                alResult(5) = alResult(5) + 1
               
            Case (RESULT_TIME.F + 1) To RESULT_TIME.G
                alResult(6) = alResult(6) + 1
            
            Case Is > RESULT_TIME.G
                alResult(7) = alResult(7) + 1
               
        End Select
    End If
   
    alResult(9) = alResult(9) + 1
Next i

'総計出力
Range(PRINT_ROW).Offset(lPrintCnt) = "総計"
Range(PRINT_ROW).Offset(lPrintCnt).Font.Bold = 1
Range(PRINT_ROW).Offset(lPrintCnt).Interior.ColorIndex = 43

For k = 0 To RESULT_ARR
    Range(PRINT_ROW).Offset(lPrintCnt, k + 1) = alTotResult(k)
    Range(PRINT_ROW).Offset(lPrintCnt, k + 1).Font.Bold = 1
    Range(PRINT_ROW).Offset(lPrintCnt, k + 1).Interior.ColorIndex = 43
Next k

End Sub