Excel Pragma

The objective is to create a spreadsheet that could automatically generate an Oracle SQL script to “INSERT” data into a table. A typical INSERT statement looks like : INSERT INTO <table>(<field string names>) VALUES (<values string>);

The problem when trying to generate the strings from a range of cells is to

1- put the values in the cells between quotes.

2- exclude certain types of cells from being put between quotes (for example, date values).

3- concatenate the quoted and non-quoted cells into one string.

The ConcSep function was very useful in generating the final string but the function needed more tweaking to put the values between quotes (while allowing the user to specify the quote character/string). So here’s the function :

Public Function ConcSepQuote(InCells As Range, Quot As String, Sep As String) As String
    Dim OutStr As String
    Dim Cell As Variant
    
    OutStr = “”
    If InCells Is Nothing Then Exit Function
    On Error Resume Next
    For Each Cell In InCells
        OutStr = OutStr & Quot & Cell.Value & Quot & Sep
    Next Cell
    OutStr = Left(OutStr, Len(OutStr) - Len(Sep))
    ConcSepQuote = OutStr
End Function

and a small example :