I have this piece of VB code in Excel that takes in various Excel files and
translates them into tab delimited text files. The code to go through the
files in the directory one by one and do the export does not work in Excel
2007 so I have modified it. I now have it exporting the files. However,
some of them are not exporting correctly. If the data in the cell is too
many characters, it exports the field as ########### instead of the values.

This is the code to do the export of the individual files:

Function ExportCurWorkbook() As Boolean
Dim cell As Object, i As Integer
Dim wrkbk As Workbook, flPath As String

On Error GoTo ExportFail

i = 2

'Select cell A2, *first line of data*.
Range("A" + Format(i) + ":D" + Format(i)).Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
Selection.NumberFormat = "@"
For Each cell In Selection
'Apply formatting that the user has selected
'if the cell is empty, insert a - replace commas
'with / to avoid quotes in text file
If IsNull(cell.Value) Or cell.Value = "" Or cell.Value = " " Then
If INSERT_DASH Then cell.Value = "-"
Else
If CONVERT_TEXT Then
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
End If

If REPLACE_COMMA Then
cell.Value = Replace(cell.Value, ",", "/")
End If
End If
'A2=LEFT(A1,CEILING(LEN(A1)/2,1))
'A3 = Right(A1, Floor(Len(A1) / 2, 1))

Next
' Step down 1 row from present location.
i = i + 1
Range("A" + Format(i) + ":D" + Format(i)).Select
Loop

'Save the workbook as a text file, then close the spreadsheet
Set wrkbk = ActiveWorkbook

If EXPORT_FILE Then
If txtpath = "" Then txtpath = "C:"
Else
txtpath = "Y:\QuickLinks\MSAccess2007 test folder jkn\test
specialsearch\data\exchange_route\"
End If

' Remove the .xls in the filename and tack on .txt
flPath = txtpath & "\" & Left(wrkbk.name, Len(wrkbk.name) - 4) & ".txt"

Application.DisplayAlerts = False
wrkbk.SaveAs flPath, xlTextWindows

wrkbk.Close SAVE_FILE

ExportCurWorkbook = True

ExportCurWorkbook_exit:
Exit Function

ExportFail:
ExportCurWorkbook = False
Resume ExportCurWorkbook_exit

End Function

I have narrowed the problem down to this line: wrkbk.SaveAs flPath,
xlTextWindows - when I step through the Do Until loops, the cell.Value of the
cell in question is what is supposed to be but when the SaveAs call is made,
it converts the cell to #############.

I am at a loss. As a note, this is not the function I was working on - this
same call (wrkbk.SaveAs) is used in Excel 2003 and the cells are exported
correctly. There is no other difference in the .xls files that are being
changed to text format. I have also verified it is not the data itself - but
rather the length of the data.

Thank you.