Exporting as ASCII file

[kev]

I need to export a query as an ASCII file. the file needs
the first 22 lines as 'common' header data (i.e line 1 is
name, line 2 is address01, line 3 is address 02, etc, then
I need 'xx' amount of details lines, then 14 lines
of 'common' footer information (such as total, VAT, etc).
can anyone tell me how to do this please?

 

[Roger Carlson]

I would use low level I/O to export your data. Something like this:

Sub ExportTextFile()
On Error GoTo ExportTextFile_Err

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Directory As String
Dim MyString As String, strSQL As String
Dim strDS As String

Set cnn = CurrentProject.Connection
strDS = cnn.Properties("data source")
Directory = (Mid(strDS, 1, Len(strDS) - Len(Dir(strDS))))

Open Directory & "\TestOutput.txt" For Output As #1

rst.Open "qryMyQuery", cnn, adOpenForwardOnly, adLockReadOnly

rst.MoveFirst
Do While Not rst.EOF
'Header information
Print #1, rst!Name
Print #1, rst!Address01
'and so on for the 14 header lines

'Detail Lines
Print #1, rst!Detail01
Print #1, rst!Detail02
'etc.
'if the detail lines are in another table, then you'd
'open another recordset and loop thru that instead

'Footer Lines
Print #1, rst!Total
Print #1, rst!VAT

rst.MoveNext
Loop

ExportTextFile_Exit:
' Close text file.
Close #1
rst.Close
Set cnn = Nothing
Exit Sub

ExportTextFile_Err:

MsgBox Err.Description
Resume ExportTextFile_Exit

End Sub