EXCEL＋VBA＋SQL（ADO） 外部DBなど使わず、全てをエクセル内で完結させる
2回に渡り記事をUPしたが、まだ完全に纏められるほど頭が固まっていないが、最終回。
現時点での纏めです。※コードは後半
（免責）
・下記はコード作成中に発見したモノで、コードを作った後で改めて検証は行っていないため、不正確な解説が行われている可能性あるが、不正確な点あればスルーの事。
・MSにより随時更新されているEXCELのパッチにて問題が解決する場合もあるが、新たな別の問題が発生する場合もある（現在日：2015/8）。
※MSのOffice系パッチはとてもいい加減で、ある日突然マクロ（VBA）がエラーで動かなくなる（仕様が真逆に変更になっていた）等、過去にMSにしてやられたこと数回＝私的には業務PGMでexcelなど決して使いたくない
「 EXCEL エクセル＋VBAのコツ 」
「エクセルの要素（cells、sheetなど）は、本処理では、絶対に！決して！直参照しない」
※処理件数が少なければシート直接参照OK!! 問題ない。
理由：処理が数百～数万倍遅くなる
2015年8月現在はBOOKバグも発生するのでSQL使用時は必須と考えた方が良い
・最初に、シートにあるデータは、VBAで配列（若しくは変数）を作りシートを丸ごと入れ、
その配列や変数のみを使って処理を行う
（シートを参照しない＝処理速度が異様に高速になる）
・最後に配列や変数の値を、結果表示するEXCELシートへ貼り付ける
・・処理流れ・・
「前処理」 VBAからI/Oするシートやセルのデータは配列や変数を作って入れる
「本処理」 絶対に！！！sheet、cell、等エクセルシート内の要素を直参照・直設定しない。
配列や変数のみを使い処理ロジックを書く。
「後処理」 処理結果をEXCELシートへ一括貼付する（range）
これらは一般的に、VBA高速化テクニック、として公開されているが、私的には必須と思っている。
出来ないならコード書くな！と言いたい。
参考ページ → とても分かりやすい解説
「 EXCEL エクセル＋VBA+ADO＋SQL （シート同士をjoinしたクエリ） のコツ 」
「シート上で数字に見える列は、事前に全て ' を付けEXCELに文字列と宣言する」
「 教訓 」
VBAでは問題ないが、SQL（ADO）では問題になる事がある（永遠にマッチしない事態に陥る可能性ある）
多分今回発生した一連の問題のキモはこれでした。
ADOに渡ったデータの値が変わってしまうなど思いもしなかったですから。
－－－－－－－－－－－－
まず解説）
－－－－－－－－－－－－
EXCELセルの中身 A1：1234 A2：0123 A3：'0123
EXCELでの見た目 A1：1234 A2：0123 A3：0123
A2はセルの書式設定で「文字列」と指定してある
－－－－－－
VBAにて
－－－－－－
Sub main()
Dim wsMain As Worksheet
Set wsMain = Worksheets("Sheet1")
MsgBox (wsMain.Range("A1").Value)
MsgBox (wsMain.Range("A2").Value)
MsgBox (wsMain.Range("A3").Value)
End Sub
実行すると・・・次のよう表示される
1234
0123
0123
VBA上ではEXCELの数値の仕様とセルの書式設定が効いている。
だから2番目と3番目が「0123」と表示される
－－－－－－
しかしADOでは！！
－－－－－－
EXCELの数値仕様 ： 適用
セルの書式設定 ： 無視
データは其々次のよう読み込まれていた
1234
123
'0123
VBAで表現されるデータがそのままADOに渡ると思っていたため、永遠にjoinしない状況が発生！
※実際にADOに渡されたデータを見ることは不可能。（今回使ったドライバーではこうなった）
－－－－－－
回避策は・・
－－－－－－
EXCELの数値列の全セルの頭に、予め ' を付加し、ADOでは「'0123」と言うデータでSQLした。
今回のSQLに使ったドライバ
Microsoft ActiveX Data Objects 6.1 Library
Provider：Microsoft.ACE.OLEDB.12.0
他のドライバ、またバージョンによって動きが異なること、大いにあり得る
＜ コツ（まとめ） ＞
・ADO（VBAのADO）では、エクセルで設定したセルの書式設定は無視される
上記より
・数字に関してエクセルには数々の仕様が存在し、事前に仕様が適用されたのち、クエリ（ADO）へ渡される
仕様回避は不可能 上記と記事（2）を参照
・ADOを使用するなら数字列はEXCEL上で事前に ' を付加しておく
処理結果をシートへ結果を貼り付ける時も、EXCELの数値仕様が適用され問題となることがあるので ' を付けると全て上手く行く
・SQLのjoinキーは、エクセルでの全ての数字の制約（仕様）を考慮しないと永遠にマッチしない事態に陥る
今回大問題になった事件（記事（2）を参照）
・対策としてEXCEL数値列に ’ を付加するには
１．別シートを用意し当該シートのセルを其々参照させ、その別シートをVBAに渡しクエリで使用する（簡単）
２．CVSなどをラッパーとし利用I/Oし、書きor読込時に ' を付加し、新シートへ書き込むVBAを作る（面倒）
上記数字列全てに ' を付けてしまう手法は、joinキーに数字を用いない場合もエクセルの各種数字に対する制約がキツイので、なら数字列は予め全て ' 付けてしまえ！！的な手法。 但し美しくないので、机上のプロやＩＴ記者（私はマスタべーションのプロと呼んでいる）は嫌がると思うが、なら現場入ってみろ！と私ならそう言います。
批判されること、あるかも知れない現場的な手法ですが、開発もメンテも楽になりますよ！
今回作ったコードの一部を記します。ご参考まで。
※これがウォータープルーフ開発で用いられていた上から下へ流れるコード一例、でもある
（オブジェクト指向でない ＝ 今どきこんなコードを書いたら張り倒されるかクビになるかも？）
※VBAは、VBA＜VB6 要するにVBAは20年前の代物＝学習教材としては最悪です
java(JavaScriptではない)、PHP、C#を勉強しましょう
※解説はしませんので質問もしないでください、少々雑でいい加減な個所もありますが動きます
※不明なパラメータなど、ググれば腐るほど解説ページが出てきますので、そちらを参照のこと
※エラー処理は外しているので、On Error GoTo、して下さい
※下記コードの著作権は私にあるので好き使っていただいて結構です （但し、使えるものなら！）
'-----------------------------------------
' サンプルコード START
'
' Windows10（64bit）、Excel2013（64bit）、拡張子～.xlsm、にて稼働中
'-----------------------------------------
Sub CollectAccountDue()
'出力する未収金反映済の請求シート名
Dim OutSheetName As String
OutSheetName = "当月請求"
'SQL文
Dim SQLSheet As String
Dim SQLCell As String
SQLSheet = "tempSQL"
SQLCell = "A2"
'ワークシート
Dim wsMain As Worksheet
Dim wsSql As Worksheet
Dim wsOut As Worksheet
Set wsMain = Worksheets("メイン") 'メイン画面
Set wsSql = Worksheets(SQLSheet) '当該セルよりSQL読込
Set wsOut = Worksheets(OutSheetName) '出力先シート名
'ツール → 参照設定 → Microsoft ActiveX Data Objects 6.1 Library
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
'出力STARTセル情報
Dim oColumn As Long '出力カラム
Dim oRow As Long '出力行
oColumn = 1
oRow = 1
Application.ScreenUpdating = False
'出力先シートをクリア
wsOut.Cells.Clear
'ADO
Set cn = New ADODB.Connection
cn.Provider = "MSDASQL"
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=YES;"""
sql = wsSql.Range(SQLCell).Value '当該セルよりSQL文を読込
cn.Open
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenStatic
If rs.Fields.Count < 1 Then
MsgBox ("エラー発生！！該当者がマスター上に存在しません。処理を終了します")
Exit Sub
End If
'（未使用）新シート作成（現在のシートの最後に追加）
'（未使用）Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = Range(NewSheetNameCell).Value
'---------------------------------------
'--- Query Data -> New Sheet ---
'---------------------------------------
Dim rsFieldsCount As Long
rsFieldsCount = rs.Fields.Count
'出力
Dim rowArray(1 To 3000, 1 To 100)
Dim colCount As Long
Dim rowCount As Long
colCount = 0
rowCount = 1
'未収金、請求額算出
Dim Claim As Long
Dim Claim1 As Long
Dim Claim2 As Long
Dim Claim3 As Long
'========================
Dim dblStart As Double
Dim dblEnd As Double
Dim dbltime As Double
dblStart = Timer
Debug.Print dblStart
'========================
'クエリ結果を配列へ挿入
' EXCELバグが発生しなければ配列へ挿入する倍の時間で直接シートへ挿入可能
' だがEXCELのBook内バグが発生しだすと300倍の時間が掛かり回避不可能
' 従って回避策として配列へ挿入する
colCount = rsFieldsCount + 3 '未払金、小計、請求額を付加
Do Until rs.EOF
'ヘッダー行セット（1行目）
If rowCount = 1 Then
For i = 1 To rsFieldsCount
rowArray(1, i) = rs(i - 1).Name
Next
rowArray(1, 45) = "未払額"
rowArray(1, 46) = "小計"
rowArray(1, 47) = "請求額"
rowCount = rowCount + 1
End If
'データ行セット（2行目以降）
If rs(2).Value <> "'" Then
'col
For i = 1 To rsFieldsCount
If IsNull(rs(i - 1).Value) = False Then
rowArray(rowCount, i) = rs(i - 1).Value
Else
rowArray(rowCount, i) = ""
End If
Next
'========================================
' 未払い金、小計、請求額を付加
'========================================
'請求金額(32)
If (rowArray(rowCount, 32) = "") Then
Claim1 = 0
Else
Claim1 = rowArray(rowCount, 32)
End If
'当月合計(20)
If (rowArray(rowCount, 20) = "") Then
Claim2 = 0
Else
Claim2 = rowArray(rowCount, 20)
End If
'現金入金(21)
If (rowArray(rowCount, 21) = "") Then
Claim3 = 0
Else
Claim3 = rowArray(rowCount, 21)
End If
'** 未払額 ** ** 小計 **
If (rowArray(rowCount, 37) = 0) Then
rowArray(rowCount, 45) = Claim1
Claim = Claim1 + Claim2 + Claim3
Else
rowArray(rowCount, 45) = 0
Claim = Claim2 + Claim3
End If
rowArray(rowCount, 46) = Claim
'** 請求額算出 **
If (Claim ＜= 0) Then
rowArray(rowCount, 47) = 0
Else
rowArray(rowCount, 47) = Claim
End If
'行カウント
rowCount = rowCount + 1
Else
Exit Do
End If
rs.MoveNext
Loop
Dim rowCnt
rowCnt = UBound(rowArray, 1) '1368
'======================
'出力先シート ← 配列
'======================
'1Startを0Startにする（正常に戻す）
rowCount = rowCount - 1
'-1を行った後のrowCountは、1行目のヘッダーを含めた件数なので注意！！！
wsOut.Select 'アクティブにしておかないと「rangeオブジェクトは失敗しました 1004 」が発生する
wsOut.Range(Cells(1, 1), Cells(rowCount, colCount)) = rowArray ’配列→シート：オーバーフローしているが問題ない（切り捨てられる）
'========================
dblEnd = Timer
Debug.Print dblEnd
dbltime = dblEnd - dblStart
Debug.Print "処理時間は" & Format$(Int(dbltime)) ＆ "秒でした"
Debug.Print "処理時間は" & Format$(Int(dbltime * 10 ^ 4 + 0.5) / 10 ^ 4) ＆ "秒でした"
'========================
rs.Close
cn.Close
'============================
'口振シート作成（CSV化の元データ）
'============================
Dim rowBankArray()
ReDim rowBankArray(1 To rowCount - 1, 1 To colCount) '1行目はヘッダー行なので不要、-1する）
Call CreateBankTransferData(rowArray(), rowBankArray())
'================================
'コンビニシート作成（CSV化の元データ）
'================================
Dim rowCvsArray()
ReDim rowCvsArray(1 To rowCount - 1, 1 To 80) '1行目はヘッダー行なので不要、-1する）
Call CreateCVSTransferData(rowArray(), rowCvsArray())
Application.ScreenUpdating = True
'メインシートをアクティブにする
wsMain.Select
Dim ret
ret = MsgBox("処理は正常に終了しました", vbOKOnly, "当月請求データ作成")
End Sub
'-----------------------------------------
' サンプルコード END
'-----------------------------------------
※↑ブログUP時、特殊文字が自動変換されてしまうので大文字にしている箇所があります
※仕様としては、顧客マスタと未払金CSVをSQLでマッチングさせ、通常請求額＋未払い額を算出し、当月請求額としてFIXさせている。
一応クエリも貼っておきます
select * from [請求データ$] a left outer join [CSV1$] b ON mid(a.顧客ID,2,5) = mid(b.請求番号,12,5)
・SQLはこんな感じにセルに記入
・VBAでシート「SQL」の「A2」を読み込み、SQL文として使用する
※データは機密のため見せられませんが、EXCELで管理している「顧客情報」と「請求情報」なので大したことありませんから特に記述しません。
参考までに 第1回記事 で解説した、手作業で行っていたEXCELを使った請求業務。
上記EXCEL＋SQLを使うことで、パートさんが行っていた毎月7人日必要としていた請求処理は、わずか8秒で完了できるようになった。
開発費用はたった半年で回収でき、あとはボロ儲け？めでたしめでたし。
（ まとめ 余談 オチ ）
3回に渡り、EXCELのシート同士を其々テーブルと見立て、とあるシートのセルに書いたSQL文をVBAで読み取り、ADOを使いそのSQLを実行、結果を新しいシートへ貼り付ける。と言うVBAコードと解説？意見？日記？を長々書いてきましたが、最後に余談とオチ。
私はEXCELには詳しくない普通の職業プログラマーだったため、基本EXCEL関数は使いません。と言うより信用していない、私には必要ない、と言った方が正しい。
やはり速度が非常に遅い点、応用が利かない点、MSが予告なく仕様を勝手に変更する点、EXCELやVBA特有の関数を覚えたところでEXCEL以外では使えない点＝それでは金にならない＝食っていけない、からですね。（VB6やっていたので対した苦も無くVBAでコード書けますが・・）
職業プログラマーはプログラミングや設計で生計を立てるので、いかに将来性があり（市場シェア）効率よく金になるモノに注力し取り組めるか（学習勉強）、これが注力対象を選択する一番のポイント。しかも腐るほど金を持っている巨大企業が好む言語等を選択するしかない。中小企業（金がない＝単金も安く金払いも悪い）が好む事が多いEXCEL VBAの市場シェアは低く（無に等しい）覚えたところで永年に渡り食っていけません（寝る間を惜しんで死ぬほど学習しているなら別！）。それならjava、.net、c#、各種web系言語、開発、運用ノウハウ、コンサル等に注力した方がよっぽど良い。と言うよりそれ以外の選択肢はない。数十年に渡り家族食わしていかないとでしょ？これがEXCEL・VBAが私に必要ない理由であり覚えない理由。ノウハウは今回の記事だけで十分、もうイラナイ。
そして最後にオチ
今回の、シートAとBをマッチングし、結合結果をシートCへ書く、と言う処理は、EXCEL VBA関数である「Find」を使い応用すれば出来ます。
「VBA シート同士の結合」とググれば例は山ほど出てきます。コード量も大変少なく、、さて、あなたならどちらの手段を選択しますか？
SQLを使い処理件数が増えても速度も速く汎用性を持たせるが面倒なロジック、FINDを使い短いコードで処理をこなす。
FINDを使った処理群、速度に関してはどうなのでしょう？試してないのでわかりませんのでご自身でお試しください。
私はFINDもですがエクセル提供の関数など信用しないので基本EXCELは使いませんが（EXCEL関数群があそこまで処理が強烈に遅くハングする等のバグも多いとはイマドキ夢にも思いませんでした）、数百件程度の少量データならFINDを使う事を大いに検討するかな？と考えます。
おわり
パート１はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする（第１回）
パート２はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする（第２回）
0 件のコメント:
コメントを投稿