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

"Janet" wrote in message
...
Okay -this takes several steps.

First, change your font to one of the static font sizes
like Courier so you can see the spacing easily.


Fixed-point or fixed-width font.

Static tends to denote a variable declaration.

And what's up with with "Courier New" versus "Courier"?
Is "Courier" a printer font? There's just something funny
about "Courier" that I haven't figured out right at the moment.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


Second, make sure you have set the columns in number
format with a specified number of decimal places so all
the decimals line up, now, right justified.

Third, set ALL of your column widths to the proper figures
to have your decimals end up in the right place.

Save the worksheet as a .prn file. This will preserve the
proper spacing. You can simply change the .prn to .txt
and open it in your word processing or text editor.

The trick is really the font type.

Then you can use the decimal tab from there on out.

Hope this helps. email me if you need more help.


-----Original Message-----
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.

I cannot simply save the file to PRN because I do not want to send
everything. I already wrote a loop that picks up the precise data and
writes to a line in the file.

This is basically what I have come up with (with some help from other
groups!):


For L = 5 To daLastRow
'Lot of mess but writes to correct positions
numberToFormat = Cells(L, 26)
sNumber = Format(numberToFormat, "#.00")
B1 = Space(17 - Len(sNumber)) & sNumber
numberToFormat = Cells(L, 29)
sNumber = Format(numberToFormat, "#.00")
b2 = Space(17 - Len(sNumber)) & sNumber
numberToFormat = Cells(L, 32)
sNumber = Format(numberToFormat, "#.00")

Print #1, Tab(5); Left(Cells(2, 1), 6); Tab(14); tsCol; _
B1; b2; B3; B4; B5; B6; B7; B8; B9; B10; B11; B12
Next L



Just hoping that there was an automatic way to line up in a position
without the calisthenics.



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