Cells to Columns in Text File

  • Thread starter Thread starter MacroAlan
  • Start date Start date
M

MacroAlan

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.

:confused:
 
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
 
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/[email protected]
 
Janet said:
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.
Click to expand...

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.
 
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.
 
Back
Top