Helper function for outputting Access query data via TransferSpreadsheet to Excel.

Excel wants LF only, Access uses CRLF

Function CRLF2LF(s As Variant) As String
    ' Helper function for outputting data via TransferSpreadsheet to Excel. Excel wants LF only, Access uses CRLF
    If IsNull(s) Then
        CRLF2LF = ""
    Else
        CRLF2LF = Replace(s, Chr(13) & Chr(10), Chr(10))
    End If
End Function

This function can be used in a query to convert field's contents to properly export to Excel, and avoid the 'funny blocks' that show up when fields containing newlines are exported to Excel. This is due to the fact that Acess uses CRLF (Carriage Return/Line Feed) to denote a newline, whereas Excel only uses LF. In Escel, the CR will show up as an unknown character represented by a little block.

Permalink