エクセルのデータからRDB用のSQLを作成するマクロです。シート名をテーブル名、1行目をテーブルのフィールド名として処理します。
作成されるSQL
INSERT INTO bookmark (ID,URL,TITLE) values (0,'http://www.google.co.jp/','GOOGLE'); INSERT INTO bookmark (ID,URL,TITLE) values (1,'http://www.yahoo.co.jp/','Yahoo! JAPAN');
マクロ
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