title: Excel VBA, Keep Formatting when saving as a .txt
body: <p>I am importing a fixed width text file to Excel and then exporting it. 
  I'm trying to figure out how to keep the formatting when saving.<br>
  On the import I have tried to use .NumberFormat, which forced formatting on import but on export it looses the formatting.</p>
  
  <p>Text file I have imported:
  <a href="https://i.stack.imgur.com/68CrL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/68CrL.png" alt="Text file I am importing"></a></p>
  
  <p>my export routine:</p>
  
  <pre><code>Option Explicit
  Sub CreateFixedWidthFile(strFile As String, ws As Worksheet, s() As Integer)
    Dim i As Long, j As Long
    Dim strLine As String, strCell As String
  
    'get a freefile
    Dim fNum As Long
    fNum = FreeFile
  
   'open the textfile
    Open strFile For Output As fNum
   'loop from first to last row
   'use 2 rather than 1 to ignore header row
    For i = 2 To ws.Range("a65536").End(xlUp).Row
      'new line
      strLine = ""
      'loop through each field
      For j = 0 To UBound(s)
        'make sure we only take chars up to length of field (may want to 
        output some sort of error if it is longer than field)
        strCell = Left$(ws.Cells(i, j + 1).Value, s(j))
        'add on string of spaces with length equal to the 
        'difference in length between field length and value length
          strLine = strLine &amp; strCell &amp; String$(s(j) - Len(strCell), Chr$(32))
      Next j
      'write the line to the file
      Print #fNum, strLine
    Next i
   'close the file
    Close #fNum
  End Sub
  </code></pre>
  
  <p>Calling it:</p>
  
  <pre><code>Sub CreateFile()
    Dim sPath As String
    sPath = Application.GetSaveAsFilename("", "Text Files,*.txt")
    If LCase$(sPath) = "false" Then Exit Sub
    'specify the widths of our fields
    'the number of columns is the number specified in the line below +1
    Dim s(5) As Integer
   'starting at 0 specify the width of each column
    s(0) = 10
    s(1) = 2
    s(2) = 9
    s(3) = 10
    s(4) = 1
    s(5) = 20
   'write to file the data from the activesheet
    CreateFixedWidthFile sPath, ActiveSheet, s
  End Sub
  </code></pre>
  
  <p>Exported Text File (see the format of 6th column is now a decimal &amp; scientific notation):
  <a href="https://i.stack.imgur.com/0xPuP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0xPuP.png" alt="After Export"></a></p>