Blog Pro de Jean-Baptiste HEREN

Sub generateSQLInsert()
  'Loop on current scheet and generate sql INSERT based on column names as columns an sheet name as table
  ' Will create one statement per line aka  INSERT INTO mytable(col1,col2,...) VALUES(val1,val2,...)
  ' Author: Jean-Baptiste Heren - 29-06-2010 - http://blog.jbheren.com
 
  Dim tableName As String 'tablename extracted from first row of each column
  Dim filePath As String
  Dim slashPosition As Integer
  Dim pathOnly As String
 
  Dim columnsSQL As String ' will contain the insert into table (col1,col2,...) statement
  Dim dataSQL As String    ' will contain the values (val1,val2,...) statement
  Dim separator As String
 
  Dim myRow As Integer 'Row counter
  Dim myCol As Integer 'Column counter
 
  'set Tablename from sheet name
  tableName = ActiveSheet.Name
 
  ' get current excel file Path
  filePath = ThisWorkbook.FullName
  slashPosition = InStrRev(filePath, "\")
  pathOnly = Left(filePath, slashPosition)
 
  MyFile = pathOnly + tableName + ".sql"
 
  'get column names from first row
  myColInput = InputBox("What column to START on ?")
  myCol = myColInput
  myRow = 1
  columnsSQL = "INSERT INTO " + tableName + " ("
  separator = " "
 
  Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop until you find a blank.
    
    columnsSQL = columnsSQL + separator + Trim(ActiveSheet.Cells(myRow, myCol))
    separator = ", "
    
    myCol = myCol + 1 ' Move to next column
  Loop
  columnsSQL = columnsSQL + ") "
 
  'get column values from second row
  myRow = 2
  myCol = myColInput
 
  'set and open file for output
  fnum = FreeFile()
  Open MyFile For Output As fnum
 
 
  Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on rows until blank.
    
    dataSQL = "VALUES ("
    separator = " "
    
      Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on columns until blank.
        dataSQL = dataSQL + separator + "'" + Trim(CStr(ActiveSheet.Cells(myRow, myCol))) + "'"
        separator = ", "
        myCol = myCol + 1
      Loop
    
    dataSQL = dataSQL + ");"
    
    myCol = myColInput ' Return to specifiedcolumn
    myRow = myRow + 1 ' Move to next row
    
    Print #fnum, columnsSQL + dataSQL
  Loop
 
  ' Close the file
  Close #fnum
 
End Sub