Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlTextPrinter and Accounting format
I'm using the xlTextPrinter file format to save fixed width text
files, but I have run into a problem with "Accounting" formatted cells. When saved in xlTextPrinter format, the numbers in accounting formats have all of the digits after the first comma deleted. I suspect there may be conflict with the comma delimiter as in comma seperated values, but I'm not completely sure. Anyone have any experience with this? The code works perfectly in all other cases. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlTextPrinter and Accounting format
I suspect there may be conflict with the comma delimiter as in comma
seperated values I don't understand what you mean by 'conflict'. If you save a PRN file than it is not a CSV file. The formats are different. Are you renaming the file to CSV? That doesn't make it one. -- Jim wrote in message ... | I'm using the xlTextPrinter file format to save fixed width text | files, but I have run into a problem with "Accounting" formatted | cells. | | When saved in xlTextPrinter format, the numbers in accounting formats | have all of the digits after the first comma deleted. I suspect there | may be conflict with the comma delimiter as in comma seperated values, | but I'm not completely sure. | | Anyone have any experience with this? The code works perfectly in all | other cases. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlTextPrinter and Accounting format
I understand the difference in formats. I am also aware that changing
a file extension to .csv doesn't make its contents comma-seperated. I was simply proposing that the commas in the accounting format may be causing the problem. I have worked on this issue further and discovered that the commas are not the issue. I have found that when saving in xlTextPrinter format, numbers in accounting format will only come out in full when their columns are made much wider. I do not understand why this is the case, but it certainly seems to be consistent. If anyone has any insight on the matter, I would highly appreciate it. Thanks. On Mar 19, 9:49*am, "Jim Rech" wrote: I suspect there may be conflict with the comma delimiter as in comma seperated values I don't understand what you mean by 'conflict'. *If you save a PRN file than it is not a CSV file. *The formats are different. *Are you renaming the file to CSV? *That doesn't make it one. -- wrote in message ... | I'm using the xlTextPrinter file format to save fixed width text | files, but I have run into a problem with "Accounting" formatted | cells. | | When saved in xlTextPrinter format, the numbers in accounting formats | have all of the digits after the first comma deleted. I suspect there | may be conflict with the comma delimiter as in comma seperated values, | but I'm not completely sure. | | Anyone have any experience with this? The code works perfectly in all | other cases. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlTextPrinter and Accounting format
I wasn't trying to insult you, it's just that you never know how technical
people are. I tried reproducing what you were doing, using Accounting format and saving a PRN file via a macro. I got weird results initially. Even though I saw 2 decimal places on screen the PRN had 1 decimal place. Widening the columns made it worse. The wider I went the more characters were cut off on the right. I only got what I wanted when I changed the definition of the Normal style to a fixed width font, Courier specifically. This was the style being used in the cells I used and doing this changes the font in the column and row headers, and seems to work better in creating fixed width text files. Anyway, I got exactly the right output after this. -- Jim wrote in message ... I understand the difference in formats. I am also aware that changing a file extension to .csv doesn't make its contents comma-seperated. I was simply proposing that the commas in the accounting format may be causing the problem. I have worked on this issue further and discovered that the commas are not the issue. I have found that when saving in xlTextPrinter format, numbers in accounting format will only come out in full when their columns are made much wider. I do not understand why this is the case, but it certainly seems to be consistent. If anyone has any insight on the matter, I would highly appreciate it. Thanks. On Mar 19, 9:49 am, "Jim Rech" wrote: I suspect there may be conflict with the comma delimiter as in comma seperated values I don't understand what you mean by 'conflict'. If you save a PRN file than it is not a CSV file. The formats are different. Are you renaming the file to CSV? That doesn't make it one. -- wrote in message ... | I'm using the xlTextPrinter file format to save fixed width text | files, but I have run into a problem with "Accounting" formatted | cells. | | When saved in xlTextPrinter format, the numbers in accounting formats | have all of the digits after the first comma deleted. I suspect there | may be conflict with the comma delimiter as in comma seperated values, | but I'm not completely sure. | | Anyone have any experience with this? The code works perfectly in all | other cases. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlTextPrinter and Accounting format
No offense taken at all, and I appreciate you taking the time to
respond. I have noticed that using a fixed-width font does help, but I still can't figure out why. Another idiosyncracy I have observed is that selecting one column past the accounting format solves the problem as well. The objective for this whole exploration is to create a subroutine that will convert a table in excel to a fixed width text blob. This issue is not that big of a deal, just thought I'd see if anyone had encountered it I have included the code for reference: Sub ExportBBG() 'Creates BBG friendly table Dim sPath As String Dim sFileName As String Dim sSheetName As String Dim wBook As Workbook 'Set application settings so that it runs without nag messages With Application .DisplayAlerts = False .ScreenUpdating = False End With 'Get path to Desktop Dim WSHShell As Object Set WSHShell = CreateObject("Wscript.Shell") sPath = WSHShell.SpecialFolders("Desktop") Set WSHShell = Nothing 'check if temp.prn is open On Error Resume Next Set wBook = Workbooks("temp.prn") If wBook Is Nothing Then On Error GoTo 0 Else wBook.Close On Error GoTo 0 End If sFileName = sPath & "\temp.prn" 'Make a duplicate copy of the active sheet to a new workbook 'Sheets(sSheetName).Copy 'Copy selection to new workbook Selection.Copy Workbooks.Add ActiveSheet.Paste Selection.PasteSpecial Paste:=xlPasteColumnWidths Application.CutCopyMode = False 'Save the new, single-sheet workbook as a fixed-width text file (*.prn) ActiveWorkbook.SaveAs Filename:=sFileName, FileFormat:=xlTextPrinter, CreateBackup:=False 'Close the workbook without saving ActiveWindow.Close (False) Application.Workbooks.Open sFileName 'Restore application settings With Application .DisplayAlerts = True .ScreenUpdating = True End With End Sub On Mar 20, 11:53*am, "Jim Rech" wrote: I wasn't trying to insult you, it's just that you never know how technical people are. I tried reproducing what you were doing, using Accounting format and saving a PRN file via a macro. *I got weird results initially. *Even though I saw 2 decimal places on screen the PRN had 1 decimal place. *Widening the columns made it worse. *The wider I went the more characters were cut off on the right. I only got what I wanted when I changed the definition of the Normal style to a fixed width font, Courier specifically. *This was the style being used in the cells I used and doing this changes the font in the column and row headers, and seems to work better in creating fixed width text files. Anyway, I got exactly the right output after this. -- wrote in message ... I understand the difference in formats. I am also aware that changing a file extension to .csv doesn't make its contents comma-seperated. I was simply proposing that the commas in the accounting format may be causing the problem. I have worked on this issue further and discovered that the commas are not the issue. I have found that when saving in xlTextPrinter format, numbers in accounting format will only come out in full when their columns are made much wider. I do not understand why this is the case, but it certainly seems to be consistent. If anyone has any insight on the matter, I would highly appreciate it. Thanks. On Mar 19, 9:49 am, "Jim Rech" wrote: I suspect there may be conflict with the comma delimiter as in comma seperated values I don't understand what you mean by 'conflict'. If you save a PRN file than it is not a CSV file. The formats are different. Are you renaming the file to CSV? That doesn't make it one. -- wrote in message ... | I'm using the xlTextPrinter file format to save fixed width text | files, but I have run into a problem with "Accounting" formatted | cells. | | When saved in xlTextPrinter format, the numbers in accounting formats | have all of the digits after the first comma deleted. I suspect there | may be conflict with the comma delimiter as in comma seperated values, | but I'm not completely sure. | | Anyone have any experience with this? The code works perfectly in all | other cases.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlTextPrinter and Accounting format
I have noticed that using a fixed-width font does help, but I still can't
figure out why. My understanding, fwiw, is that when Excel creates a fixed width file the "width" is determined by the widths of columns with data, not by the data itself. This gives you more control of the resulting file because, for example, the first field may have a required width of 20 but the longest item in that column of the current data may be only 16. The question then is what is a character's width? With a proportional font each character's could be different. So Excel uses some kind of average for "column width" and the width in the file may bear little relationship to the screen. But with a fixed width font it's exact as all characters have the same width. So what you see is what you get. Forgive me for pontificating if you've figured this all out yourself.<g -- Jim wrote in message ... No offense taken at all, and I appreciate you taking the time to respond. I have noticed that using a fixed-width font does help, but I still can't figure out why. Another idiosyncracy I have observed is that selecting one column past the accounting format solves the problem as well. The objective for this whole exploration is to create a subroutine that will convert a table in excel to a fixed width text blob. This issue is not that big of a deal, just thought I'd see if anyone had encountered it I have included the code for reference: Sub ExportBBG() 'Creates BBG friendly table Dim sPath As String Dim sFileName As String Dim sSheetName As String Dim wBook As Workbook 'Set application settings so that it runs without nag messages With Application .DisplayAlerts = False .ScreenUpdating = False End With 'Get path to Desktop Dim WSHShell As Object Set WSHShell = CreateObject("Wscript.Shell") sPath = WSHShell.SpecialFolders("Desktop") Set WSHShell = Nothing 'check if temp.prn is open On Error Resume Next Set wBook = Workbooks("temp.prn") If wBook Is Nothing Then On Error GoTo 0 Else wBook.Close On Error GoTo 0 End If sFileName = sPath & "\temp.prn" 'Make a duplicate copy of the active sheet to a new workbook 'Sheets(sSheetName).Copy 'Copy selection to new workbook Selection.Copy Workbooks.Add ActiveSheet.Paste Selection.PasteSpecial Paste:=xlPasteColumnWidths Application.CutCopyMode = False 'Save the new, single-sheet workbook as a fixed-width text file (*.prn) ActiveWorkbook.SaveAs Filename:=sFileName, FileFormat:=xlTextPrinter, CreateBackup:=False 'Close the workbook without saving ActiveWindow.Close (False) Application.Workbooks.Open sFileName 'Restore application settings With Application .DisplayAlerts = True .ScreenUpdating = True End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlTextPrinter and Accounting format
On Mar 20, 3:02*pm, "Jim Rech" wrote:
I have noticed that using a fixed-width font does help, but I still can't figure out why. My understanding, fwiw, is that when Excel creates a fixed width file the "width" is determined by the widths of columns with data, not by the data itself. *This gives you more control of the resulting file because, for example, the first field may have a required width of 20 but the longest item in that column of the current data may be only 16. The question then is what is a character's width? *With a proportional font each character's could be different. *So Excel uses some kind of average for "column width" and the width in the file may bear little relationship to the screen. But with a fixed width font it's exact as all characters have the same width. *So what you see is what you get. Forgive me for pontificating if you've figured this all out yourself.<g -- wrote in message ... No offense taken at all, and I appreciate you taking the time to respond. I have noticed that using a fixed-width font does help, but I still can't figure out why. Another idiosyncracy I have observed is that selecting one column past the accounting format solves the problem as well. The objective for this whole exploration is to create a subroutine that will convert a table in excel to a fixed width text blob. This issue is not that big of a deal, just thought I'd see if anyone had encountered it I have included the code for reference: Sub ExportBBG() 'Creates BBG friendly table Dim sPath As String Dim sFileName As String Dim sSheetName As String Dim wBook As Workbook 'Set application settings so that it runs without nag messages With Application * .DisplayAlerts = False * .ScreenUpdating = False End With 'Get path to Desktop Dim WSHShell As Object Set WSHShell = CreateObject("Wscript.Shell") sPath = WSHShell.SpecialFolders("Desktop") Set WSHShell = Nothing 'check if temp.prn is open On Error Resume Next Set wBook = Workbooks("temp.prn") If wBook Is Nothing Then * * On Error GoTo 0 Else * * wBook.Close * * On Error GoTo 0 End If sFileName = sPath & "\temp.prn" 'Make a duplicate copy of the active sheet to a new workbook 'Sheets(sSheetName).Copy 'Copy selection to new workbook Selection.Copy * * Workbooks.Add * * ActiveSheet.Paste * * Selection.PasteSpecial Paste:=xlPasteColumnWidths * * Application.CutCopyMode = False 'Save the new, single-sheet workbook as a fixed-width text file (*.prn) ActiveWorkbook.SaveAs Filename:=sFileName, FileFormat:=xlTextPrinter, CreateBackup:=False 'Close the workbook without saving ActiveWindow.Close (False) Application.Workbooks.Open sFileName 'Restore application settings With Application * .DisplayAlerts = True * .ScreenUpdating = True End With End Sub I completely agree. It's strange that putting the number in accounting format affects the final column width, but manageable nonetheless. Thanks for the feedback. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accounting Format not aligned | Excel Programming | |||
How do I show zero in Accounting format ? | Excel Worksheet Functions | |||
Currency vs. Accounting Format | Excel Worksheet Functions | |||
Display 0's in Accounting format | Excel Discussion (Misc queries) | |||
Problem with xlTextPrinter? | Excel Programming |