Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003: Alignment problems saving workbook as prn file.
This seems to be a very specific problem so I will give exact steps on how to
reproduce the problem. 1. Open a new blank xls 2. Make sure error checking is on and specifically the option for "Numbers stored as text." (Tools, Options, Error Checking) 2. Format columns A-C as Text 3. Right-Align column B - (this is key) 4. Enter 1234 in cell A1 5. Enter 5678 in cell A2 6. Enter 9999 in cell C1 and notice with each number entered the green triangle appears indicating error checking. 7. Enter 1234 in cell D1 8. Now format cell D1 as Text and notice no error checking even though it is number stored as text. 9. Copy cell D1 to cell B2 and still no green triangle 10. Save the file as Formatted Text (Space Delimited) (*.prn) 11. Now open the .prn file in NotePad and notice how column B is not aligned. 12. It seems that cells that are right-aligned and have the error checking triangle will be shifted to the left one space so that they are not aligned anymore. 13. Using the formula =cell("type",B1) shows that the cell with error checking is "l" which means it contains a text constant and =cell("type",B2) is "v". 14. Obviously they are treated differently which you can even see opening the .xls in NotePad. Cells with error checking internally are stored different than the cells without the error checking triangle. 15. However, both cells look the same in Excel and should therefore save the same when saved as a prn file. 16. One fix is to highlight column B after the paste and then click Data, Text to Columns, Finish. 17. Is this situation known and/or is there a fix? Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003: Alignment problems saving workbook as prn file.
Is this situation known and/or is there a fix?
" wrote: This seems to be a very specific problem so I will give exact steps on how to reproduce the problem. 1. Open a new blank xls 2. Make sure error checking is on and specifically the option for "Numbers stored as text." (Tools, Options, Error Checking) 2. Format columns A-C as Text 3. Right-Align column B - (this is key) 4. Enter 1234 in cell A1 5. Enter 5678 in cell A2 6. Enter 9999 in cell C1 and notice with each number entered the green triangle appears indicating error checking. 7. Enter 1234 in cell D1 8. Now format cell D1 as Text and notice no error checking even though it is number stored as text. 9. Copy cell D1 to cell B2 and still no green triangle 10. Save the file as Formatted Text (Space Delimited) (*.prn) 11. Now open the .prn file in NotePad and notice how column B is not aligned. 12. It seems that cells that are right-aligned and have the error checking triangle will be shifted to the left one space so that they are not aligned anymore. 13. Using the formula =cell("type",B1) shows that the cell with error checking is "l" which means it contains a text constant and =cell("type",B2) is "v". 14. Obviously they are treated differently which you can even see opening the .xls in NotePad. Cells with error checking internally are stored different than the cells without the error checking triangle. 15. However, both cells look the same in Excel and should therefore save the same when saved as a prn file. 16. One fix is to highlight column B after the paste and then click Data, Text to Columns, Finish. 17. Is this situation known and/or is there a fix? Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003: Alignment problems saving workbook as prn file.
You need to follow my detailed steps exaclty, I believe everything is there,
to see what I am talking about. Only one column should be right-aligned. In that column cells that have error checking showing with the green triangle, when saved as a prn file will be off by a space when opened in Notepad. Ryan "Nick Hodge" wrote: Ryan I'm sorry, I can't replicate your example. These are summations by me...not fact 1) Smart tags seem to represent 'errors' information that the user may not know, therefore a pre-formatted cell, column would show a text number error, but they don't appear to be 'retrospective', that is if you enter a number and then convert it to text as in D1 it shows no smart tag. I don't find this an issue as you have knowingly made the number in the cell text. Maybe a calc should show it, or 'resetting ignored errors' should maybe do so...it doesn't, but the functionality you seek me be someone else's annoyance. 2) The .prn file structure (Fixed width - Space delimited) is determined by column width, that is, if you want your 'fixed width' to be 4 characters, then set it to that, my default width is 8.43, which translates to 8 characters, my data is 1234....1234....1234....1234 Where the periods represent spaces so Right aligned, 4 spaces, right aligned, 4 spaces, right aligned, 4 spaces, 4 chars (EOR) No spaces after end of record because it is the last field and numeric. I see no issue (Excel 2003 SP1), but maybe I am misunderstanding. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS wrote in message ... Is this situation known and/or is there a fix? " wrote: This seems to be a very specific problem so I will give exact steps on how to reproduce the problem. 1. Open a new blank xls 2. Make sure error checking is on and specifically the option for "Numbers stored as text." (Tools, Options, Error Checking) 2. Format columns A-C as Text 3. Right-Align column B - (this is key) 4. Enter 1234 in cell A1 5. Enter 5678 in cell A2 6. Enter 9999 in cell C1 and notice with each number entered the green triangle appears indicating error checking. 7. Enter 1234 in cell D1 8. Now format cell D1 as Text and notice no error checking even though it is number stored as text. 9. Copy cell D1 to cell B2 and still no green triangle 10. Save the file as Formatted Text (Space Delimited) (*.prn) 11. Now open the .prn file in NotePad and notice how column B is not aligned. 12. It seems that cells that are right-aligned and have the error checking triangle will be shifted to the left one space so that they are not aligned anymore. 13. Using the formula =cell("type",B1) shows that the cell with error checking is "l" which means it contains a text constant and =cell("type",B2) is "v". 14. Obviously they are treated differently which you can even see opening the .xls in NotePad. Cells with error checking internally are stored different than the cells without the error checking triangle. 15. However, both cells look the same in Excel and should therefore save the same when saved as a prn file. 16. One fix is to highlight column B after the paste and then click Data, Text to Columns, Finish. 17. Is this situation known and/or is there a fix? Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems saving Workbook as webpage with interactivity (Excel 2002 | Excel Worksheet Functions | |||
Saving Excel 2007 file in 2003 creates very large file | Excel Discussion (Misc queries) | |||
Problems with saving a Excel file | Excel Discussion (Misc queries) | |||
Saving a workbook in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 - Shared Workbook NOT Saving Changes | Excel Worksheet Functions |