以前に改修した神エクセル帳票の記入項目を見直し、データベースに登録する項目をピックアップします。さらに、テーブルレイアウトを決めてデータ定義書を作成し、CREATE文の出力機能を搭載してみました。
DBの準備：データ定義
改修神エクセル帳票の記入項目からデータベースに登録する項目をピックアップし、データ定義書を作成してみたいと思います。
帳票の記入項目からDB登録項目のピックアップ
帳票は、以前の記事「神エクセルを改修してみる」で、改修したユーザID登録申請書（図1）を活用したいと思います。本帳票の記入項目から、データベースに登録する項目をピックアップします。
改修したユーザID登録申請書には、利用する記入項目を抽出し、登録先のDBレイアウトに沿って並べる体で、帳票欄外に申請書データ抽出欄が設けられています（図2）。都合が良いので、図2の抽出項目を登録対象としてデータの整備を行って行きたいと思います。
表1は、改修したユーザID登録申請書の各入力欄（図1）と、申請書データ抽出欄（図2）の各カラム名の対応表です。表1からデータベースに登録する項目をピックアップします。№1:誓約は申請受理の前提で、ユーザID登録申請書本体の「情報セキュリティに関する誓約」のチェックボックスがONの状態で、誓約書の役割を果たすので、登録項目ではない（抽出データ一式の登録可/不可判定の為、取得はする）。№2～№16は、ユーザID登録に必須な情報の為、データベース登録項目とします。
| № | 図2上段抽出欄 | 図1#1入力欄 | № | 図2下段抽出欄 | 図1#1入力欄 |
| 1 | 誓約 | 情報セキュリティ誓約 ※チェックボックス | 11 | email_add | 発行予定メールアドレス |
| 2 | user_id | 希望するユーザID | 12 | access_lv | 利用資格 ※ラジオボタン |
| 3 | uname_kj | 漢字氏名 | 13 | option_jyuki | 追加利用システム-住基 ※チェックボックス |
| 4 | uname_kana | フリガナ | 14 | option_dev | 追加利用システム-開発 ※チェックボックス |
| 5 | eｍp_num | 職員番号 | 15 | remarks | 備考 |
| 6 | department | 所属（部） | № | 図2下段抽出欄 | 図1#2入力欄 |
| 7 | division | 所属（課） | 16 | application_num | 申請管理番号 ※申請書の稟議処理の為に発行されるID |
| 8 | unit | 所属（係その他） | |||
| 9 | extension_num | 連絡先（内線） | |||
| 10 | birthday | 生年月日（西暦） |
※カラム値は入力欄をセル参照して取得し、チェックボックス・ラジオボタンは、選択値を直接データ抽出欄に出力する
テーブルレイアウトの決定とデータ定義書の作成
ここ迄の経緯を踏まえて、表1の№2～№16を記載順にExcel上に展開して、テーブルレイアウトとし、各項目に項目名、データ型、桁数、主キー設定等を追加し、帳票から抽出したデータを、ユーザID情報テーブルにまとめて登録する体で、データ定義書を作成しました（図3）。※図3：№16～18は内部データ管理用に追加したカラムです。
図3：ユーザID情報テーブルのデータ定義で黄色く着色した列が、実際のデータ処理に使う項目です。また、黄緑色に着色した№列の項番は、各項目のデータ取得に使う為、連番を振ってあります。
データ定義書にCREATE文出力機能を搭載する
一般にデータ定義書は、対象テーブルのテーブルレイアウトに準じて記載されているのて、そのことを利用し、記載データを使ってCREATE文を出力する機能が付いていることが多いです。本定義書にも、図3の着色列を利用し、CREATE文を出力する機能を搭載してみました。
データ定義書はExcelで作ったので、CREATE文を出力する機能は、ExcelVBAにて実装しました。汎用的に使えるように、CREATE文は標準SQL構文で出力する仕様とします。これらを踏まえて、参考サイト（デザインポケット：VBAでテーブル仕様書からDLL文を自動生成する）の掲載コード等を手本にして、以下のようなコードを作成しました。
「ｍain」ルーチン
' データ定義書からCreate文を作成
'
Sub main()
Dim ddlws As Worksheet 'データ定義シート
Dim bookPath As String 'データ定義書のフォルダパス
'現行のデータ定義シートを処理対象に設定
Set ddlws = ThisWorkbook.ActiveSheet
'データ定義書のフォルダパス（出力先フォルダパス）を取得
bookPath = ThisWorkbook.Path
'Create文出力サブルーチン呼出し
mk_Create_statement ddlws, bookPath
'Create文出力を通知し、出力先フォルダを開く
MsgBox "Create文の出力が終わりました。"
Shell "C:\Windows\Explorer.exe " & bookPath, vbNormalFocus
End Sub以上が「ｍain」ルーチンです。プログラムの概要は、以下のようになります。
- 現行のデータ定義シートとデータ定義書のフォルダパス（出力先フォルダパス）を取得する
- CREATE文出力サブルーチンを呼出し、データ定義シートと出力先フォルダパスを渡す
- CREATE文の出力を通知し、出力先フォルダを開く
ｍainルーチンのコード構成は、参考サイトに倣ったもので、典型的なパラメータの受け渡しかしていないので、出力先フォルダを開くのに使った、Shell関数だけ説明します。
Shell関数
Shell関数は、指定された実行可能プログラムを実行し、成功した場合はプログラムのタスク ID を表す Variant (Double) 値を返し、失敗した場合は 0 を返します。
Shell関数の構文は、RetVal = Shell（pathname, windowstyle（オプション））として記載されています。RetVal：プログラムのタスク ID（異常時 0）、pathname：指定プログラム(パス指定含む)、windowstyle：省略可能(オプション)。プログラム実行時のウィンドウのスタイルを指定するVariant (Integer)値。指定省略時、プログラムがフォーカスを持った状態で最小化されて起動する。
しかし、参考にしたサイトのサンプルコードなどを見た限りでは、Shell pathname, windowstyle（オプション）のように戻り値を聴かず、外部関数のような使い方をすることが多いです。今回は、データ定義書と同じフォルダに、自身で作成したSQLファイルを出力する為、不確定要素がないので、こちらの使い方に倣い、上記「ｍain」ルーチンコード欄の17行目、以下のような記述になりました。
Shell "C:\Windows\Explorer.exe " & bookPath, vbNormalFocus- “C:\Windows\Explorer.exe ” ⇒ Explorer起動指定(デフォルトパス) + ” “(半角スペース)
- bookPath ⇒ データ定義書のフォルダパス「Explorer.exe フォルダ指定」で指定フォルダを開く
- vbNormalFocus ⇒ VB定数：開いたウィンドウがフォーカスを持ち、元のサイズと位置に表示される
「mk_Create_statement（Create文出力）」サブルーチン
' Create文出力サブルーチン（シート毎の処理）
'
' 引数：データ定義シート、出力先フォルダパス
' 概要：データ定義書と同じフォルダにCreate文を出力する
'
Sub mk_Create_statement(ddlws As Worksheet, ddbkPath As String)
'Create文出力先SQLファイル作成用
Dim f As Integer 'ファイル番号
Dim fname As String 'ファイル名
'データ定義書シート読込み（テーブル情報）
Dim tid As String 'テーブルID
'データ定義書シート読込み（カラム情報）
Dim clmID As String 'カラムID
Dim clmtype As String 'データ型
Dim clmleng As String '桁数
Dim clmpk As String '主キー
Dim clmnull As String '必須項目
Dim clmval As String '初期値
Dim pkarray(30) As String '主キー格納配列
Dim pkcount As Integer '主キーカウンタ
Dim i As Integer '処理カウンタ
Dim linectr As Integer 'カラム情報取得開始行
Dim workstr As String 'SQL編集文字列
pkcount = 0 '主キーカウンタ初期化
linectr = 6 'カラム情報取得開始行のセット
'データ定義シートからテーブルID取得
tid = ddlws.Cells(3, 4) 'テーブルID
'SQLファイル名の設定（出力先フォルダパス + "\" +"Create_" + テーブルID + ".sql"）
fname = ddbkPath & "\" & "Create_" & tid & ".sql"
'ファイルオープン
'（データ定義シート情報からCreate文作成）
f = FreeFile
Open fname For Output As #f
'Create Table文の編集開始
Print #f, "CREATE TABLE " & tid & " ("
'データ定義シートからテーブルレイアウト取得（No列の項番分繰返す）
Do While ddlws.Cells(linectr, 2) <> ""
'カラムの情報を読み取る
clmID = Trim(ddlws.Cells(linectr, 3)) 'カラムID
clmtype = Trim(ddlws.Cells(linectr, 6)) 'データ型
clmleng = Trim(ddlws.Cells(linectr, 7)) '桁数
clmpk = Trim(ddlws.Cells(linectr, 8)) '主キー
clmnull = Trim(ddlws.Cells(linectr, 9)) '必須項目
clmval = Trim(ddlws.Cells(linectr, 10)) '初期値
'Create Table文の自動生成
workstr = ""
'カラム名編集
workstr = workstr & clmID & " "
'主キー指定項目の格納
If clmpk = "〇" Then
pkarray(pkcount) = clmID
pkcount = pkcount + 1
End If
'データ型編集（VARCHAR、INTEGER、DATE型を想定）
Select Case clmtype
Case "VARCHAR"
workstr = workstr & clmtype
workstr = workstr & "(" & clmleng & ")"
Case "INTEGER"
workstr = workstr & clmtype
Case "DATE"
workstr = workstr & clmtype
Case Else
'想定外：何もしない
End Select
'初期値編集（指定時のみ）
If Len(clmval) > 0 Then
workstr = workstr & " DEFAULT " & clmval
End If
'必須項目編集（指定時のみ）
If clmnull = "〇" Then
workstr = workstr & " NOT NULL"
End If
'カラム毎のカンマ区切り
workstr = workstr & ","
Print #f, Space(4); workstr
linectr = linectr + 1
Loop
'主キー情報の編集
If pkcount > 0 Then
Print #f, "CONSTRAINT " & tid & "_PK PRIMARY KEY (";
For i = 0 To pkcount - 1
Print #f, pkarray(i);
If i < pkcount - 1 Then
Print #f, ",";
Else
Print #f, ")"
End If
Next
Else
'想定外：主キーは必ず設定されるものと想定
End If
'Create Table文の作成終了
Print #f, ")"
'SQLファイルクローズ
Close #f
End Sub以上が「mk_Create_statement」サブルーチンです。プログラムの概要は、以下のようになります。
- 処理対象のデータ定義シートと出力先フォルダを引数として取得する
- データ定義シートからテーブルIDを取得
- SQLファイル名の設定 ⇒ 出力先フォルダパス + “Create_” + テーブルID + “.sql”
- 出力先フォルダにSQLファイル(CREATE文)を出力
- データ定義シートの取得情報からCreate Table文を作成
データ定義シートと出力先フォルダを引数として取得
上記「mk_Create_statement」サブルーチンコード欄の6行目のサブルーチン定義の通りです。ｍainルーチン13行目のサブルーチン呼出しから、引数で受動的に渡されてきます。
Sub mk_Create_statement(ddlws As Worksheet, ddbkPath As String)- mk_Create_statement ⇒ Create文出力サブルーチン
- ddlws ⇒ ｍainルーチンから現行のデータ定義シートを受け取るWorksheet型引数
- bookPath ⇒ ｍainルーチンから出力先フォルダパスを受け取るString型引数
データ定義シートからテーブルIDを取得
サブルーチンコード欄の33行目にてデータ定義シートから取得しています。
'データ定義シートからテーブルID取得
tid = ddlws.Cells(3, 4) 'テーブルID- ddlws.Cells(3, 4) ⇒ 図3#1のテーブルID（D3）に対応する座標から値を取得
- tid ⇒ テーブルIDを受けるString型変数
SQLファイル名の設定
サブルーチンコード欄の36行目で、出力するSQLファイル名を設定しています。ファイル名の命名規則は、「出力先フォルダパス + “\” +”Create_” + テーブルID + “.sql”」で、出力先明示の為、フルパス付きで設定しています。
'SQLファイル名の設定（出力先フォルダパス + "\" +"Create_" + テーブルID + ".sql"）
fname = ddbkPath & "\" & "Create_" & tid & ".sql"- ddbkPath & “\” & “Create_” & tid & “.sql” ⇒ 命名規則どおりの文字列連結
- fname ⇒ String型変数、ファイル名格納例（”C:\temp\Create_T_UIDINFO.sql”）
出力先フォルダにSQLファイル(CREATE文)を出力
サブルーチンコード欄の40行目で未使用のファイル番号を取得し、41行目で取得したファイル番号に、ファイル設定を紐づけてOpenします。同時に出力先フォルダに、設定したファイル名で出力されます。
f = FreeFile
Open fname For Output As #f- f = FreeFile ⇒ ｆ：Integer型変数、FreeFile関数：未使用のファイル番号を取得
- Open ～ For Output As #f ⇒ 取得ファイル番号にファイル設定を紐づけ、出力モードで開く
- fname ⇒ 出力するSQLファイル設定、ファイル番号の紐づけと同時に、出力先フォルダに設定したファイル名でSQLファイルが作成される
構文：FreeFile(rangenumber)
引数 rangenumber（オプション、省略可） は、次の空きファイル番号を返す範囲を指定する Variant変数 です。 0 (デフォルト) を指定すると 1 以上 255 以下の範囲のファイル番号を返します。 1 を指定すると 256 以上 511 以下の範囲のファイル番号を返します。
データ定義シートの取得情報からCREATE TABLE文を作成
サブルーチンコード欄の44～116行で、データ定義シートからテーブルレイアウトと各項目の定義情報を取得してCREATE文として編集し、SQLファイルに出力しています。
CREATE TABLE文の編集開始
コード欄の44行目からCREATE TABLE文の編集を開始します。以降、汎用的に使えるように、標準SQL構文で出力します。
'Create Table文の作成開始
Print #f, "CREATE TABLE " & tid & " ("- Print #f ⇒ Print #ステートメント、ファイル番号に紐づいたファイルへの書込み命令
- “CREATE TABLE ” & tid & ” (” ⇒ CREATE TABLE “テーブルID” ( ～で出力開始。以降、標準SQL構文で出力する
データ定義シートからテーブルレイアウト編集ループ
コード欄の47～96行のDo Whileループが、CREATE TABLE文編集のメイン処理です。ループ文のコードが長い為、各処理ごとにブロック分けして、一部簡略化して説明します。
ループ処理回数の判定
コード欄の47行目のDo Whileループの開始で、データ定義シートのNo列（図3）について、項番の存在チェックを行い、No列に記入された項番分、ループ処理を行います。処理開始行については、30行目で設定しています。
各カラム情報の取得
コード欄の50～55行で、各項目のカラム情報（カラムID, データ型, 桁数, 主キー, 必須項目, 初期値）を取得しています。
主キー指定項目の格納
コード欄の63～66行で、データ定義シートの主キー列（図3#2）に、チェック（clmpk = “〇”）がある行のカラムID（clmID）を、主キー格納配列（pkarray(30)）に収集し主キーカウンタ（pkcount）をカウントアップしています。
データ型編集
コード欄の69～79行で、データ定義シートのデータ型／桁数列（図3#2）から、データ型（clmtype）／桁数（clmleng）を取得し、処理行のデータ型に対応した定義書式に編集しています。扱うデータ型は、VARCHAR、INTEGER、DATE型のみを想定しています。
初期値編集（指定時のみ）
コード欄の82～84行で、データ定義シートの初期値列（図3#2）に値指定がある行の項目に、初期値（clmval）設定を追記します。
必須項目編集（指定時のみ）
コード欄の87～89行で、データ定義シートの必須項目列（図3#2）に、チェック（clmnull = “〇”）がある行の項目に、”NOT NULL”指定を追記します。
カラム毎のカンマ区切り
コード欄の92～93行で、編集が終わった各項目の先頭に字下げ（空白）を追記し、末尾に区切り（カンマ）を追記します。
主キー情報の編集
サブルーチンコード欄の99～111行で、主キー指定項目について、主キー制約を追記します。前述の「データ定義シートからテーブルレイアウト作成ループ」内の「主キー指定項目の格納」処理で収集した、主キー格納配列（pkarray(30)）を使用して、主キー制約設定（PRIMARY KEY～）を編集し、先に出力したCREATE文に追記します。
主キー制約設定（PRIMARY KEY～）の編集開始
コード欄の100行目から、主キー制約設定に”テーブルID_PK”と名前を付けて、編集を開始します。
Print #f, "CONSTRAINT " & tid & "_PK PRIMARY KEY (";- “CONSTRAINT ” & tid & “_PK PRIMARY KEY (” ⇒ CONSTRAINT “テーブルID_PK” PRIMARY KEY ( ～と編集開始
- CONSTRAINT “テーブルID_PK” ⇒ 主キー制約に、“テーブルID_PK”と名前を付ける
- PRIMARY KEY ( ～ ⇒ 主キー制約（PRIMARY KEY）の編集開始
主キー制約設定項目の編集開始
コード欄の101～108行で、主キー制約を設定する各項目の末尾に、区切り（カンマ）を追記して出力します。
For i = 0 To pkcount - 1
Print #f, pkarray(i);
If i < pkcount - 1 Then
Print #f, ",";
Else
Print #f, ")"
End If
Next- For i = 0 To pkcount – 1 ～ Print #f, pkarray(i); ⇒ 主キー格納配列（pkarray(30)）を展開し、各項目毎に出力する。※主キーカウンタ（pkcount）と配列の添え字の差異を吸収する為、Forループは、0～主キーカウンタ -1まで回す
- コード欄の101～108行で、主キー制約を設定する各項目の末尾に区切り（カンマ）を追記し、最後の項目の場合、末尾をカッコ「”)”」で閉じる
SQLファイルクローズ
'Create Table文の作成終了
Print #f, ")"
'SQLファイルクローズ
Close #f- コード欄の114行目で、CREATE TABLE文編集終了の為、カッコ「”)”」を出力し末尾を閉じる
- コード欄の116行目、Close #f ⇒ CREATE TABLE文出力の終わったSQLファイルを閉じる
データ定義書に搭載した機能のテスト
Create文出力ボタンの追加
データ定義シートに「Create文出力」ボタンを追加し、mainルーチンを関連付けます。
| 1 | メニュー→[開発]→[挿入]→フォームコントロールから「ボタン」コントロールを選択 |  |
| 2 | 十字カーソルが出るので、ワークシートの任意の場所に、任意の大きさで貼り付ける |  |
| 3 | 「マクロの登録」画面が自動で開くので、マクロの保存先に「作業中のブック」を選択して新規作成をクリック |  |
| 4 | 自動で新しく標準モジュールが追加され、追加ボタンのクリックイベントが、書き込まれた状態で、VBエディタが開く |  |
| 5 | ①：4のボタンクリックイベントをコピーし、既存プログラムをまとめているモジュール(ここではModule1)を開き、コード末尾に貼り付ける ②：ボタンクリックイベント名を、適宜変更(ここではCreateTable_Click)し、mainルーチンを呼出す ③：自動で追加された標準モジュールを右クリック→[モジュールの開放]を選択し、VBAプロジェクトのツリーから消去する |  |
| 6 | ワークシートに戻り、追加ボタンを右クリック→[テキストの編集]を選択し、適宜変更(ここではCreat文出力) |  |
| 7 | 追加ボタンを右クリック→[マクロの登録]を選択 |  |
| 8 | 「マクロの登録」画面が開くので、5-②で変更したクリックイベント(ここではCreateTable_Click)を選択する |  |
| 9 | クリックイベント(CreateTable_Click)を選択したら、マクロの保存先に「作業中のブック」を選択して、OKをクリック |  |
Create文を出力する
データ定義シートに「Create文出力」ボタンを押下し、CREATE文を出力します（図5）。
「Create文出力の出力が終わりました。」メッセージが表示され、出力先フォルダが開くので、所定の命名規則（”Create_” + テーブルID + “.sql”）で、SQLファイルが作成（Create_T_UIDINFO.sql）されている事を確認します（図6）。
Create文の出力内容の確認
作成したSQLファイル（Create_T_UIDINFO.sql）のCreate文の以下出力内容と、図3のデータ定義書の設定内容を見比べて、仕様通りCREATE文が出力されている事を確認します。
CREATE TABLE T_UIDINFO (
user_id VARCHAR(8) NOT NULL,
uname_kj VARCHAR(128) NOT NULL,
uname_kana VARCHAR(128) NOT NULL,
eｍp_num VARCHAR(8) NOT NULL,
department VARCHAR(128) NOT NULL,
division VARCHAR(128) NOT NULL,
unit VARCHAR(128),
extension_num INTEGER,
birthday DATE NOT NULL,
email_add VARCHAR(28) NOT NULL,
access_lv INTEGER NOT NULL,
option_jyuki INTEGER DEFAULT 2,
option_dev INTEGER DEFAULT 2,
remarks VARCHAR(128),
application_num VARCHAR(7) NOT NULL,
del_flg INTEGER NOT NULL,
reg_date DATE,
up_date DATE,
CONSTRAINT T_UIDINFO_PK PRIMARY KEY (user_id)
)作成したデータ定義書のダウンロード
今回作成したデータ定義書は、以下の通りです。
データ定義書（CREATE文出力機能実装）.zip (1602 ダウンロード )関連記事
参考文献
- アイティメディア株式会社「＠IT」”「データベーススペシャリスト試験」戦略的学習のススメ（16）” 2022年4月29日閲覧
- デザインポケット(2016-) “VBAでテーブル仕様書からDLL文を自動生成する” 2022年4月29日閲覧
- パソコンスキルの教科書 “VBAでShellを使ってフォルダを開く｜最前面に表示(アクティブ化)” 2022年4月29日閲覧
- microsoft.com「Microsoft Build」”Shell 関数” 2022年4月29日閲覧
- microsoft.com「Microsoft Build」”FreeFile 関数” 2022年4月29日閲覧
コメント