If you want to save the INSERT script in the same location where the spreadsheet is located and name the file like the active sheet:

Dim FieldName As String

FilePath = ActiveWorkbook.Path

FieldName = ActiveSheet.Name

Open FilePath & "\" & TableName & ".sql" For Output As #1

And to avoid to manually specify the size (end row and column), use the spreadsheet size:

Dim SheetObj As Variant

SheetObj = Sheets(1).UsedRange.Value

The VBA function UBound(SheetObj, 1) gives the last row number and UBound(SheetObj, 2) the last column used

Note: Empty trailing rows are also considered so your code needs to deal with that.

Finally if the purpose is to insert data into a DB/table, why not insert it directly from EXCEL using i.e. an ADODB object?