Home » Questions » Computers [ Ask a new question ]

Export an Excel spreadsheet to fixed-width text file?

Export an Excel spreadsheet to fixed-width text file?

Excel has the functionality to import fixed-width text files where it presents a dialog that lets you choose where the begins and ends of fields are which it puts into columns.

Asked by: Guest | Views: 243
Total answers/comments: 4
Guest [Entry]

"I think the closest you can get from native Excel functionality is Save As | Formatted Text (Space Delimited) (*.prn). It will automatically determine the widths and insert spaces to pad to that width as necessary.

Beyond that you need to have a macro or other add-in that will let you do more."
Guest [Entry]

"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"
Guest [Entry]

"This is a killer for me. It has a few options as well.

http://www.sensefulsolutions.com/2010/10/format-text-as-table.html"
Guest [Entry]

It works with Access out of the box: support.office.com/en-ie/article/export-data-to-a-text-file-f72dfc38-a8a0-4c5b-8c2c-bf2950814140#bmsteps with this way I managaed it quite easy and fast - better than with Excel. In my case it was a conversion of the table.