Use array, as Join function helps with concatenation. Consider this macro:

Dim FieldCount As Integer

Sub makeInsert()

Dim r As Range

Dim BeginOfCommand As String

Dim RestOfCommand As String

Dim Command As String

FieldCount = 0

File = "c:\temp\InsertCode.txt" 'Associate file

fHandle = FreeFile() ' Clean it

Open File For Output As fHandle 'and open it for writing

For Each r In ActiveSheet.UsedRange.Rows 'Go through all nonempty, continuous rows

If r.Row = 1 Then ' first row contains field names

BeginOfCommand = "INSERT INTO [myTable] (" & CollectFields(r, 1) & ") VALUES "

Else

RestOfCommand = CollectFields(r, 2) ' the others contains data for input

Command = BeginOfCommand & "(" & RestOfCommand & ");" 'prepare INSERT declaration

Print #fHandle, Command ' and write it in the file

End If

Next

Close #fHandle

End Sub

Function CollectFields(r As Range, FieldType As Integer) As String

If FieldType = 1 Then 'Detect how many fields we have

While r.Cells(1, FieldCount + 1) <> ""

FieldCount = FieldCount + 1

Wend

FieldCount = FieldCount - 1

End If

ReDim arr(FieldCount) As Variant 'Declare array

Dim v As Variant

For i = 0 To FieldCount

v = r.Cells(1, i + 1).Value

If IsNumeric(v) Then

arr(i) = v

Else

If FieldType = 1 Then

arr(i) = "[" & v & "]"

Else

arr(i) = "'" & v & "'"

End If

End If

Next

CollectFields = Join(arr, ", ")

End Function

Here You only need to name the fields in the first row and fill the data under. Oh, and if there is no data, just add "NULL"