Exporting to a text file

P

Paul

How to export an Access query to a text file without the column heading? The
query consists of single column and is used to import to an accounting
system. Thanks.
 
K

Ken Snell \(MVP\)

Cannot be done with the built-in TransferText action / method. You'd need to
open a text file via VBA code and write the query's records (one at a time)
into that file. If interested, post back and I'll provide some sample code.
 
P

Paul

Yes please...

Thanks


Ken Snell (MVP) said:
Cannot be done with the built-in TransferText action / method. You'd need
to open a text file via VBA code and write the query's records (one at a
time) into that file. If interested, post back and I'll provide some
sample code.
Click to expand...
 
K

Ken Snell \(MVP\)

Here is a sample subroutine to write data to text file without a header row
in the text file -- change the value of the strTextFile constant to be the
path and filename of the textfile to be created, and change the value of the
strDelim constant to be the delimiter that you want to use to separate the
field values, and change the value of the strSQL constant to be either the
name of the table/query you want to export OR to be the SQL statement that
will provide the data you want to export:


Public Sub WriteRstToTextFileWithoutHeaderRow()

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim intF As Integer
Dim lngLoop As Long
Dim strRecord As String, strTemp As String

Const strTextFile As String = "C:\MyTextFile.txt"
Const strDelim As String = ","
Const strSQL As String = "SELECT * FROM TableName;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

intF = FreeFile

Open strTextFile For Output As #intF

If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
strRecord = ""
For lngLoop = 0 To rst.Fields.Count - 1
' delimit value with " characters if value contains delimiter
If InStr(rst.Fields(lngLoop).Value, strDelim) > 0 Then
strTemp = Chr(34) & Nz(rst.Fields(lngLoop).Value, "") &
Chr(34)
Else
strTemp = Nz(rst.Fields(lngLoop).Value, "")
End If
strRecord = strRecord & strTemp & strDelim
Next lngLoop
If Len(strRecord) > 0 Then
strRecord = Left(strRecord, Len(strRecord) - Len(strDelim))
Print #intF, strRecord
End If
rst.MoveNext
Loop
End If

Close #intF

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub
 
Top