EFT file VBA/Macro help

[mattdete]

I am trying to figure how to create a csv file with the attributes below for an EFT file. I think using a macro would work.






Header Record
Field # Data Element Start Length Format Description
Pos.
1 Record Type 1 1 X(1) “1”
2 Date 2 8 X(8) System Date; MMDDCCYY
3 Time 10 4 X(4) HHMM
4 File Identification 14 11 X(11) “HR”
5 Begin Period Date 25 8 X(8) MMDDCCYY
6 End Period Date 33 8 X(8) MMDDCCYY
7 Filler 41 360 X(360) Blanks




Detail Record
Field # Data Element Start Length Format Description
Pos.
1 Record Type 1 1 X(1) Valid Value
1= Header Record
2=Detailed record
4=Trailer record
Required to identify source of file
2 Social Security Number 2 9 X(9) Required
GCP and International Transfers will always be sent with a 9 + Employee ID if they do not have a valid SSN
3 SSN Change Flag 11 1 X(1) Field will be populated if SSN is changing and will not be populated on any subsequent file unless the SSN changes again.
4 Last Name 12 30 X(30) Required
5 First Name 42 20 X(20) Required
6 Middle Name 62 20 X(20) Optional
7 Birth Date 82 8 X(8) MMDDCCYY
8 Gender 90 1 X(1) Valid Values:
9 Address Line 1 91 30 X(30) Street address
10 Address Line 2 121 30 X(30) P.O. Box/Apt. #
11 City 161 24 X(24) Required
12 State/Providence 185 2 X(2) U.S./Canadian postal standard
13 ZIP Code 187 9 X(9) Required if participant lives in the U.S but will be blank if participant does not live in the U.S..
14 Country Code 196 3 X(3) Valid Values:
15 Full‑Time/Part‑Time Code 199 1 X(1) Used to determine insurance eligibility.
16 FT/PT Effective date 200 8 X(8) MMDDCCYY
17 Tax Unit Code 208 2 X(2) This code is used for payroll reconciliation of employee contributions and loan repayments, calculates 401(k) match, year-end payroll reporting and is used in Connections and Financial Manager reporting.
18 File Status Code 210 1 X(1) Used to indicate employee’s or senior executive’s employment status.
19 Reason for Leaving 210 2 X(2) This code and the file status code will be used to determine which Employment Status value gets set on TBA and Current Employment Status Date will be used to effective date Employment Status...
20 Current Employment Status Date 211 8 9(8) MMDDCCYY
21 Hire Date 219 8 9(8) MMDDCCYY
22 Pay Frequency 227 1 X(1) Valid value:
23 Office Phone Number 228 10 X(10) Alight will derive effective date using the period end date in the file header record.
24 Annual Basic Salary 238 9 S9(7)V9(2)
25 Annual Basic Salary Effective Date 247 8 X(8) MMDDCCYY
26 401(k) Match Eligibility Code 255 2 X (2) Valid Values:




27 DPS Eligibility Code 257 2 X(2) PA=0% - not eligible
28 DB Eligibility Code 259 3 X (3) Valid Values:
29 Job Level 262 8 X(8) Field will only be used for informational purposes and will no longer be used as a benefit driver.
30 Acquisition Code 270 5 X(5) Valid Values




Trailer Record
Field # Data Element Start Length Format Description
Pos.
1 Record Type 1 1 9(1) 4 = File trailer
2 Record Count 2 9 9(9) Total record count including header and trailer records for type*4 trailer
3 Filler 11 390 X(390)

 

[Joe4]

Are you looking for someone to program this for you?
If so, then take a look at enlisting Consulting Services to help you (https://www.mrexcel.com/consulting-services/).

If you want to try to work on this yourself, and need some pointers, then a good start is to use the Macro Recorder and record yourself manually formatting the columns the way that you need them, and saving as a CSV file.

I did something like this once many years ago. There are a few ways to approach it.
1. Create three tabs in Excel, one for the header, one for the detail, and one for the trailer.
2. On each tab, format each column that way you want it, then save that sheet to a CSV file
3. Combine the three exported CSV files (header, detail, trailer) into a single CSV file

Another way (a little trickier):
1. Write code that combines every column into a single column, separating each value by a comma (treating each section appropriately)
2. Export as a Space Delimited (prn) text file

A third way (trickiest, requires most knowledge of VBA):
1. Write VBA code that goes through and writes each line directly to a CSV file

If you choose to try it yourself, one of the most important functions you will use is the TEXT function (if doing it in Excel worksheet), or the similar FPRMAT function in VBA.
This is what you would use to format dates and numbers the way you want/need, i.e.

=TEXT(A1,"MMDDYY")

If you want to take a stab at doing this yourself, I would recommend starting with the Macro Recorder, and posting here as you come across specific questions.

 

[mattdete]

this gets me what I need for the detailed record. but, how can I insert a header and footer with unique positions and column widths? i am thinking there must be a way to do a sub procedure.


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 = 3 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 & strCell & 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



'for example the code could be called using:


Sub CreateFile()
Dim sPath As String
sPath = Application.GetSaveAsFilename("", "Text Files,*.prn")
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(30) As Integer
'starting at 0 specify the width of each column
s(0) = 1
s(1) = 9
s(2) = 1
s(3) = 30
s(4) = 20
s(5) = 20
s(6) = 8
s(7) = 1
s(8) = 30
s(9) = 30
s(10) = 24
s(11) = 2
s(12) = 9
s(13) = 3
s(14) = 1
s(15) = 8
s(16) = 2
s(17) = 1
s(18) = 2
s(19) = 8
s(20) = 0
s(21) = 1
s(22) = 10
s(23) = 9
s(24) = 8
s(25) = 2
s(26) = 2
s(27) = 3
s(28) = 8
s(29) = 5
'for example to use 3 columns with field of length 5, 10 and 15 you would use:
'dim s(2) as Integer
's(0)=5
's(1)=10
's(2)=15
'write to file the data from the activesheet
CreateFixedWidthFile sPath, ActiveSheet, s
End Sub

 

[Joe4]

I never tried writing all three sections directly to a text file like that. So I would be in the same boat as you, trying that technique.
You would have to play around with it to see what you could get it to do.
You may need to keep the file open until you have written all the different data lines.

I might try to approach it like this:
Have one "main" sub procedure which starts the process and calls three other sub procedures, one that builds/write the data for each section.
I don't know if that is the best way to go about it, but it probably is how I try to approach it.