Okay, maybe vba-ing your way from Excel files to raw SQL isn't the way most folks extract, transform, and load, but I like it okay when you get an Excel file of values.

This code is essentially the same as this jive from 2009, but I like that I've finally given up on adding a UserForm, naming it, adding a text box, naming it too, then stretching each until they look like a finished product.

Instead, I now just add a form, double-click a TextBox on there, and let this programmatically do the rest.

Option Explicit

Public Sub createSql()
    Dim i As Integer
    Dim intStart As Integer
    Dim intEnd As Integer
    
    Dim strInsertSql As String
    Dim strUpdateSql As String
    Dim strLine As String
    
    Dim strOut As String
    
        
    intStart = 2
    intEnd = 963
    
    i = intStart
    While (Not "" = Cells(i, 1).Value)  ' assuming there are no blanks in the first col
    'While (i <= intEnd)  ' if you'd rather hard-code
        
        
        strLine = "INSERT INTO TABLE1 " & _
            " (FIELD1, FIELD2) " & _
            " VALUES " & _
            " (" & _
                CStr(Cells(i, 1).Value) & "," & _
                CStr(Cells(i, 2).Value) & _
            ")"
           
        strInsertSql = strInsertSql & strLine & "; -- " & (i - intStart + 1) & vbNewLine
        
        
        
        strLine = "UPDATE TABLE2 SET " & _
            "FIELD1 = '" & CStr(Cells(i, 1).Value) & "' " & _
            "WHERE FIELD3 = '" & CStr(Cells(i, 2).Value) & "'; -- " & (i - intStart + 1)

        strUpdateSql = strUpdateSql & strLine & "; -- " & (i - intStart + 1) & vbNewLine
            
        
        
        i = i + 1
    Wend

    
    strOut = strInsertSql & vbNewLine & _
        vbNewLine & _
        "--------------------------------------------------" & vbNewLine & _
        vbNewLine & _
        strUpdateSql & vbNewLine & _
        vbNewLine & _
        "--------------------------------------------------" & vbNewLine & _
        "--------------------------------------------------" & vbNewLine & _
        vbNewLine


        
    '============================================
    ' making this so I don't have to set up the
    ' danged userform and textbox any more
    '============================================
    UserForm1.Height = 800
    UserForm1.Width = 1000
    With UserForm1.TextBox1
        .Top = 10
        .Left = 10
        .Height = UserForm1.Height - 40
        .Width = UserForm1.Width - 20
        .MultiLine = True
        .ScrollBars = fmScrollBarsBoth
    End With
    '============================================
    '============================================
    
    
    UserForm1.TextBox1.Text = strOut
    UserForm1.Show
    
End Sub

Labels: , , ,