小さい会社の1人SEの開発記というか奮戦記

「SQL Server Express と ADO、Excel と VBA を使って課題管理表を作ってみよう」のデータ登録ボタンをクリックしたときの処理について説明します。

考え方

1. C列の行の最大値を取得する
2. 最大値＝2であれば対象無しとして処理を終了する
3. 3行目から行の最大値行までC列の値を確認する。空白であれば次の行に行き、空白でなければ処理を行う
4. C列の値が「追加」の場合、Noの最大値を取得しそれに+1しデータを追加する
5. D列の値が「更新」の場合、Noはそのままでデータを追加する。
6. E列の値が「削除」の場合、同一Noのレコードの削除フラグに「削除」を入れる。

※実際の表示は同一Noの中で最新のもののみを表示する。

プログラムのポイント

ここではExcelのVBAでプログラムのポイントになるところを特記します。全体についてはプログラム全てを最後に記述しますのでそちらを参照下さい。

C列の最大行

Dim i2 As Long ‘C列最大行数
i2 = Cells(Cells.Rows.Count, 3).End(xlUp).Row

Ｃ列なのでCellsの右側は3、この数式を使うことで「指定した列」の下から上に「文字が入力されているセル」を探してその行の数を表示することができます。DEBUG.Print や MsgBox を使って事前に値を確認しておきましょう。この処理はワークシートを指定していないのでアクティブなシートが対象です。ワークシートを指定する場合はCellsの前にワークシートオブジェクトを指定する必要があります。

3行目から行の最大値行までC列の値を取得

Dim i2 As Long ‘行カウンタ
For i1 = 3 To i2
：
C列の値は Cells(i1, 3).Value
：
Next

Noの最大値を取得するSQL文

Dim TXT_SQ As String
TXT_SQ = ”SELECT MAX(No) AS 管理番号最大 FROM dbo.T_課題管理表”

追加・更新処理

Dim ADO_RS As Object ‘レコードセットオブジェクト
：
Set ADO_RS = CreateObject(“ADODB.Recordset”) ‘ADOレコードセットオブジェクトを作成
With ADO_RS
.Open “T_課題管理表”, ADO_CN, adOpenDynamic, adLockOptimistic, adCmdTable
.AddNew
!分類 = Cells(i1, 4).Value
!課題 = Cells(i1, 5).Value
!対応進捗 = Cells(i1, 6).Value
!担当者 = Cells(i1, 7).Value
!責任者 = Cells(i1, 8).Value
!更新日 = Date
!完了予定日 = Cells(i1, 10).Value
!完了日 = Cells(i1, 11).Value
!更新時間 = Now()
.Update
.Close
End With

削除処理

SQL文：“UPDATE T_課題管理表 SET 削除フラグ = ‘1’ WHERE (No = ” & Cells(i1, 2).Value & “);”
削除するNoを持つレコードの削除フラグに 1 を立てる。

Set ADO_RS = CreateObject(“ADODB.Command“) ‘ADOコマンドオブジェクトを作成
With ADO_RS
.ActiveConnection = ADO_CN
.CommandText = TXT_SQ
.Execute
End With

実際に作成したプログラム

Sub データ登録_Click()
    
    '   変数宣言
    Dim i1, i2, i3 As Long      'カウンタ
    Dim ADO_RS As Object        'レコードセットオブジェクト
    Dim TXT_SQ As String        'SQL文
    Dim TIM_NW As Date          '現在時刻
    
    TIM_NW = Now()              '現在時刻設定
    
    i2 = Cells(Cells.Rows.Count, 3).End(xlUp).Row   'C列最終行
    
    Call connect                 'データベース接続
    
    '   SQL文
    TXT_SQ = "SELECT MAX(No) AS 管理番号最大 FROM dbo.T_課題管理表"
    
    Set ADO_RS = CreateObject("ADODB.Recordset")
    ADO_RS.Open TXT_SQ, ADO_CN
        If ADO_RS.EOF Then      '1件目
            i3 = 0
        Else
            i3 = ADO_RS("管理番号最大")
        End If
    ADO_RS.Close
    
    If i2 > 2 Then              'I2 = 2で登録対象無しです。
        For i1 = 3 To i2
            
            '   バッチ = 「追加」
            If Cells(i1, 3).Value = "追加" Then     'バッチが「追加」だったら処理実行
                i3 = i3 + 1
                
                '   データ追加処理
                Set ADO_RS = CreateObject("ADODB.Recordset")
                With ADO_RS
                    .Open "T_課題管理表", ADO_CN, adOpenDynamic, adLockOptimistic, adCmdTable
                    .AddNew
                        !No = i3
                        !分類 = Cells(i1, 4).Value
                        !課題 = Cells(i1, 5).Value
                        !対応進捗 = Cells(i1, 6).Value
                        !担当者 = Cells(i1, 7).Value
                        !責任者 = Cells(i1, 8).Value
                        !更新日 = Date
                        !完了予定日 = Cells(i1, 10).Value
                        !完了日 = Cells(i1, 11).Value
                        !更新時間 = Now()
                    .Update
                    .Close
                End With
                
            '   バッチ = 「更新」
            ElseIf Cells(i1, 3).Value = "更新" Then
                
                If Not IsNumeric(Cells(i1, 3).Value) Then   '管理番号が数値じゃ無かったらエラー
                    Call disconnect
                    MsgBox "管理番号が異常です"
                    Cells(i1, 2).Select
                    Exit Sub
                End If

                Set ADO_RS = CreateObject("ADODB.Recordset")
                With ADO_RS
                    .Open "T_課題管理表", ADO_CN, adOpenDynamic, adLockOptimistic, adCmdTable
                    .AddNew
                        !No = Cells(i1, 2).Value
                        !分類 = Cells(i1, 4).Value
                        !課題 = Cells(i1, 5).Value
                        !対応進捗 = Cells(i1, 6).Value
                        !担当者 = Cells(i1, 7).Value
                        !責任者 = Cells(i1, 8).Value
                        !更新日 = Date
                        !完了予定日 = Cells(i1, 10).Value
                        !完了日 = Cells(i1, 11).Value
                        !更新時間 = Now()
                    .Update
                    .Close
                End With
            
            '   バッチ = 「削除」
            ElseIf Cells(i1, 3).Value = "削除" Then
                If Not IsNumeric(Cells(i1, 2).Value) Then   '管理番号が数値じゃ無かったらエラー
                    Call disconnect
                    MsgBox "管理番号が異常です"
                    Cells(i1, 2).Select
                    Exit Sub
                End If
    
                '   SQL文
                TXT_SQ = "UPDATE T_課題管理表 SET 削除フラグ = '1' WHERE (No = " & Cells(i1, 2).Value & ");"
                    
                Set ADO_RS = CreateObject("ADODB.Command")
                With ADO_RS
                    .ActiveConnection = ADO_CN
                    .CommandText = TXT_SQ
                    .Execute
                End With
            End If
        Next
    End If
    
    Call disconnect             'データベース接続解除
    
    MsgBox "処理が完了しました。"
End Sub

まとめ

ポイントになるのがレコードセットの CreateObject の使い方です。

追加と更新はレコード単位で処理してしまうので
Set ADO_RS = CreateObject(“ADODB.Recordset“)

削除はSQLコマンドで複数行処理していますので
Set ADO_RS = CreateObject(“ADODB.Command“)

を使います。使い方のポイントになりますので覚えておきましょう。

前の記事 →「SQL-Excelサンプル VBAで接続テストを行う」
次の記事 →「SQL-Excelサンプル 課題管理表全件表示ボタンを押したときの処理」