エクセルで最強の進捗管理表を作る【②フォーム作成】
前回に引き続き進捗管理表を作成していきます。
進捗管理表を作る上で日程や担当者の記録など、進捗管理表がちゃんと機能するための項目が必要ですよね？
そこで今回は進捗管理表の項目を作っていこうと思います。
進捗管理表の項目としては、
「ID、プロジェクト名、テーマ名、担当者、内容、開始日程、終了日程、備考、更新日時」があれば十分だと思います。
他に追加する項目があればどんどん追加していってください。
進捗管理表の項目を作成する
項目を作成するVBAコードは以下のようになります。
Accessデータベースに列が追記されていきます。
参照設定を忘れないようにしてください。
Public Sub テーブル作製() Dim TB As ADOX.Table Dim CAT As ADOX.Catalog Dim DBFile As String Dim strDB As String Dim CN As ADODB.Connection DBFile = "Accessのパス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile Set CAT = New ADOX.Catalog CAT.ActiveConnection = strDB Set TB = New ADOX.Table TB.Name = "進捗管理表" '*****Accessに列を追加***** With TB.Columns .Append "ID", adInteger .Append "プロジェクト名", adVarWChar, 50 .Append "テーマ名", adVarWChar, 50 .Append "担当者", adVarWChar, 50 .Append "内容", adVarWChar, 50 .Append "開始日程", adDate .Append "終了日程", adDate .Append "備考", adVarWChar, 50 .Append "更新日時", adVarWChar, 50 '★★★ End With CAT.Tables.Append TB Set CAT = Nothing Set TB = Nothing MsgBox "終了しました" End Sub
9行目にはAccessデータベースの絶対パスを入力してください。
追加したい項目があれば★★★印のところに追記してください。
ただしデータ型には注意。
追加する項目のデータ内容が文字ならadVarWChar, 50、数字ならadInteger、日付ならadDateです。
メッセージボックスに「終了しました」と表示されたら、きちんと項目が追加されたか確認しましょう。
次のコードを打ち込んでExcel上にちゃんと表示されているか確認してください。
Public Sub 全列名取得() '参照設定：Microsoft ADO Ext.6.0 for DDL and Security Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Dim strDB As String Dim DBFile As String Dim SQL As String Dim AryData() Dim j DBFile = "Accessのパス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile SQL = "SELECT * FROM 進捗管理表" Set CN = New ADODB.Connection CN.ConnectionString = strDB CN.Open Set RS = New ADODB.Recordset RS.Open Source:=SQL, ActiveConnection:=CN '******Accessの列名を配列に格納****** For j = 0 To RS.Fields.Count - 1 ReDim Preserve AryData(j) AryData(j) = RS(j).Name Next j RS.Close CN.Close Set RS = Nothing Set CN = Nothing '******Excel上に配列に格納した列名を表示****** For j = 0 To UBound(AryData) Cells(1, j + 1) = AryData(j) Next j End Sub
↓実行結果
無事Accessデータベースに追加されていることが確認できました！
フォーマットを作る
Accessに追加されていることが確認できたら今度はユーザーフォームを開いてフォーマット作りをしてみましょう。
試しにこんな感じで作ってみました。
プロジェクト、テーマ、担当者は項目から選べるようにできれば便利かと思ってコンボボックスに、内容と備考に関しては自由度が高いと思うのでテキストボックスにしました。
日程のところはスピンボタンも入れて操作のしやすさを出してみました。
コードを作る
フォーマットができたら今度はコーディングを行っていきましょう。
フォーマットに情報を入力できたらAccessに反映されるようにしていきます。
フォーマットの「OK」と書かれたコマンドボタンを右クリック→コードの表示でこんな画面が表示されます。
この中にコードを記述していきます。
Private Sub CommandButton2_Click() Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Dim strDB As String Dim DBFile As String Dim SQL As String Dim i As Long DBFile = "Accessのパス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile Set CN = New ADODB.Connection CN.ConnectionString = strDB CN.Open '*****IDの最大値を取得****** SQL = "SELECT MAX(ID) FROM 進捗管理表" Set RS = New ADODB.Recordset RS.Open Source:=SQL, ActiveConnection:=CN '*****ID未入力の場合i=0にする****** i = 0 '*****IDの最大値をiの値に設定。IDが記入されていないときのエラー処理を併記***** On Error Resume Next i = RS(0) RS.Close RS.Source = "進捗管理表" RS.ActiveConnection = CN RS.CursorType = adOpenDynamic RS.LockType = adLockOptimistic RS.Open '*****各列にユーザーフォームのデータを追加****** RS.AddNew RS.Fields("ID") = i + 1 RS.Fields("プロジェクト名") = Me.Cmbプロジェクト RS.Fields("テーマ名") = Me.Cmbテーマ RS.Fields("担当者") = Me.Cmb担当者 RS.Fields("内容") = Me.Txt内容 RS.Fields("開始日程") = DateSerial(Me.Txt開始年, Me.Cmb開始月, Me.Cmb開始日) RS.Fields("終了日程") = DateSerial(Me.Txt終了年, Me.Cmb終了月, Me.Cmb終了日) RS.Fields("備考") = Me.Txt備考 RS.Fields("更新日時") = Now RS.Update RS.Close CN.Close Set RS = Nothing Set CN = Nothing '*****入力したデータをExcelに表示****** Call 全データ取得 MsgBox "終了しました" End Sub
Accsessへのデータ入力の方法はこちらの記事に載せてますので参考までに。
続いてモジュール画面から新しいプロシージャを作成し、以下のコードを貼ります。
入力されたデータをExcelに表示するプログラムです。
上のコードの59行目にあるCallを呼び出すためのものです。
Public Sub 全データ取得() '参照設定：Microsoft ADO Ext.6.0 for DDL and Security Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Dim strDB As String Dim DBFile As String Dim SQL As String Dim AryData() Dim i Dim j DBFile = "Accessのパス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile SQL = "SELECT * FROM 進捗管理表" Set CN = New ADODB.Connection CN.ConnectionString = strDB CN.Open Set RS = New ADODB.Recordset RS.Open Source:=SQL, ActiveConnection:=CN Cells.SpecialCells(xlCellTypeVisible).Clear '****Accessにデータが入っていないとき列名だけを配列に格納****** If RS.EOF = True Then For j = 0 To RS.Fields.Count - 1 ReDim Preserve AryData(RS.Fields.Count - 1, 0) AryData(j, 0) = RS(j).Name Next j GoTo L1 Else '*****Accessにデータが入っているときは先頭行に移動****** RS.MoveFirst End If i = 1 '******Accessに入っているデータを全部配列に格納****** Do Until RS.EOF ReDim Preserve AryData(RS.Fields.Count - 1, i) For j = 0 To RS.Fields.Count - 1 AryData(j, 0) = RS(j).Name AryData(j, i) = RS(j) Next j i = i + 1 RS.MoveNext Loop L1: RS.Close CN.Close Set RS = Nothing Set CN = Nothing '*****配列に格納したデータをExcelに表示****** On Error Resume Next For i = 0 To UBound(AryData, 2) For j = 0 To UBound(AryData, 1) Cells(i + 1, j + 1) = AryData(j, i) Next j Next i End Sub
コードが貼り終わったら作成したユーザーフォームを開き、データ入力がきちんとできているか確認しましょう。
試しに次のようなデータを入力しました。
ユーザーフォームの「OK」ボタンを押すと次にような結果が表示されるはずです。
