Embedded line feeds in Excel causes problem

[Mike Blakley]

I have received a report that Audit Commander does not properly handle data in Excel sheets when any of the cells contain line feeds - i.e. multiple rows within a single cell.

Unfortunately, the way that Microsoft ADODB handles Excel (or other) data sources is that whenever a line feed or carriage return are encountered, the assumption is that this marks the end of the line, even though that row may contain more data. This causes incorrect results because the line is effectively split up into multiple lines.

There is a work-around, which is to replace all the line feeds and carriage returns with some other character, e.g. a dash. This can be done using an Excel macro such as shown below:

Sub repcrlf()
'
' replace all cr and lf in range specified
'
Dim r As Range
Dim c As Object
Dim stemp As String
Dim i As Integer
Dim s As String
Dim irep As Long
Dim iLoop As Integer
Dim sSheet As String
Dim sRepChar As String

'<<<<<<<<<<<<<<<<< specify the sheet name on the next line
sSheet = "TM"
Set r = Sheets(sSheet).UsedRange
'<<<<<<<<<<<<<<<<< specify the replacement character on the next line
sRepChar = "-"
irep = 0
iLoop = 0
For Each c In r
stemp = c.Value
iLoop = iLoop + 1
For i = 1 To Len(stemp)
s = Mid(stemp, i, 1)
Select Case s
Case vbLf
Mid(stemp, i, 1) = sRepChar
irep = irep + 1
c.Value = stemp
Case vbCr
Mid(stemp, i, 1) = sRepChar
irep = irep + 1
c.Value = stemp
Case vbTab
Mid(stemp, i, 1) = sRepChar
irep = irep + 1
c.Value = stemp
End Select
Next i
Next c
Application.StatusBar = "Replacements: " & irep
End Sub


This macro can be copied and pasted into the Excel workbook as a module. Change the Sheet name as required and also change the replacement character, if you do no wish to use a "dash". After running this macro on the worksheet, Audit Commander should interpret all the data correctly.