Hello, 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.

THE FORMAT

Column 1 - A Static Number, never changes, position 1-10
Column 2 - A Dynamic Param filled at runtime for Quarter/Year, position 11-13
Column 3 - SSN, no hyphens or spaces, filled from column A, position 14-22
Column 4 - Last Name, filled from column B, Truncated at 10, Left Justify & fill with blanks, position 23-32
Column 5 - First Name, filled from C, Truncate at 8, Left Justify & fill with blanks, position 33-40
Column 6 - Total Gross Wages/Quarter, filled from D, strip all formatting, Right Justify Zero Fill, position 41-49
Column 7 - A Static Code, never changes, position 50-51
Column 8 - BLANKS, Fill with blanks, position 52-80
I have, I assume, 3 options:

VBA
.NET
SQL
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.

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.

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.

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, "I don't want to be responsible for running this," principle.

Thanks in advance for any time given

First of all "I don't want to be responsible for running this" - FORGET IT.

Your writing COMPLIANCE CODE at the governmental level. You must get this perfect, and maintainable.

Look into removing the VBA and doing it in some other language that will pick up and read the data out. If you leave the code behind the spreadsheet someone somehow will muck it up.

If I were you this is what I would architect:
1. A client application for the user to "pick" the file.
2. A class for reading, parsing and formating the data.
3. A data layer to save the SUBMITTED data for future reference.
4. Plan on a reporting system, some one is going to want to know.

You are messing with peoples taxes, their payroll. If you screw up one, 10:1 you screw em all up. You need a paper trail, and trust me when I say the governing agency WILL lose your submittals.

Don't forget to do your validations on every piece of data in every column. 1 improperly placed or mis-handled apostrophe will completely mess up your system, also excel allows you to enter some really funky stuff.

Good luck. Ive been working with systems for sending data into government controlled systems for over 10 years, so once you get into this, the more "fun" in becomes.

pete

oh and by the way...since your working with personally identifying information you are subject to the Sarbanes-Oxley Act of 2002.

Throw that at your manager, big fun.

out. pete.

If you are working with Excel Worksheets, Excel VBA would be a good choice to create a text file from Excel worksheet data. It sounds like you are not an expert at Excel VB. If you do not have a long time to teach yourself VB you should find someone to do this for you.

If you are pulling data from very large Excel files, then you may want to run an ADO query to get a subset of data based on criteria and work through the recordset to put together lines to write to a text file.

Most likely you will use the Print Statement in your code to write raw text to the file. Here's an example of opening a freefile to write data


Next you are going to identify the range or area you want to get text from and loop the range to get your text string. You can use string commands if you need to restrinct text length or check criteria of partial strings. Limit text to 25 characters, Left(strVar, 25) all Upper, Ucase(strVar)





Bill

You can try with GemBox .NET Excel component. It is very easy to work with and it's very fast. Here's an example how to export excel in c# and vb.