Wow, I was going to ask this question myself but it was already asked. All Excel clipboard output is tab delimited by default. This is kind of annoying for "real" plain text output when you have a fixed width font but not necessarily tab delimiter support.
Anyway, I found and modified a small Excel Macro that will copy the currently selected region as a simple fixed-width columns ASCII table -- like so:
187712 201 37 0.18
2525 580 149 0.25
136829 137 43 0.31
Here's the Macro code. To use it, make sure you enable the Developer tab in Excel Options if you are using Excel 2007 or later.
Sub CopySelectionToClipboardAsText()
' requires a reference to "Windows Forms 2.0 Object Library"
' add it via Tools / References; if it does not appear in the list
' manually add it as the path C:\Windows\System32\FM20.dll
Dim r As Long, c As Long
Dim selectedrows As Integer, selectedcols As Integer
Dim arr
arr = ActiveSheet.UsedRange
selectedrows = UBound(arr, 1)
selectedcols = UBound(arr, 2)
Dim temp As Integer
Dim cellsize As Integer
cellsize = 0
For c = 1 To selectedcols
temp = Len(CStr(Cells(1, c)))
If temp > cellsize Then
cellsize = temp
End If
Next c
cellsize = cellsize + 1
Dim line As String
Dim output As String
For r = 1 To selectedrows
line = Space(selectedcols * cellsize)
For c = 1 To selectedcols
Mid(line, c * cellsize - cellsize + 1, cellsize) = Cells(r, c)
Next c
output = output + line + Chr(13) + Chr(10)
Next r
Dim MyData As MSForms.DataObject
Set MyData = New DataObject
MyData.SetText output
MyData.PutInClipboard
MsgBox "The current selection was formatted and copied to the clipboard"
End Sub
3Just to add some clarity - it will "automatically" determine widths based on the width you specify for your columns. While normally the measurements approximate to ems (as far as I recall), when exporting to fixed width prn they are taken literally and rounded down to give integer values used for the number of characters in a column of the prn file. Spaces are added for padding, to the left or right depending on alignment in the cells (so by default numbers wil be padded on the left to force them right, text on the right to force left, but if you have changed alignments they will be different) – AdamV – 2010-01-25T21:55:44.527
This does do what I was looking for. Thanks! – RationalGeek – 2010-01-26T19:34:41.637
In order to use the macro pointed to by iokevins, you have to remove "& delimiter" from the two places where CellText is defined. Otherwise, it will add an additional space for each cell, which saving as a prn does not do. – farrenthorpe – 2015-10-12T19:49:17.977
1
Please note: Formatted text (.prn) is limited to 240 characters per line in Excel http://support.microsoft.com/kb/249885
– iokevins – 2012-09-20T16:57:07.693