Writing text file - very urgent

[Jonathan Blitz]

I am trying to wrote out data to a text file from a recordset I have built
from an SQL statement.

The am using the PUT command but I am having problems as for some reason it
all seems to come out on one line.

I have tried using Binary, Random and all other formats. I have also tried
add vbCrLf to the line - all to no avail.

What am I doing wrong?

Many thanks
Jonathan Blitz
AnyKey Limited
Israel

[SteveT]

Here are some sample routines:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDeliminator As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiinator is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or Chr(20) for Space
'blnHeader - True = write field header row, False = data only

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & Chr(34) & fld.NAME & Chr(34) & strDeliminator
Next

'remove extra last deliminator
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
'use quoted strings
If fld.Type = 10 Or fld.Type = 12 Then
varData = varData & Chr(34) & fld.Value & Chr(34) &
strDeliminator
Else
varData = varData & fld.Value & strDeliminator
End If
Next

'remove extra last deliminator
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
End Sub

Public Sub ExportFixed(strTable As String, strExportFile As String, Optional
blnHeader As Boolean)

'strTable is the table or query name or Select Sql string
'strExportFile is the full path and name of file to export to
'blnHeader - True = write field header row, False = data only

Dim fld As DAO.Field
Dim varData As Variant
Dim rs As DAO.Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & Left(fld.NAME & Space(fld.Size), fld.Size)
Next

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
'Left aligned
varData = varData & Left(fld.Value & Space(fld.Size), fld.Size)

'right aligned
'varData = varData & Right(Space(fld.Size) & fld.Value, fld.Size)
Next

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
End Sub

Public Sub ExportRs(ByRef rs As DAO.Recordset, strExportFile As String)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to

Dim fld As Field
Dim intFileNum As Integer
Dim varData As String
Dim strDeliminator As String

strDeliminator = ","

'get file handle and open for output
intFileNum = FreeFile()

Open strExportFile For Output As #intFileNum

'now your data
Do While Not rs.EOF

varData = ""

'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDeliminator
Next

'remove extra last deliminator
varData = Left(varData, Len(varData) - 1)

Print #intFileNum, varData

rs.MoveNext
Loop

'We're done!
Close #intFileNum
rs.Close
Set rs = Nothing
End Sub

"Jonathan Blitz" <muf...@yahoo.com> wrote in message
news:eAzkCSAjCHA.2268@tkmsftngp10...

[Jonathan Blitz]

In the end I used a Stream and wrote using a WriteLine command.
I saw someone else suggesting using a CopyFIle command.

Is there any difference?

(Just for future knowledge. Now that it works well "If it ain't broke don't
fix it")

Jonathan

<SteveT> wrote in message news:uTyj8mAjCHA.2300@tkmsftngp10...