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.