[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