【VBA】セルの内容を一括でSQL文に変換するマクロ

このデータでINSERT(UPDATE)しといて！とエクセルを渡される事が個人的に多々あります。
そんな時に毎度ちまちま文字列くっ付けてSQL作るのが面倒だったので解消するマクロを作ってみた

やりたい事
MakeUpdateSQL
実際の動き

やりたい事

例えばこんな社員テーブルがあったとして、

名前	状態	役職	ID
本田	在籍	チーフ	1
茂野	在籍	正社員	2
佐藤	退職	–	3
小森	在籍	正社員	4

この内容に更新しといて～とこんなエクセルが飛んでくる↓

こんな時いつもE1に=”UPDATE テーブル SET ” & $A$1 & ” = “・・・　みたいな感じでちまちま作ってオートフィルしてた訳です。

カラムがこの程度なら大した事無いですが、実際のテーブルはもっととんでもなく多い。しかも日付の時にはシリアル値になっちゃうからTEXTで変換しなきゃ～等で超面倒。

なのでこいつをサクッとやっつけようってのが今回のマクロ。ちなみにMySQL用です。

MakeUpdateSQL

コード実態↓コピペでそのまま使えます。

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

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

' ファイルクローズ

Close #1

' 作成したテキストを開く

CreateObject("Shell.Application").ShellExecute strPath

End Sub

単純に文字列を＆(アンパサンド)して結合させるとフリーズするのでテキスト作成して直書き方式にしました。毎度値にTrim掛けてるのは数値型だと前後にスペースが出来てしまう為それの削除用。

ルールとして最後の行はWHERE句にしてるのと、日付カラムに対して空白UPDATEするとSQL投げた際にエラーになるので、予め1900/01/01等のそのカラムの初期値で埋めとく事。

実際の動き

以前上記記事で作成した自分用のメニューの中に突っ込んで使って見た

INSERTしたい場合でもループの中身ちょっと変えるだけで応用効くはずなので、是非活用してみて下さい～(ᵔᴥᵔ)