Export Data to *.txt or *prn. Text file to have equal field spacing.

Hi All,

I have to export columns A:J to a text, prn or similar text file. However I require the data from these cells to be exported such that in the text file they align with fields in the text file. By fields I mean the data from cell A1 must be written to the text file within the first 10 spaces of the text file. Data from cell A2 must be written within the 11th space to the 20th space. Data from cell A3 must be written within the 21th space to the 30th space... And so on.

If it helps:

$23456789!123456789!123456789!123456789!123456789!123456789!123456789!123456789!123456789!123456789!
A1_Data ! A2_Data! A3_Data! A4_Data!...

Appologies for the above example the spacing of a 'space' seems to vary. It is important that info within the first 10 spaces (from data of cell A1) of the text are LHS justified, while the other 9 fields are RHS justified.

Obviously the maximum data that can be exported from any 1 cell is 10 characters.

Hopefully that makes some sense...?

The data is required to be used as an input deck for a piece of software.

If anyone can help me out with the above i'd greatly appreciate it!

Cheers

Hi

I'm hoping that you want to take each row and output it, not each column...

Sub yyy()
  Open "c:\temp\rs6.txt" For Output As #1
  
  For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    strr = Left(ce.Value & "          ", 10)
    
    For i = 1 To 9
      strr = strr & Right("          " & ce.Offset(0, i), 10)
    Next i
    Print #1, strr
  Next ce
  
  Close #1
End Sub

rylo