I too liked this script 🙂
I modified it a little so that any single quotes are replaced, the results are put into the last column and it prompts you for the table name, for when you forget to change the name of the sheet. 😉
As ever when looking a utility macros, I always like to mention it's worth storing them in your PERSONAL.xls, so that they are available in any worksheet, just in case someone reading this doesn't know about that handy feature.
Well done Arun
HTH
Dave Jackson
Sub CreateInsertScript()
Dim Row As Integer
Dim Col As Integer
'To store all the columns available in the current active sheet
Dim ColNames(100) As String
Col = 1
Row = 1
Dim ColCount As Integer
ColCount = 0
'Get Columns from the sheet
Do Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.
ColNames(ColCount) = "[" + ActiveSheet.Cells(Row, Col) + "]"
ColCount = ColCount + 1
Col = Col + 1
Loop
ColCount = ColCount - 1
Dim LastRow As Long
Dim LastCol As Integer
Dim S As Worksheet
Set S = ActiveSheet
LastRow = S.UsedRange.Rows.Count
LastCol = S.UsedRange.Columns.Count
'Inputs for the starting and ending point for the rows
Row = InputBox("Give the starting Row No.", , 2)
Dim MaxRow As Integer
MaxRow = InputBox("Give the Maximum Row No.", , LastRow)
Dim tableName As String
tableName = ActiveSheet.Name
tableName = InputBox("Give the Table name.", , tableName)
Dim CellColCount As Integer
Dim StringStore As String 'Temporary variable to store partial statement
Do While Row <= MaxRow
StringStore = ""
CellColCount = 0
'ActiveSheet.Name will give the current active sheet name
'this can be treated as table name in the database
StringStore = StringStore + "insert into [" + ActiveSheet.Name + "] ( "
Do While CellColCount <= ColCount
StringStore = StringStore + ColNames(CellColCount)
'To avoid "," after last column
If CellColCount <> ColCount Then
StringStore = StringStore + " , "
End If
CellColCount = CellColCount + 1
Loop
StringStore = StringStore + " ) values("
CellColCount = 0
Do While CellColCount <= ColCount
StringStore = StringStore + " '" + Replace(CStr(ActiveSheet.Cells(Row, CellColCount + 1)), "'", "''") + "'"
If CellColCount <> ColCount Then
StringStore = StringStore + ", "
End If
CellColCount = CellColCount + 1
Loop
'Update the last column with the statement
Cells(Row, LastCol + 1).Value = StringStore & ");"
Row = Row + 1
Loop
MsgBox ("Successfully Done")
End Sub