How do I export data in text format with minimum of spaces?

[ilya2]

The following macro exports a specified portion of Excel file as
a .prn:

Sub ExportA1()

Sheets("A1").Select
ActiveWindow.SmallScroll Down:=-3
Range("D321:AR350").Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"Detail.prn", FileFormat:=xlTextPrinter, CreateBackup:=False
End Sub

The output looks more or less like this:

SKU 01 0 570511455 2 1
2.49 2.49
SKU 01 0 570511455 2 1
14.99 -1.50 13.49

Whereas I want it to look like this:

SKU 01 0 570511455 2 1 2.49 2.49
SKU 01 0 570511455 2 1 14.99 -1.50 13.49

Does SaveAs have a parameter which would compress any sequence of
spaces into one space?

 

[Dave Peterson]

If you save as a .prn file, excel will use the column widths to pad (or
truncate!) the values.

Maybe you could adjust the columnwidths to be what you want before you save.
I'd change the font to Courier New (a non-proportional font) to make it a bit
easier.

You may be able to use a formula in a helper cell in each row:

=left(a1,3) & " " & text(b1,"00" & " " & ....

Then copy that formula down the column (after you have it perfekt!). Then copy
that column, paste into Notepad and save from there.

You could hide that column so that it's always available, but not too
distracting.

====
Or you could use a macro and write code and have complete control over what you
write.

Here are three sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

==============
Take a look at Earl's program. It may do what you want.