[Excel] How do align some columns exporting to TXT (VBA)?

[andreurc]

Hi mates,

I'm new and no experiencing with coding, I've used a VBA code from Interent and adapted a little bit, works well but I need a little help aligning some columns.

Here is the code:

Sub ExportTextCustom()
Dim TempLine As String
Dim fileNum As Integer
Dim strFileName As String
Dim cl As Range
Dim POS As Integer
strFileName = ThisWorkbook.Path & "\Demo.txt"

fileNum = FreeFile 'get a freefile number
 Open strFileName For Output As #fileNum

'define the length of a line by adding up all the fields length
    TempLine = Space(133)
For Each cl In Range("A1", Range("A1").End(xlDown))
TempLine = Space(133)
'write to specific position in string

   Mid(TempLine, 2 - Len(cl.Cells(1, 1))) = cl.Cells(1, 1)
   Mid(TempLine, 8 - Len(cl.Cells(1, 2))) = cl.Cells(1, 2)
   Mid(TempLine, 58 - Len(cl.Cells(1, 3))) = cl.Cells(1, 3)
   Mid(TempLine, 66 - Len(cl.Cells(1, 4))) = cl.Cells(1, 4)
   Mid(TempLine, 71 - Len(cl.Cells(1, 5))) = cl.Cells(1, 5)
   Mid(TempLine, 93 - Len(cl.Cells(1, 6))) = cl.Cells(1, 6)
   Mid(TempLine, 106 - Len(cl.Cells(1, 7))) = cl.Cells(1, 7)
   Mid(TempLine, 111 - Len(cl.Cells(1, 8))) = cl.Cells(1, 8)
   Mid(TempLine, 116 - Len(cl.Cells(1, 9))) = cl.Cells(1, 9)
   Mid(TempLine, 117 - Len(cl.Cells(1, 10))) = cl.Cells(1, 10)
   Mid(TempLine, 122 - Len(cl.Cells(1, 11))) = cl.Cells(1, 11)
   Mid(TempLine, 134 - Len(cl.Cells(1, 12))) = cl.Cells(1, 12)
Print #fileNum, TempLine
Next cl
Close #fileNum
End Sub

I've this Excel and with this marco can export to TXT as a delimited spaces, but I need the column C and F to align left, not to the right as you can see in this images.







Can you help me please? I've used "HorizontalAlignment = xlLeft" but doen't work...

Thank you very much!!!!


Andreu

[jdc2000]

I am still not exactly sure what you are looking for. It would appear that you are importing that text file as a Fixed Width file and then selecting the position to add breaks. If you do it that way, you would need to correctly set up the data and and desired leading spaces. A better method would be tab delimited, and then you could just output the fields in the correct order.

If you are getting the data into Excel in the correct columns as desired, text data should default to left alignment. You may need to specify the data as Text when importing.

[Zvoni]

Yes, looks like fixed-length string.
I'd start with: Each Excel column has which width in the fixed-length TXT-File?
If i see this correctly, the first column should be 2 characters in width, but OP exports only 1.
So that's going to blow up the moment he reaches 10th row

That said: I've done similiar things enough times.
Define the Width of the TARGET for each column.
Then i can help you

@Jdc
I'm thinking using a UDT with fixed-length Strings. That should pad everything automagically