Removing special characters before importing text in Excel 2007

[neeraj_chow]

I have a 300MB+ text file which needs to be imported in Excel 2007. But some fields in the text file contains few special characters like tab or newline character. I do not know which of the fields contains these chars. While importing, these chars cause the line to split in multiple lines or shift the data in next column, resulting loss of alignment in the columns. I need help with removing these special characters before importing in excel. Using help of net and this board I clobbered some code
-------------------------------------------------------------
Function RemoveSplChars()

Dim temp_string As String
Dim filebytes() As Byte
Dim byteindex As Long
Dim maxbytes As Long

Open Application.GetOpenFilename For Binary As #1
ReDim filebytes(LOF(1) - 1)
Get #1, , filebytes
Close
On Error GoTo 0

' Remove the characters one does'nt want (actually replace them with an unused character, in this case chr(219))

maxbytes = UBound(filebytes)
For byteindex = 0 To maxbytes
If filebytes(byteindex) = 0 Or filebytes(byteindex) = 9 Or filebytes(byteindex) = 10 Or filebytes(byteindex) = 11 Or filebytes(byteindex) = 12 Then filebytes(byteindex) = 32
Next byteindex
' have not handled char(13) here but needs to be handled
' Convert to a string, (also removing the 'substitute' character)
temp_string = Replace(StrConv(filebytes, vbUnicode), Chr$(219), vbNullString)
ReDim filebytes(0)

'temp_string now contains the whole text file
End Function
----------------------------------------------------------------------

The problem is that the code throws run time error 7 'Out of memory' and that the field can contain char(13) which has to be removed but not the one at the end of line.
How can I modify the code so that the two issues are resolved.

 

[AlphaFrog]

You could remove the line-feeds after the text is imported into Excel using the Replace feature. Maybe use a macro like this...

Sub Remove_LineFeed()
    Cells.Replace What:=Chr([COLOR="Red"]10[/COLOR]), Replacement:=" ", LookAt:=xlPart
    Cells.WrapText = False
    Columns("A:IV").AutoFit
End Sub

Chr(10) is Line Feed and Chr(13) is a Carriage Return. I'm not sure which one you would need.

I don't have a definitive suggestion for the out of memory error. Perhaps you should write the processed text back to a temporary text file instead of to a string variable. Or split the text into multiple variables.