StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExport Excel range/sheet to formatted text file
text
Body
copied!<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 & fill with blanks, position 23-32</li> <li>Column 5 - First Name, filled from C, Truncate at 8, Left Justify & 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload