bbecker
asked on
VBA code to write simple Access table to multi-record fixed-width text file with multiple layouts.
I have a simple table in Access 2002/2003. Using VBA, I need to read each record in the table and for each record in the table, write out 3 fixed width text records with defferent layouts to a text file. So for example, if the table has 10 records, I will end up with a single text file with 30 records, but as I said, each of the 3 text records has a different fixed-width layout. One text record is a Header Record, one record is a Wage Record (empty except for a "W" in column 1) and one record is a Tax Record. Note that not all the input fields are needed and a few constant fields are required on the ouput text file (H, W, T, YN, etc). The EIN field is NOT a constant.
Setting up the code to loop thru the recordset (Table) was a no-brainer. I have attached that code. I have also attached some files showing sample contents of the input recordset and the layouts of the 3 record types. I created 3 Export File Specifications and tried to use DoCmd.TransferText acExport Fixed, for example:
txtExportName = "C:\Temp\PaymentExport.txt
Docmd.TransferText acExportFixed, RecordHExportSpec, tblFilteredInput, txtExportName
Docmd.TransferText acExportFixed, RecordWExportSpec, tblFilteredInput, txtExportName
Docmd.TransferText acExportFixed, RecordTExportSpec, tblFilteredInput, txtExportName
But using this results in a text file containing only T records (since each TransferText statement overwrites the prior one (and makes the use of the loop irrelevant, anyway)
So how do I make this loop act as follows:
1. Read a single recordset row
2. Append a properly formatted H record to the text file
3. Append a properly formatted W record to the same text file
4. Append a properly formatted T record to the same text file
5. Read the next row of the recordset and repeat steps 2 thru 4
6. Continue as above until EOF
One other small detail -- there is a single $ amount field in the T (Tax) record. Even using the Export Specification File for the T-record, I can't get the text file to properly fill the amount field with leading zeros and right-justify, for example 000000340.97; it wants to left-justify the amount and strip off the leading zeros, as: 340.97.
PaymentExport.txt
tblRecordHExportSpec.jpg
tblRecordHKey.txt
tblRecordTExportSpec.jpg
Setting up the code to loop thru the recordset (Table) was a no-brainer. I have attached that code. I have also attached some files showing sample contents of the input recordset and the layouts of the 3 record types. I created 3 Export File Specifications and tried to use DoCmd.TransferText acExport Fixed, for example:
txtExportName = "C:\Temp\PaymentExport.txt
Docmd.TransferText acExportFixed, RecordHExportSpec, tblFilteredInput, txtExportName
Docmd.TransferText acExportFixed, RecordWExportSpec, tblFilteredInput, txtExportName
Docmd.TransferText acExportFixed, RecordTExportSpec, tblFilteredInput, txtExportName
But using this results in a text file containing only T records (since each TransferText statement overwrites the prior one (and makes the use of the loop irrelevant, anyway)
So how do I make this loop act as follows:
1. Read a single recordset row
2. Append a properly formatted H record to the text file
3. Append a properly formatted W record to the same text file
4. Append a properly formatted T record to the same text file
5. Read the next row of the recordset and repeat steps 2 thru 4
6. Continue as above until EOF
One other small detail -- there is a single $ amount field in the T (Tax) record. Even using the Export Specification File for the T-record, I can't get the text file to properly fill the amount field with leading zeros and right-justify, for example 000000340.97; it wants to left-justify the amount and strip off the leading zeros, as: 340.97.
Option Compare Database
Option Explicit
Private Sub Main()
Dim db As Database
Set db = CurrentDb
Dim recFilteredInput As DAO.Recordset
Set recFilteredInput = db.OpenRecordset("tblFilteredInput", _
dbOpenDynaset)
Do While Not recFilteredInput.EOF
'Place here commands to write out text records
recFilteredInput.MoveNext
Loop
End Sub
tblFilteredInput.jpgPaymentExport.txt
tblRecordHExportSpec.jpg
tblRecordHKey.txt
tblRecordTExportSpec.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can also use instead of this line
open "c:\myTextFile.txt" for append as #1
'the line below
open "c:\myTextFile.txt" for output as #1
ASKER
Hi capricorn,
Thanks very VERY much. I see where this is going and I believe it will work. Do I dare press you for a solution to my additional little problem -- how do I get (for instance) 340.97 to come out on the text file as 000000340.97, or at least properly right-justify the number (with or without the leading zeroes)? Will I have to parse the field somehow and count the digits then add the appropriate number of zeroes to the left using a variable, or is there a way to right-justify the number? Thanks again. You've earned your points already.
Thanks very VERY much. I see where this is going and I believe it will work. Do I dare press you for a solution to my additional little problem -- how do I get (for instance) 340.97 to come out on the text file as 000000340.97, or at least properly right-justify the number (with or without the leading zeroes)? Will I have to parse the field somehow and count the digits then add the appropriate number of zeroes to the left using a variable, or is there a way to right-justify the number? Thanks again. You've earned your points already.
ASKER
so opening the file as output will clear out any existing data in the file, as opposed to append. And that will save me the trouble of testing whether the file already exists and KILL it. Right?
use format
format(340.97,"000000000.0 0")
format(340.97,"000000000.0
so opening the file as output will clear out any existing data in the file, as opposed to append. And that will save me the trouble of testing whether the file already exists and KILL it. Right?
Yes
Yes
ASKER
capricorn1 you are the BEST!!!!! I owe you my sanity. Your solution works like a charm.
Open in new window