I have to export a number of cells to a textfile in which the decimals
must line up and data is in specific positions in the file.

I cannot use semicolons ; to line up because I need specific right
positions.





------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Perhaps you can adapt this:

This will line up each value in a 10 character wide field with 2
decimal places, padded with spaces. Adjust to suit.

Public Sub PaddedTextFile()
Const SPACESTR As String = " " '6 spaces
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells, _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & Right(SPACESTR & _
Format(myField.Value, "#,##0.00"), 10)
Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1
End Sub



In article ,
MacroAlan wrote:

I have to export a number of cells to a textfile in which the decimals
must line up and data is in specific positions in the file.

I cannot use semicolons ; to line up because I need specific right
positions.





------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

And if you have trouble (.prn files will wrap after 240 characters) or if your
fields have varying length, here's a couple more choices:

I'd either concatenate the cell values into another column:

=LEFT(A1&REPT(" ",20),20) & LEFT(B1&REPT(" ",10),10) & TEXT(C1,"000,000.00")

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/groups?threadm=015...0a% 40phx.gbl

MacroAlan wrote:

I have to export a number of cells to a textfile in which the decimals
must line up and data is in specific positions in the file.

I cannot use semicolons ; to line up because I need specific right
positions.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson