Write / Append Recordset From A Table To Textfile

[hawkpig]

Hi guys

Here's a simple helpful function to create a datestamped batch file with a header, some tab delimited table data and a trailer.

I know there is room for improvement but hope it helps someone.

*********************************

Function BUILD_AMBER_FILE()

Dim filedate As String
Dim filename As String
Dim rst As Recordset

DoCmd.OpenQuery "G16 DATA TO AMBER", acNormal, acEdit ' CREATE TABLE WITH DATA IN IT

filedate = Year(Date) & Month(Date) & Day(Date) ' CREATE DATESTAMP FOR FILE
filename = "W:\FINANCE\Amber Finance\AMBER1" & filedate & ".TXT" ' CREATE FILENAME FOR FILE
Set rst = CurrentDb.OpenRecordset("G05 DATA TO AMBER TABLE") ' ASSIGN RECORDSET POINTER TO CORRECT TABLE

Open filename For Output As #1 ' OVERWRITE / CREATE NEW FILE TO WRITE TO
Print #1, "HEADER" & vbTab & filedate & "01" & vbTab & Now() ' WRITE A HEADER TO THE FILE

Do Until rst.EOF
Print #1, rst(0) & vbTab & rst(1) 'WRITE FIELDS TO FILE - TAB DELIMITED
rst.MoveNext 'GO TO NEXT RECORD
Loop

Print #1, "TRAILER" & vbTab & rst.RecordCount 'WRITE A FOOTER TO FILE WITH RECORD COUNT

Close #1 ' CLOSE THE FILE
rst.Close ' CLOSE THE RECORDSET
Set rst = Nothing ' CLEAR RECORDSET FROM MEMORY
DoCmd.DeleteObject acTable, "G05 DATA TO AMBER TABLE" ' REMOVE TABLE CREATED EARLIER


End Function