Excel > TIPS


※上記の広告は60日以上更新のないWIKIに表示されています。更新することで広告が下部へ移動します。



■使用例
以下の要領でシートにデータを記述します。
  • 1行目に列名
  • 2行目に列の型
  • 3行目以降は列の値
  • 最後の次の行の1列目のセルに、エンドマーク『#END』
上記の要領で書き上げたシートが下図です。(クリックで拡大)



   Option Explicit
   
   Sub createInsertSql()
       Dim newbook As Workbook
       Dim currentCell As Range
       
       '前処理
       Dim srcSheet As Worksheet
       Set srcSheet = ActiveSheet
       
       Dim targetRange As Range
       Set targetRange = srcSheet.UsedRange
       'INSERT文の前半
       Dim head As String
       head = "INSERT INTO " & srcSheet.Name & " ("
       
       Dim first As Boolean
       first = True
       
       Dim currentColumnIndex As Integer
       For currentColumnIndex = 1 To targetRange.Columns.Count
           If (first) Then
               first = False
           Else
               head = head & ","
           End If
           Set currentCell = srcSheet.Cells(1, currentColumnIndex)
           head = head & currentCell.Value
       Next
       head = head & ") "
       
       '新しいBook作成
       Set newbook = Workbooks.Add
       
       'INSERT文のvalues以降
       Dim currentRowIndex As Integer
       For currentRowIndex = 2 To targetRange.Rows.Count
           
           Dim sql As String
           sql = head & "values ("
           first = True
       
           For currentColumnIndex = 1 To targetRange.Columns.Count
               If (first) Then
                   first = False
               Else
                   sql = sql & ","
               End If
               Set currentCell = srcSheet.Cells(currentRowIndex, currentColumnIndex)
               If IsNull(currentCell) Or Trim(currentCell.Value) = "" Then
                   sql = sql & "null"
               ElseIf IsNumeric(currentCell.Value) Then
                   sql = sql & currentCell.Value
               Else
                   sql = sql & "'" & currentCell.Value & "'"
               End If
           Next
           
           sql = sql & ");"
           
           newbook.ActiveSheet.Cells(currentRowIndex - 1, 1).Value = sql
       Next
   End Sub





EOF
添付ファイル