File = InputBox("Enter file destination like C:\\filename.sql", "Filename", "c:\\InsertScript.sql")

INSERT INTO [BASE_TABLE]([C1]) VALUES('Joe''s Bar')

Sub CreateInsertScript()
    Dim Row As Integer
    Dim Col As Integer
    
    'To store all the columns available in the current active sheet
    Dim ColNames(100) As String
    
    Col = 1
    Row = 1
    Dim ColCount As Integer
    ColCount = 0
    'Get Columns from the sheet
    Do Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.
        ColNames(ColCount) = "[" + ActiveSheet.Cells(Row, Col) + "]"
        ColCount = ColCount + 1
        Col = Col + 1
    Loop
    ColCount = ColCount - 1
    Dim LastRow As Long
    Dim LastCol As Integer
    Dim S As Worksheet
    Set S = ActiveSheet
    
    LastRow = S.UsedRange.Rows.Count
    LastCol = S.UsedRange.Columns.Count
    
    'Inputs for the starting and ending point for the rows
    Row = InputBox("Give the starting Row No.", , 2)
    
    Dim MaxRow As Integer
    MaxRow = InputBox("Give the Maximum Row No.", , LastRow)
    
    Dim tableName As String
    tableName = ActiveSheet.Name
    tableName = InputBox("Give the Table name.", , tableName)
    
    Dim CellColCount As Integer
    Dim StringStore As String 'Temporary variable to store partial statement
    
    Do While Row <= MaxRow
        StringStore = ""
        CellColCount = 0
        'ActiveSheet.Name will give the current active sheet name
        'this can be treated as table name in the database
        StringStore = StringStore + "insert into [" + ActiveSheet.Name + "] ( "
        Do While CellColCount <= ColCount
            StringStore = StringStore + ColNames(CellColCount)
            'To avoid "," after last column
            If CellColCount <> ColCount Then
                StringStore = StringStore + " , "
            End If
            CellColCount = CellColCount + 1
        Loop

        StringStore = StringStore + " ) values("

        CellColCount = 0
        Do While CellColCount <= ColCount
            StringStore = StringStore + " '" + Replace(CStr(ActiveSheet.Cells(Row, CellColCount + 1)), "'", "''") + "'"
            If CellColCount <> ColCount Then
                StringStore = StringStore + ", "
            End If
            CellColCount = CellColCount + 1
        Loop
        
        'Update the last column with the statement
        Cells(Row, LastCol + 1).Value = StringStore & ");"
        Row = Row + 1
    Loop
    
    MsgBox ("Successfully Done")
End Sub

   Dim FieldName As String
   FilePath = ActiveWorkbook.Path
   FieldName = ActiveSheet.Name
   Open FilePath & "\" & TableName & ".sql" For Output As #1

   Dim SheetObj As Variant
   SheetObj = Sheets(1).UsedRange.Value