(Solved) – SQL Insert via VBA scripting

  • by
(solved)-–-sql-insert-via-vba-scripting

I want to write a script to format my tables into the format required to insert into SQL.

My initial thoughts are to do it something like this.

  Public Sub Main()
    Dim sb As StringBuilder
    Set sb = New StringBuilder

    lr = Cells(Rows.Count, 1).End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column

    For i = 2 To lr
        For n = 1 To lc
        If n = 1 Then
            sb.Append "('" & Cells(i, n) & "',"
        ElseIf n = lc Then
            sb.Append Cells(i, n) & "), "
        Else
            sb.Append Chr(39) & Cells(i, n) & "',"
        End If

        Next n
    Next i

Debug.Print sb.toString
End Sub

But I am not sure how to account for the changing data types in the spreadsheet (i.e. some will not require single quotes) Any ideas or will this just not work? I do not want to reformat the report everytime I run this. Thanks for the help.

Leave a Reply

Your email address will not be published. Required fields are marked *