Sub GenerateInsertQuery()
Dim strTblName, strSqlTemplate, strColValue, strForReplace As String
Dim nStCol, nStRow, nEndCol, nEndRow, nDataStRow As Integer
Dim rngTmp As Range
Dim varColName, varType, varColValue As Variant
'
If 1 = 3 Then
' Set tgtRange = Application.Selection
' If tgtRange = Nothing Then
MsgBox ("セルが選択されていません！")
' Exit Sub
Else
' 範囲確認用
nStCol = Application.ActiveCell.Column
nStRow = Application.ActiveCell.Row
nDataStRow = nStRow + 4
Set tmpRange = ActiveSheet.Cells(nStRow + 1, nStCol).End(xlToRight)
nEndCol = tmpRange.Column
Set tmpRange = ActiveSheet.Cells(nStRow, nStCol).End(xlDown)
nEndRow = tmpRange.Row
strTblName = Cells(nStRow, nStCol + 1).Value
' 範囲確認用
' ActiveSheet.Range(Cells(nStRow + 1, nStCol), Cells(nEndRow, nEndCol)).Select
If (Cells(nStRow, nStCol).Value <> "テーブル名") Then
MsgBox ("セルの選択位置が不正です！")
GoTo Skip
End If
End If
' Application.ActiveCell.Column
' 情報行の情報取得
ReDim varColName(nEndCol)
ReDim varType(nEndCol)
For colNum = nStCol To nEndCol
varColName(colNum) = Cells(nStRow + 1, colNum).Value
varType(colNum) = Cells(nStRow + 2, colNum).Value
Next
' データ行先頭からの処理
For rowNum = nDataStRow To nEndRow
strForReplace = ""
ReDim varColValue(nEndCol)
' 先頭列からの処理
For colNum = nStCol To nEndCol
' NULLの場合
If Cells(rowNum, colNum).Value = "" _
Or Cells(rowNum, colNum).Value = "NULL" _
Then
strColValue = "NULL"
' 数値型の場合
ElseIf varType(colNum) = "int" Or _
varType(colNum) = "bigint" Or _
varType(colNum) = "numeric" Or _
varType(colNum) = "decimal" _
Then
strColValue = Cells(rowNum, colNum).Value
' bit型の場合
ElseIf (varType(colNum) = "bit") Then
strColValue = Cells(rowNum, colNum).Value
If (strColValue = "0") Then
strColValue = "FALSE"
ElseIf (strColValue = "1") Then
strColValue = "TRUE"
End If
Else
' 数値以外
strColValue = "'" & Cells(rowNum, colNum).Value & "'"
End If
' 設定値を格納
varColValue(colNum) = strColValue
Next
' VALUE句を生成
For colNum = nStCol To nEndCol
' 先頭列
If (colNum = nStCol) Then
strForReplace = varColValue(colNum)
Else
strForReplace = strForReplace & "," & varColValue(colNum)
End If
Next
' 生成したSQLを最終列＋１列目へ設置
Cells(rowNum, nEndCol + 1).Value = "INSERT INTO " & strTblName & " VALUES(" & strForReplace & ");"
Next
Skip:
End Sub
