このデータでINSERT(UPDATE)しといて!とエクセルを渡される事が個人的に多々あります。
そんな時に毎度ちまちま文字列くっ付けてSQL作るのが面倒だったので解消するマクロを作ってみた
やりたい事
例えばこんな社員テーブルがあったとして、
名前 | 状態 | 役職 | ID |
本田 | 在籍 | チーフ | 1 |
茂野 | 在籍 | 正社員 | 2 |
佐藤 | 退職 | – | 3 |
小森 | 在籍 | 正社員 | 4 |
この内容に更新しといて~とこんなエクセルが飛んでくる↓
こんな時いつもE1に=”UPDATE テーブル SET ” & $A$1 & ” = “・・・ みたいな感じでちまちま作ってオートフィルしてた訳です。
カラムがこの程度なら大した事無いですが、実際のテーブルはもっととんでもなく多い。しかも日付の時にはシリアル値になっちゃうからTEXTで変換しなきゃ~等で超面倒。
なのでこいつをサクッとやっつけようってのが今回のマクロ。ちなみにMySQL用です。
MakeUpdateSQL
コード実態↓コピペでそのまま使えます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
Sub MakeUpdateSQL() ' Table名を入力させる Dim strTableName As String strTableName = InputBox("テーブル名を入力してください") ' 空またはキャンセルの場合 If strTableName = "" Then Exit Sub End If ' 選択範囲値格納 Dim arr As Variant arr = Selection.Value ' テキスト読み込み(無ければ作成) Dim strPath As String Dim wsh As Object Set wsh = CreateObject("WScript.Shell") strPath = wsh.SpecialFolders("Desktop") & "\UpdateSQL.txt" Set wsh = Nothing ' テキストオープン Open strPath For Output As #1 ' カウンタ Dim nRowCnt As Long Dim nColCnt As Long ' 1列目はカラム名称なので2列目から始める For nRowCnt = 2 To UBound(arr, 1) For nColCnt = 1 To UBound(arr, 2) Select Case nColCnt ' 初回行 Case 1 Print #1, "UPDATE "; Print #1, strTableName; Print #1, " SET "; Print #1, arr(1, nColCnt); Print #1, " = '"; Print #1, Trim(arr(nRowCnt, nColCnt)); Print #1, "',"; ' 最終更新値行 Case UBound(arr, 2) - 1 Print #1, arr(1, nColCnt); Print #1, " = '"; Print #1, Trim(arr(nRowCnt, nColCnt)); Print #1, "' "; ' 最終行(WHERE句) Case UBound(arr, 2) Print #1, "WHERE "; Print #1, arr(1, nColCnt); Print #1, " = '"; Print #1, Trim(arr(nRowCnt, nColCnt)); Print #1, "';" ' 更新値行 Case Else Print #1, arr(1, nColCnt); Print #1, " = '"; Print #1, Trim(arr(nRowCnt, nColCnt)); Print #1, "',"; End Select Next Next ' ファイルクローズ Close #1 ' 作成したテキストを開く CreateObject("Shell.Application").ShellExecute strPath End Sub |
単純に文字列を&(アンパサンド)して結合させるとフリーズするのでテキスト作成して直書き方式にしました。毎度値にTrim掛けてるのは数値型だと前後にスペースが出来てしまう為それの削除用。
ルールとして最後の行はWHERE句にしてるのと、日付カラムに対して空白UPDATEするとSQL投げた際にエラーになるので、予め1900/01/01等のそのカラムの初期値で埋めとく事。
実際の動き
以前上記記事で作成した自分用のメニューの中に突っ込んで使って見た
INSERTしたい場合でもループの中身ちょっと変えるだけで応用効くはずなので、是非活用してみて下さい~(ᵔᴥᵔ)
コメント