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

[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

 

[Joe4]

Welcome to the Board!

Try changing it like this (note the changed in red):

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, 11) = 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, 76) = 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

You are just telling it which character to start at, without any space padding for those fields.

 

[andreurc]

Welcome to the Board!

Try changing it like this (note the changed in red):

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, 11) = 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, 76) = 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

You are just telling it which character to start at, without any space padding for those fields.

Works like a charm Joe4!

Let me abuse and ask one quiestion more please.

In column K, if I don’t use text in the cell, in TXT is shown in this fotmat, aaaa/mm/dd instead mm/aa, I’ve to write in text. In some other columns also happens (B). Any way to export exactly what is shown in Excel?

Thank you very much again, really appreciated!


Andreu

 

[Joe4]

In column K, if I don’t use text in the cell, in TXT is shown in this fotmat, aaaa/mm/dd instead mm/aa, I’ve to write in text. In some other columns also happens (B). Any way to export exactly what is shown in Excel?

If you have applied some sort of cell formatting to the cell, you will need to apply that same formatting inside your code.

So if column K is a date entered with a format of "mm/dd", you would need to update this line of your code:

   Mid(TempLine, 122 - Len(cl.Cells(1, 11))) = cl.Cells(1, 11)

to this:

   Mid(TempLine, 122 - Len(Format(cl.Cells(1, 11), "mm/dd"))) = Format(cl.Cells(1, 11), "mm/dd")