excel to text with column format

Hello Xtro,

Here is the macro code. The macro has already been added to the attached workbook. You will need to change the file path and name to what you are using. It is marked in red. If the file already exists, any data will be overwritten. If it doesn't exist, the macro will create it.

Type Text_Record
  ID As String * 9
  Filler1 As String * 6
  LastName As String * 17
  FirstName As String * 17
  MI As String * 1
  Filler2 As String * 6
  Suffix As String * 4
  Address1 As String * 35
  Filler3 As String * 1
  Address2 As String * 35
  Filler4 As String * 1
  City As String * 15
  Filler5 As String * 1
  State As String * 2
  Filler6 As String * 1
  ZIP As String * 5
  Filler7 As String * 13
  NewLine As String * 2
End Type
  
Sub WriteDataToFile()

  Dim Buffer As Integer
  Dim Cell As Range
  Dim FileName As String
  Dim Filler As String
  Dim Rng As Range
  Dim RngEnd As Range
  Dim TR As Text_Record
  
    Filler = String(16, "#")
    FileName = "C:\Xtro.txt"
    Buffer = FreeFile
      
      Set Rng = Worksheets("Sheet1").Range("A2")
      Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
      Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))
    
      Open FileName For Random As #Buffer Len = Len(TR)
        For Each Cell In Rng
          With TR
            .ID = Cell
              .Filler1 = Filler
            .LastName = Cell.Offset(0, 1)
            .FirstName = Cell.Offset(0, 2)
            .MI = Cell.Offset(0, 3)
              .Filler2 = Filler
            .Address1 = Cell.Offset(0, 5)
              .Filler3 = Filler
            .Address2 = Cell.Offset(0, 6)
              .Filler4 = Filler
            .City = Cell.Offset(0, 7)
              .Filler5 = Filler
            .State = Cell.Offset(0, 8)
              .Filler6 = Filler
            .ZIP = Cell.Offset(0, 9)
              .Filler7 = Filler
            .NewLine = vbCrLf
          End With
          Put #Buffer, , TR
        Next Cell
      Close #Buffer
    
End Sub