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