xlTextPrinter and Accounting format

[eugene.gokhvat]

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.

 

[Jim Rech]

I suspect there may be conflict with the comma delimiter as in comma
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
| 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.

 

[eugene.gokhvat]

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.

 

[Jim Rech]

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
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.

 

[eugene.gokhvat]

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

 

[Jim Rech]

I have noticed that using a fixed-width font does help, but I still can't
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
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

 

[eugene.gokhvat]

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 averagefor
"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>

--

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.