How to export Excel worksheets to database with SQL statement file.

When you’d like to register Excel worksheets in database, you could ‘BULK INSERT’ txt file into database. In this article, I’d like to explain VBA code that exports Excel worksheets to sql file. It’s assumed that worksheet name is same as table name and first column order is same as table attribute order.

Option Explicit

Sub EXPORT_SQL()
    Dim mySht   As Worksheet
    Dim myAr    As Variant
    Dim i       As Long
    Dim j       As Long
    Dim mySQL   As String
    Dim SQLAr() As String
    Dim myFSO   As Object
    Dim myTS    As Object
    Dim myPath  As String
    Dim n       As Long
    myPath = ThisWorkbook.Path
    For Each mySht In Worksheets
        myAr = mySht.UsedRange.Resize(mySht.UsedRange.Rows.Count - 1).Offset(1)
        ReDim SQLAr(LBound(myAr) To UBound(myAr))
        For i = LBound(myAr) To UBound(myAr)
            For j = LBound(myAr, 2) To UBound(myAr, 2)
                If myAr(i, j) = Empty Then
                    mySQL = mySQL & "NULL, "
                Else
                    mySQL = mySQL & "'" & myAr(i, j) & "', "
                End If
            Next j
            mySQL = "INSERT INTO " & mySht.Name & " VALUES (" & Left(mySQL, Len(mySQL) - 2) & ")"
            SQLAr(i) = mySQL
            mySQL = ""
        Next i
        Set myFSO = CreateObject("Scripting.FileSystemObject")
        Set myTS = myFSO.CreateTextFile(Filename:=myPath & "\" & mySht.Name & ".sql", Overwrite:=True)
        For n = LBound(SQLAr) To UBound(SQLAr)
            myTS.writeline SQLAr(n)
        Next n
        myTS.Close
        Set myFSO = Nothing
        Set myTS = Nothing
    Next mySht
End Sub