title: Export Excel data to fixed-width text file - field locations
body: <p>Let me begin by saying I'm kind of new to working with delimited files. I am trying to emulate how a piece of software lays out a text file using Excel.</p>
  
  <p>Here is the code I'm using to create a text file from the worksheet:</p>
  
  <pre><code>Sub Export_Selection_As_Fixed_Length_File()
       ' Dimension all  variables.
      Dim DestinationFile, CellValue, Filler_Char_To_Replace_Blanks As String
      Dim FileNum, ColumnCount, RowCount, FieldWidth As Integer
      Dim sht As Worksheet
  
      'Below are options incase you want to change the folder where VBA stores the .txt file
      'We use ActiveWorkbook.Path in this example
      'ActiveWorkbook.Path 'the activeworkbook
      'ThisWorkbook.Path  'the workbook with the code
      'CurDir  'the current directory (when you hit File|open)
  
  
      'If a cell is blank, what character should be used instead
      Filler_Char_To_Replace_Blanks = " "
  
          'Check if the user has made any selection at all
          If Selection.Cells.Count &lt; 2 Then
              MsgBox "Nothing selected to export"
              Selection.Activate
              End
          End If
  
      'This is the destination file name.
      DestinationFile = ActiveWorkbook.Path &amp; "/textfile.txt"
      'Obtain next free file handle number.
      FileNum = FreeFile()
  
       ' Turn  error checking off.
      On Error Resume Next
  
       ' Attempt to open destination file for output.
      Open DestinationFile For Output As #FileNum
  
       ' If an error occurs report it and end.
      If Err &lt;&gt; 0 Then
           MsgBox "Cannot open filename " &amp; DestinationFile
           Selection.Activate
          End
      End If
  
       ' Turn error checking on.
      On Error GoTo 0
  
       '  Loop for each row in selection.
      For RowCount = 1 To Selection.Rows.Count
                  For ColumnCount = 1 To Selection.Columns.Count
                      CellValue = Selection.Cells(RowCount, ColumnCount).Text
                      If (IsNull(CellValue) Or CellValue = "") Then CellValue = Filler_Char_To_Replace_Blanks
                      FieldWidth = Cells(1, ColumnCount).Value
                      If (ColumnCount = Selection.Columns.Count) Then
                              Print #FileNum, Format$(CellValue, "!" &amp; String(FieldWidth, "@")) &amp; vbCrLf;
                      Else: Print #FileNum, Format$(CellValue, "!" &amp; String(FieldWidth, "@"));
                      End If
                  Next ColumnCount
           ' Start next iteration of RowCount loop.
      Next RowCount
       ' Close destination file.
      Close #FileNum
      Selection.Activate
      Workbooks.OpenText Filename:=DestinationFile
  End Sub
  </code></pre>
  
  <p>The software I'm trying to emulate has "data locations" and "field sizes." For example, one field has a data location of 77, which means it will start as the 77th character on the line in the text file. (I don't know how common this is, so if it's very common, please excuse the useless information.) And the field size is 12.</p>
  
  <p>If that doesn't make sense, here's a screenshot of a text file. The first line shows what my VBA creates, and the second line is how I want it to look. How can I force the values on the worksheet to start at a certain position on the line based on the column it's in?</p>
  
  <p><a href="https://i.stack.imgur.com/W9jIQ.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/W9jIQ.gif" alt="enter image description here"></a></p>