Skip to content

Instantly share code, notes, and snippets.

@relopezbriega
Last active August 29, 2015 14:14
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save relopezbriega/c63475a562c4a618c94d to your computer and use it in GitHub Desktop.
Create SQL INSERT stament from Excel
Sub CreateInsert()
Dim rng As Range
Dim insertRng As Range
Dim lastColumn As Integer
Dim lastRow As Integer
Dim tabla As String
Dim insertText As String
Dim wsTest As Variant
Dim my_column As Integer
Dim OldSheet As Variant
Dim my_row As Integer
Dim lastInsertRow As Integer
Dim totalRows As Integer
Dim i As Integer
Application.ScreenUpdating = False
Set rng = Application.Selection
lastColumn = rng.Columns.Count + rng.column - 1
lastRow = rng.Rows.Count + rng.Row - 1
lastInsertRow = (lastRow - rng.Row) * 3
totalRows = rng.Rows.Count
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets("raulo_insert")
On Error GoTo 0
Set rng = Application.Selection
Set OldSheet = ActiveSheet
If wsTest Is Nothing Then
Worksheets.Add.Name = "raulo_insert"
End If
Sheets("raulo_insert").Cells.ClearContents
OldSheet.Activate
tabla = UCase(InputBox("Ingrese la libreria/tabla en la que quiere insertar los datos", "Tabla"))
answer = MsgBox("Terminar las sentencias con ;?", vbQuestion + vbYesNo, "Terminar sentencias con ;")
Sheets("raulo_insert").Range("A1").Value = "INSERT INTO " & tabla
Sheets("raulo_insert").Range("A2").Value = " ("
Sheets("raulo_insert").Cells(2, rng.Columns.Count + 2).Value = ")"
my_row = 2
my_column = 2
On Error GoTo Error
For Each cell In rng
If cell.column = lastColumn Then
If cell.Row = rng.Row Then
Sheets("raulo_insert").Cells(my_row, my_column).Value = cell.Value
Else
Sheets("raulo_insert").Cells(my_row, my_column).Value = "''" & cell.Value & "'"
End If
my_row = my_row + 1
my_column = 1
Sheets("raulo_insert").Activate
Set insertRng = Range(Cells(1, 1), Cells(2, rng.Columns.Count + 2))
OldSheet.Activate
Else
If cell.Row = rng.Row Then
Sheets("raulo_insert").Cells(my_row, my_column).Value = cell.Value & ","
Else
Sheets("raulo_insert").Cells(my_row, my_column).Value = "''" & cell.Value & "',"
End If
End If
If cell.Row - rng.Row <> 0 And _
cell.column = lastColumn Then
insertRng.Copy Sheets("raulo_insert").Cells(my_row, 1)
my_row = my_row + 2
End If
my_column = my_column + 1
Next cell
For i = 3 To lastInsertRow Step 3
Sheets("raulo_insert").Cells(i, 1).Value = "VALUES ("
If answer = vbYes Then
Sheets("raulo_insert").Cells(i, rng.Columns.Count + 2).Value = ");"
Else
Sheets("raulo_insert").Cells(i, rng.Columns.Count + 2).Value = ")"
End If
Next i
Sheets("raulo_insert").Rows(lastInsertRow + 1).Delete Shift:=xlUp
Sheets("raulo_insert").Rows(lastInsertRow + 1).Delete Shift:=xlUp
Application.ScreenUpdating = True
MsgBox "Cantidad de insert creados: " & totalRows - 1, , "Raulo Insert"
Exit Sub
Error:
MsgBox "Ha ocurrido un error, verifique la seleccion a procesar", , "Error"
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment