UPDATE文作成マクロ

MakeUpdate

Option Explicit

Sub MakeUPDATE()

'SQLシートをクリア
SQL.Cells.Clear

Dim sqlstring1 As String
'テーブル名を取得
sqlstring1 = "UPDATE " + DATA.Range("B3") + " SET "

'表の範囲を取得
Dim maxcol As Integer
Dim maxrow As Integer

maxcol = Range("B6").End(xlToRight).Column
maxrow = Range("B6").End(xlDown).row

'1カラムだけだと16384が返ってくるので2を無理やり代入
If maxcol = 16384 Then
  maxcol = 2
End If

'UPDATE文の生成
Dim colindex As Integer
Dim rowindex As Integer
Dim sqlstring2 As String
Dim wherestring As String

For rowindex = 7 To maxrow

'  Where句の値を先に取得
  wherestring = " WHERE " & DATA.Cells(6, 2).Value & " = " & makeSetValue(DATA.Cells(7, 2)) & ";"
  For colindex = 3 To maxcol
  
    sqlstring2 = sqlstring2 & DATA.Cells(6, colindex) & " = " & makeSetValue(DATA.Cells(rowindex, colindex))
    
    If colindex <> maxcol Then
        sqlstring2 = sqlstring2 & ", "
    Else
    ' カラム定義の最後尾の処理
        Dim sqlstring As String
        sqlstring = sqlstring1 & sqlstring2 & wherestring
        SQL.Cells(rowindex, 1) = sqlstring
        
        sqlstring = ""
        sqlstring2 = ""
        
    End If
  Next
  
Next

End Sub

'セルの内容を判定して空ならNULL文字列を返す
'文字列型なら''で囲んで返す
'それ以外は単なるstringにして返す
Function makeSetValue(targetCell As Range)
    Dim valuestring As String
    
    If IsEmpty(targetCell.Value) = True Then
      valuestring = "NULL"
    ElseIf TypeName(targetCell.Value) = "String" Then
      valuestring = "'" + targetCell.Value + "'"
    Else
      valuestring = CStr(targetCell.Value)
    End If
    
    makeSetValue = valuestring
End Function