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

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

考え方

1. シートにあるデータをクリアする
2. データを全件検索する。ソート順は、No, ID の順とする
3. レコードセットをシートに貼り付ける
4. レコードの最大行を基準にし罫線を引く

プログラムのポイント

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

B列の最大行

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

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

3行目から最大行までの削除

最大行数を i2 に代入し、
Rows(“3:i2”).Select
Selection.Delete Shift:=xlUp

全件を検索するSQL文

Dim TXT_SQ As String
TXT_SQ = ”SELECT No, 分類, 課題, 対応進捗, 担当者, 責任者, 更新日, 完了予定日, 完了日 FROM T_課題管理表 ORDER BY No, ID”

レコードセットをワークシートに貼り付ける

Do Until レコードセット.EOF ・・・ Loop を使います

i1 = 3
Do Until ADO_RS.EOF
：
Cells(i1, X).Value = ADO_RS(“XXX”)
：
i1 = i1 + 1
ADO_RS.MoveNext
Loop

罫線を引く

最終行を調べて範囲に Borders.LineStyle = True を設定します。

i2 = Cells(Cells.Rows.Count, 2).End(xlUp).Row ‘B列最終行
Range(“B3:K” & i2).Borders.LineStyle = True

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

Sub 全件表示_CLICK()
    '   変数宣言
    Dim i1, i2, i3 As Long      'カウンタ
    Dim ADO_RS As Object        'レコードセットオブジェクト
    Dim TXT_SQ As String        'SQL文

    '   過去データ削除処理
    i2 = Cells(Cells.Rows.Count, 2).End(xlUp).Row   'B列最終行
    
    If i2 > 2 Then
        Rows("3:" & i2).Select
        Selection.Delete Shift:=xlUp
    End If
    
    '   データベース処理
    Call connect                 'データベース接続
    TXT_SQ = "SELECT No, 分類, 課題, 対応進捗, 担当者, 責任者, 更新日, 完了予定日, 完了日 FROM T_課題管理表 ORDER BY No, ID"
    Set ADO_RS = CreateObject("ADODB.Recordset")
    ADO_RS.Open TXT_SQ, ADO_CN
    
    i1 = 3
    Do Until ADO_RS.EOF
        Cells(i1, 2).Value = ADO_RS("No")
        Cells(i1, 4).Value = ADO_RS("分類")
        Cells(i1, 5).Value = ADO_RS("課題")
        Cells(i1, 6).Value = ADO_RS("対応進捗")
        Cells(i1, 7).Value = ADO_RS("担当者")
        Cells(i1, 8).Value = ADO_RS("責任者")
        If IsNull(ADO_RS("更新日")) Then
            Cells(i1, 9).Value = ""
        Else
            Cells(i1, 9).Value = CDate(ADO_RS("更新日"))
        End If
        If IsNull(ADO_RS("完了予定日")) Then
            Cells(i1, 10).Value = ""
        Else
            Cells(i1, 10).Value = CDate(ADO_RS("完了予定日"))
        End If
        If IsNull(ADO_RS("完了日")) Then
            Cells(i1, 11).Value = ""
        Else
            Cells(i1, 11).Value = CDate(ADO_RS("完了日"))
        End If
        i1 = i1 + 1
        ADO_RS.MoveNext
    Loop
    
    ADO_RS.Close
    Set ADO_RS = Nothing
    
    Call disconnect             'データベース接続解除
    
    '   罫線を引く
    i2 = Cells(Cells.Rows.Count, 2).End(xlUp).Row   'B列最終行
    Range("B3:K" & i2).Borders.LineStyle = True
    
    MsgBox "処理が完了しました。"
    Cells(1, 1).Select

End Sub

まとめ

SQL文で呼び出して Cells(Y, X).Value に代入すると文字型になるというのはやってみないと分かりませんでしたね・・・。
CDate関数を使って日付型に変換してから代入しました。

前の記事 →「SQL-Excelサンプル 課題管理表登録ボタンを押したときの処理」
次の記事 →「SQL-Excelサンプル 課題管理表最新取得ボタンを押したときの処理」