title: Export Excel range/sheet to formatted text file
body: <p>I have been tasked with creating a reusable process for our Finance Dept to upload our payroll to the State(WI) for reporting. I need to create something that takes a sheet or range in Excel and creates a specifically formatted text file.</p>
  
  <p><strong><em>THE FORMAT</em></strong></p>
  
  <ul>
  <li>Column 1 - A Static Number, never changes, position 1-10</li>
  <li>Column 2 - A Dynamic Param filled at runtime for Quarter/Year, position 11-13</li>
  <li>Column 3 - SSN, no hyphens or spaces,
  filled from column A, position 14-22</li>
  <li>Column 4 - Last Name, filled from
  column B, Truncated at 10, Left
  Justify &amp; fill with blanks, position
  23-32</li>
  <li>Column 5 - First Name, filled from C,
  Truncate at 8, Left Justify &amp; fill
  with blanks, position 33-40</li>
  <li>Column 6 - Total Gross Wages/Quarter,
  filled from D, strip all formatting,
  Right Justify Zero Fill, position
  41-49</li>
  <li>Column 7 - A Static Code, never
  changes, position 50-51</li>
  <li>Column 8 - BLANKS, Fill with blanks,
  position 52-80</li>
  </ul>
  
  <p>I have, I assume, 3 options:</p>
  
  <ol>
  <li>VBA</li>
  <li>.NET</li>
  <li>SQL</li>
  </ol>
  
  <p>I had explored the .NET method first but I just couldn't find decent documentation to get me going.  I still like this one but I digress.</p>
  
  <p>Next I have some VBA that will dump a Sheet to a fixed width Text.  I am currently pursuing this which leads, finally, to my actual question.</p>
  
  <p>How do I transform a Range of text in Excel?  Do I need to coy it over to another sheet and then pass over that data with the neccesarry formatting functions the run my Dump to text routine?  I currently had planned to have a function for each column but I am having trouble figuring out how to take the next step.  I am fairly new at Office programming and developing in general so any insight will be greatly appreciated.</p>
  
  <p>The SQL option would be my fall back as I have done similar exports from SQL in the past.  I just prefer the other two on the, <em>"I don't want to be responsible for running this,"</em> principle.</p>
  
  <p>Thanks in advance for any time given.</p>