Export an Excel to a SQL-like text file

gistfile1.vb

Sub ExportSQL()

    Dim FilePath As String
    Dim CellData As String
    Dim LastCol As Long
    Dim LastRow As Long
    
    CellData = ""
    
    FilePath = Application.ActiveWorkbook.Path & "\insert.sql"
    
    LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
    
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = fso.CreateTextFile(FilePath)
    
    Application.DecimalSeparator = "."
    Application.ThousandsSeparator = ""
    Application.UseSystemSeparators = False
    
    For Row = 1 To LastRow
        CellData = "("
        
        For Col = 1 To LastCol
            If Not IsEmpty(ActiveCell(Row, Col)) Then
                If IsNumeric(ActiveCell(Row, Col)) Then
                    CellData = CellData & Replace(Trim(ActiveCell(Row, Col).Value), ",", ".")
                Else
                    CellData = CellData & "'" & Replace(Trim(ActiveCell(Row, Col).Value), "'", "\'") & "'"
                End If
                
                If Col <> LastCol Then CellData = CellData & ","
            End If
        Next Col
        
        oFile.WriteLine CellData & "),"
    
    Next Row
    
    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing
    
    MsgBox ("Fatto!")
    

End Sub