Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs call changes text to #####
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs call changes text to #####
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs call changes text to #####
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). "Jim Cone" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs call changes text to #####
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 wrote: 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs call changes text to #####
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SaveAs Text | Excel Programming | |||
runtime error 1004 saveas text file | Excel Programming | |||
SaveAs API Call | Excel Programming | |||
SaveAs text file from VBA Getting Error | Excel Programming | |||
saveas fixed text for unix | Excel Programming |