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.