Print to textfile from code

[Mackia]

I have the following piece of code which doesn't work. My objective is to
output all the lines in the recordset (sql) to a textfile. Can anyone offer
assistance. Thks.

Sub textfile()

Dim rs As Object

sql = "SELECT PSGL_Detail.*FROM PSGL_Detail;"

Set rs = CurrentDb.OpenRecordset(sql)

Open "TEXTFILE.TXT" For Output As #1

Do Until rs.EOF
Print #1, rs
rs.MoveNext
Loop

Close #1

End Sub

 

[Nikos Yannacopoulos]

Mackia.

Try this:

Sub textfile()
Dim rs As Object
strSQL = "SELECT PSGL_Detail.* FROM PSGL_Detail;"
Set rs = CurrentDb.OpenRecordset(strSQL)
Open "TEXTFILE.TXT" For Output As #1
Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1
vLine = vLine & rs.Fields(i) & ";"
Next
Print #1, vLine
rs.MoveNext
Loop
Close #1
rs.Close
Set rs = Nothing
End Sub

HTH,
Nikos

 

[Mackia]

Thanks Nikos. The code almost works. Except it does something to the fields
which is not what I was after. The resulting recordset from "sql" should be
exported as is a fixed width file with a CTLF at the end of the record.

I probably should of mentioned this earlier. Appreciate any help.

 

[Nikos Yannacopoulos]

Mackia,

My example assumed a semi-colon delimited text fiel. What you are asking
for is somewhat more complicated, and needs specific data with regard to
number, length and alignment of fields. Here's an example:

Sub textfile()
Dim rs As Object

'Assuming 5 fields
Dim vWidth(4) 'field widths in text file
vWidth(0) = 5
vWidth(1) = 10
vWidth(2) = 12
vWidth(3) = 6
vWidth(4) = 20
Dim vAlmt(4) 'field alignment
vAlmt(0) = "R"
vAlmt(1) = "L"
vAlmt(2) = "L"
vAlmt(3) = "R"
vAlmt(4) = "L"

strSQL = "SELECT PSGL_Detail.* FROM PSGL_Detail;"
Set rs = CurrentDb.OpenRecordset(strSQL)
Open "TEXTFILE.TXT" For Output As #1
Do Until rs.EOF
For i = 0 To 4 'Assuming 5 fields
vSpaces = ""
vSpaceCount = vWidth(i) - Len(rs.Fields(i))
If vSpaceCount > 0 Then
For j = 1 To vSpaceCount
vSpaces = vSpaces & " "
Next
End If
Select Case vAlmt(i)
Case "L"
vLine = vLine & rs.Fields(i) & vSpaces
Case "R"
vLine = vLine & vSpaces & rs.Fields(i)
Case Else
MsgBox "Error in alignment declaration, field " & i + 1 _
& ". Alignment setting must be L or R."
GoTo Abort_Operation
End Select
Next
Print #1, vLine
rs.MoveNext
Loop
Abort_Operation:
Close #1
rs.Close
Set rs = Nothing
End Sub

HTH,
Nikos