Excel上でSQLを生成

Sub ボタン1_Click()

    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim tableName As String
    Dim sql As String
    Dim columns As String
    Dim values As String
    Dim colType As String
    Dim v As Variant
    
    Set ws = ActiveSheet
    
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.columns.Count).End(xlToLeft).Column
    
    ' ★ テーブル名は A1 を使用
    tableName = ws.Cells(1, 1).Value
    
    ' ★ カラム名（1行目）
    columns = ""
    For c = 2 To lastCol
        columns = columns & ws.Cells(1, c).Value & ","
    Next c
    columns = Left(columns, Len(columns) - 1)
    
    ' ★ データは 3 行目以降
    For r = 3 To lastRow
        
        values = ""
        
        For c = 2 To lastCol
            
            colType = LCase(ws.Cells(2, c).Value) ' 型（2行目）
            v = ws.Cells(r, c).Value              ' データ
            
            If Trim(v) = "" Then
                values = values & "NULL,"
            Else
                
                ' 型の括弧を除去して基本型を判定（例：decimal(10,2) → decimal）
                Dim baseType As String
                baseType = Split(colType, "(")(0)
                
                Select Case baseType
                    
                    ' 数値型（クォートなし）
                    Case "int", "bigint", "smallint", "tinyint", _
                         "decimal", "numeric", "float", "real", _
                         "money", "smallmoney"
                        values = values & v & ","
                    
                    ' 日付型（クォートあり）
                    Case "date", "datetime", "datetime2", _
                         "smalldatetime", "datetimeoffset"
                        values = values & "'" & Format(v, "yyyy-mm-dd HH:mm:ss") & "',"
                    
                    ' bit 型（0/1）
                    Case "bit"
                        If v = True Or v = 1 Then
                            values = values & "1,"
                        Else
                            values = values & "0,"
                        End If
                    
                    ' GUID（uniqueidentifier）
                    Case "uniqueidentifier"
                        v = Replace(v, "'", "''")
                        values = values & "'" & v & "',"
                    
                    ' 文字列型（クォート＋エスケープ）
                    Case "varchar", "nvarchar", "char", "nchar", "text", "ntext"
                        v = Replace(v, "'", "''")
                        values = values & "'" & v & "',"
                    
                    ' その他は文字列扱い
                    Case Else
                        v = Replace(v, "'", "''")
                        values = values & "'" & v & "',"
                End Select
            End If
            
        Next c
        
        values = Left(values, Len(values) - 1)
        
        sql = "INSERT INTO " & tableName & " (" & columns & ") VALUES (" & values & ");"
        
        ws.Cells(r, lastCol + 1).Value = sql
        
    Next r
    
    MsgBox "SQL Server 用 INSERT文の生成が完了しました"

End Sub