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?