Using VBA seems like the way to go to me. This lets you write a macro that takes care of all of the various formatting options and should, hopefully, be simple enough for your finance people to run themselves.
You said you need something that takes a sheet or range in Excel. The first column never changes so we can store that in the macro, columns 3-7 come from the spreadsheet and column 8 is just blank. That leaves column 2 (the quarter/year as QYY) as an issue. If the quarter/year is specified somewhere in the workbook (e.g. stored in a cell, as a worksheet name, as part of the workbook title) then we can just read it in. Otherwise you will need to find some method for specifying the quarter/year when the macro runs (e.g. pop up a dialog box and ask the user to input it)
Some simple code (we'll worry about how to call this later):
Sub ProduceStatePayrollReportFile(rngPayrollData As Range, strCompanyNo As String, _
strQuarterYear As String, strRecordCode As String, strOutputFile As String)
The parameters are fairly obvious: the range that holds the data, the company number for column 1, the quarter/year for column 2, the fixed code for column 7 and the file we want to output the results to
' Store the file handle for the output file
Dim fnOutPayrollReport As Integer
' Store each line of the output file
Dim strPayrollReportLine As String
' Use to work through each row in the range
Dim indexRow As Integer
To output to a file in VBA we need to get a file handle so we need a variable to store that in. We'll build up each line of the report in the report line string and use the row index to work through the range
' Store the raw SSN, last name, first name and wages data
Dim strRawSSN As String
Dim strRawLastName As String
Dim strRawFirstName As String
Dim strRawWages As String
Dim currencyRawWages As Currency
' Store the corrected SSN, last name, first name and wages data
Dim strCleanSSN As String
Dim strCleanLastName As String
Dim strCleanFirstName As String
Dim strCleanWages As String
These sets of variables store the raw data from the worksheet and the cleaned data to be output to the file respectively. Naming them "raw" and "clean" makes it easier to spot errors where you accidentally output raw data instead of cleaned data. We will need to change the raw wages from a string value to a numeric value to help with the formatting
' Open up the output file
fnOutPayrollReport = FreeFile()
Open strOutputFile For Output As #fnOutPayrollReport
FreeFile() gets the next available file handle and we use that to link to the file
' Work through each row in the range
For indexRow = 1 To rngPayrollData.Rows.Count
' Reset the output report line to be empty
strPayrollReportLine = ""
' Add the company number to the report line (assumption: already correctly formatted)
strPayrollReportLine = strPayrollReportLine & strCompanyNo
' Add in the quarter/year (assumption: already correctly formatted)
strPayrollReportLine = strPayrollReportLine & strQuarterYear
In our loop to work through each row, we start by clearing out the output string and then adding in the values for columns 1 and 2
' Get the raw SSN data, clean it and append to the report line
strRawSSN = rngPayrollData.Cells(indexRow, 1)
strCleanSSN = cleanFromRawSSN(strRawSSN)
strPayrollReportLine = strPayrollReportLine & strCleanSSN
The .Cells(indexRow, 1) part just means the left-most column of the range at the row specified by indexRow. If the ranges starts in column A (which does not have to be the case) then this just means A. We'll need to write the cleanFromRawSSN function ourselves later
' Get the raw last and first names, clean them and append them
strRawLastName = rngPayrollData.Cells(indexRow, 2)
strCleanLastName = Format(Left$(strRawLastName, 10), "!@@@@@@@@@@")
strPayrollReportLine = strPayrollReportLine & strCleanLastName
strRawFirstName = rngPayrollData.Cells(indexRow, 3)
strCleanFirstName = Format(Left$(strRawFirstName, 8), "!@@@@@@@@")
strPayrollReportLine = strPayrollReportLine & strCleanFirstName
Left$(string, length) truncates the string to the given length. The format picture !@@@@@@@@@@ formats a string as exactly ten characters long, left justified (the ! signifies left justify) and padded with spaces
' Read in the wages data, convert to numeric data, lose the decimal, clean it and append it
strRawWages = rngPayrollData.Cells(indexRow, 4)
currencyRawWages = CCur(strRawWages)
currencyRawWages = currencyRawWages * 100
strCleanWages = Format(currencyRawWages, "000000000")
strPayrollReportLine = strPayrollReportLine & strCleanWages
We convert it to currency so that we can multiply by 100 to move the cents value to the left of the decimal point. This makes it much easier to use Format to generate the correct value. This will not produce correct output for wages >= $10 million but that's a limitation of the file format used for reporting. The 0 in the format picture pads with 0s surprisingly enough
' Append the fixed code for column 7 and the spaces for column 8
strPayrollReportLine = strPayrollReportLine & strRecordCode
strPayrollReportLine = strPayrollReportLine & CStr(String(29, " "))
' Output the line to the file
Print #fnOutPayrollReport, strPayrollReportLine
The String(number, char) function produces a Variant with a sequence of number of the specified char. CStr turns the Variant into a string. The Print # statement outputs to the file without any additional formatting
Next indexRow
' Close the file
Close #fnOutPayrollReport
End Sub
Loop round to the next row in the range and repeat. When we have processed all of the rows, close the file and end the macro
We still need two things: a cleanFromRawSSN function and a way to call the macro with the relevant data.
Function cleanFromRawSSN(strRawSSN As String) As String
' Used to index the raw SSN so we can process it one character at a time
Dim indexRawChar As Integer
' Set the return string to be empty
cleanFromRawSSN = ""
' Loop through the raw data and extract the correct characters
For indexRawChar = 1 To Len(strRawSSN)
' Check for hyphen
If (Mid$(strRawSSN, indexRawChar, 1) = "-") Then
' do nothing
' Check for space
ElseIf (Mid$(strRawSSN, indexRawChar, 1) = " ") Then
' do nothing
Else
' Output character
cleanFromRawSSN = cleanFromRawSSN & Mid$(strRawSSN, indexRawChar, 1)
End If
Next indexRawChar
' Check for correct length and return empty string if incorrect
If (Len(cleanFromRawSSN) <> 9) Then
cleanFromRawSSN = ""
End If
End Function
Len returns the length of a string and Mid$(string, start, length) returns length characters from string beginning at start. This function could be improved as it doesn't currently check for non-numeric data
To call the macro:
Sub CallPayrollReport()
ProduceStatePayrollReportFile Application.Selection, "1234560007", "109", "01", "C:\payroll109.txt"
End Sub
This is the simplest way to call it. The Range is whatever the user has selected on the active worksheet in the active workbook and the other values are hard-coded. The user should select the range they want to output to the file then go Tools > Macro > Run and choose CallPayrollReport. For this to work, the macro would either need to be part of the workbook containg the data or in a different workbook which had been loaded before the user calls the macro.
Someone would need to change the hard-coded value of the quarter/year before each quarter's report was generated. As stated earlier, if the quarter/year is already stored in the workbook somewhere then it's better to read that in rather than hard-coding it
Hope that makes sense and is of some use