ExcelでSQLを使う-008: EXCELとSQLツール ー SQLWriter(),SQLUpdate()
SQLツールと言っても、たかだか一ヶ月で開発したもの。二度の入院手術を挟んでの取組みだから、大層なものではない。臆面もなく、それらを一括紹介する。
１、エクセルブックを参照、更新、削除するツール
・DLookup()
・DSelect()
・SQLExecute()
・SQLWriter()
・SQLUpdate()←仮称
＊XferLitearal()
＊GetFieldName()
１－１、Dlookup()
Public Function DLookup(ByVal strSQL As String, _
Optional intSearch As Integer = 0, _
Optional xlFileName As String = "", _
Optional isHeader As Boolean = True, _
Optional returnValue As String = "") As Variant
‥‥
End Function
第1引数: SQL文。
第2引数: 何番目を検索するのか？-２は、最後から2番目。
規定値の場合は、最初に見つけたのを戻す。
第3引数: 対象のブックを指定する。
指定されてないと ThisWorkbook.FullName を採用。
第4引数: ヘッダーの有無。
第5引数: 戻り値。0、”×" などを戻り値として指定できる。
DLookup()に、最初から何番目、最後から何番目を求める声があったので第2引数を設けた。また、エクセルの式にDLookup()が書いた後に、関連するシートを修正・削除されると関数エラーが発生する。それが、余りにも煩わしいので《関数エラーの表示をする、しないのオプション》を設けた。
《使用例》
? DLookup("SELECT 書名 FROM [蔵書一覧$A1:G100]
WHERE 出版社='文芸春秋' AND 著者名 LIKE '石黒%'")
１－２、DSelect()
Public Function DSelect(ByVal strSQL As String, _
Optional colDelimita As String = ";", _
Optional rowDelimita As String = "|", _
Optional xlFileName As String = "", _
Optional isHeader As Boolean = True, _
Optional withFieldInfo As Boolean = False) As String
‥‥
End Function
第1引数: SQL文。
第2引数: 列データの区切り子。
第3引数: 行データの区切り子。
第4引数: 対象のブックを指定する。
指定されてないと ThisWorkbook.FullName を採用。
第5引数: ヘッダーの有無。
第6引数: 戻り値の先頭 にField 情報を示す行を発生させるか否か。
《使用例》
? DSelect("SELECT * FROM [Sheet2$B1:D3]")
1;wwww;12|2;wwww;12
? DSelect("SELECT * FROM [Sheet2$B1:L3]",,Chr(13))
1;1;wwww;12;6.1;10;20.1;2019/02/11;22:30:00;6.52;6.5;16
2;2;wwww;12;6.1;10;20.1;2019/02/12;23:30:00;7.52;7.5;17
第6引数に True を指定すると、Field.Name、Field.Type を示す2行を発生させる。これは、一般ユーザーにとってはほとんど意味がない。しかし、プログラマー並みのスキルを有するユーザーにとっては大いに意味がある。
? DSelect("SELECT * FROM [Sheet2$B1:L3]",,Chr(13),,,True)
ID;文字列;数字１;数字2;通貨1;通貨2;日付;時刻;パーセント;分数;指数
5;202;5;5;6;6;7;7;5;5;5
1;1;wwww;12;6.1;10;20.1;2019/02/11;22:30:00;6.52;6.5;16
2;2;wwww;12;6.1;10;20.1;2019/02/12;23:30:00;7.52;7.5;17
１－３、SQLExecute()
Public Function SQLExecute(ByVal strSQL As String, _
Optional xlFileName As String = "", _
Optional isHeader As Boolean = True) As Boolean
‥‥
End Function
第1引数: SQL文。
第2引数: 対象のブックを指定する。
指定されてないと ThisWorkbook.FullName を採用。
第3引数: ヘッダーの有無。
《使用例》
? SQLExecute("DELETE FROM [Sheet2$B1:L10] WHERE ID< 20”)
True
? SQLExecute("UPDATE [蔵書一覧$A1:F100]
SET 廃棄日='2018/12/13' WHERE ID=1")
True
? CnnExecute("INSERT INTO 蔵書一覧
VALUES (6,'紀州犬',Null, '光文社','甲斐崎 圭', '2010/09/10')")
True
１－4、SQLWriter()
Public Function SQLWriter(ByVal strSQL As String, _
ByVal strSheetName As String, _
Optional xlFileName As String = "", _
Optional isHeader As Boolean = True, _
Optional withFieldInfo As Boolean = True) As Boolean
‥‥
End Function
第1引数: SQL文。
第2引数: SQL文の結果を書き込むシート名。
第3引数: 対象のブックを指定する。
指定されてないと ThisWorkbook.FullName を採用。
第4引数: ヘッダーの有無。
第5引数: 戻り値の先頭 にField 情報を示す行を発生させるか否か。
SQLWriterがあれば、PIVOT TABLE を作成する必要はない！
１、選択クエリー
２、クロス集計クエリ―
３、重複クエリー
４、不一致クエリー
先にも述べたが、この4つのクエリー結果に基づいて作表するツールが SQLWriter関数である。SQLWriter関数は、次のようなSQL文を含む４つのクエリー実行することができる。
TRANSFORM Max(蔵書一覧.書名) AS 書名の最大
SELECT 蔵書一覧.出版社
FROM 蔵書一覧
GROUP BY 蔵書一覧.出版社
PIVOT "第 " & Format([購入日],"q") & " 四半期";
SQLWriter関数は、Acess のクエリーィザードと同じ機能を使う機会をエクセルユーザーに提供する。
《使用例》
? SQLWriter("TRANSFORM Max(書名) AS 書名の最大
SELECT 出版社 FROM [蔵書一覧$A1:Z100]
GROUP BY 出版社
PIVOT '第 ' & Format([購入日],'q') & ' 四半期'", "New")
True
SQLWriter関数は、書き込むシート名が"New"であれば新しいシートを挿入して書き込む。
XferLitearal() とGetFieldName()は、ツールとしての紹介は割愛する。
１－５、SQLUpdate()
たった、今、思い付いたんだが、
？ SQLUpdate(SQL文、cellプロパティ名、セットする値)
OK
て、ツールはあってもいいんじゃーないかな。SQL文で検索するだけが能じゃないだろう。SQL文で検索したセルを自在に変更するってのもありじゃーないかな。たしかに、UPDATE文で変更することも出来る。しかし、それはデータ（値）だけ。値も含めて前景色、背景、式などなど、なんでも変えますってツールがあっても・・・。ということで、SQLUpdate もツールの仲間入り。
2、Access のテーブルを参照、更新、削除するツール
Aceess のテーブルを参照、更新、削除するツールについては、エクセルと酷似したそれである。よって、その詳細の説明は省く。
２－１、DLookup()
２－２、DSelect()
２ー３、CnnExecute()
２－４、TableWriter()
２－５、XferLitearal()
さあ、大変だ。SQLツールの仕様を清書する過程で、ごくごく小さな修正が発生した。２、30分で終わる修正だが、なんせ腰が重い性格。こうして、ブログでも書いて自分を追い込まないと一向に作業に着手しない。本稿の続きを書く上では、その修正は不可欠。果たして・・・。で、後は、明日の心。
