SaveAs call changes text to #####

[dawn]

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) + "" + 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) + "" + 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.

 

[Jim Cone]

Change the cell format to "General"
--
Jim Cone
Portland, Oregon USA



"dawn"
wrote in message
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:

-snip-

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.

 

[dawn]

This did not work. I tried that too. I tried General, Scientific, Number,
Text.

This is the data in the cell:
200/ 209/ 254/ 346/ 347/ 403/ 407/ 420/ 427/ 428/ 432/ 433/ 434/ 435/ 436/
452/ 453/ 455/ 458/ 459/ 460/ 475/ 518/ 522/ 523/ 524/ 525/ 526/ 527/ 528/
529/ 540/ 550/ 553/ 556/ 557/ 559/ 570/ 571/ 573/ 574/ 575/ 576/ 577/ 578/
579/ 580/ 605/ 606/ 655/ 659/ 697/ 700/ 702/ 705/ 706/ 708/ 709/ 729/ 735/
768/ 770/ 788/ 795/ 796/ 801/ 802/ 805/ 806/ 807/ 809/ 819/ 859/ 884/ 886/
912/ 919/ 931/ 938/ 948/ 949/ 951/ 970/ 985/ 986/ 988/ 996/ 997/ 998

I also tried changing the SaveAs fileFormat to other things too - and they
just truncated all the fields (not just the one above).

 

[Dave Peterson]

Excel has trouble when the number of characters in a cell is between 256 and
1024 and the cell's format is Text.

Comment out or delete this line:
Selection.NumberFormat = "@"

Then try this:

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 trim(cell.Value) = "" Then
If INSERT_DASH Then cell.Value = "'-" '<-- added apostrophe
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 cell

I'm not sure how those insert_dash, convert_text, replace_comma variables get
set, but I think I'd do all the work in a variable and only update the cell
once. (Still remove that .numberformat = "@" line.)


dim myStr as string
.....
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
myStr = cell.value
If trim(mystr) = "" Then
If INSERT_DASH Then mystr = "-"
Else
If CONVERT_TEXT Then
'I don't understand what this does
mystr = " " & mystr
mystr = Right(mystr, Len(mystr) - 1)
End If
If REPLACE_COMMA Then
mystr = Replace(cell.Value, ",", "/")
End If
End If
mycell.value = "'" & mystr
Next cell

 

[dawn]

Thank you to both Jim Cone and Dave Peterson. Between your 2 replies, I was
able to solve my issue. The code change and the original file change
(General instead of Text) solved the problem.

Thanks again.