Export to text with additional text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

After reviewing some previous posting on this, I tried using the following
code:
Private Sub Command17_Click()
Dim rsR As DAO.Recordset
Dim strline As String
Dim lngFN As Long
'Create empty text file
lngFN = FreeFile()
Open "C:\Documents and Settings\mjray\Desktop\mpedata.txt" For Output As
#lngFN
'Output Header
Print #lngFN, "(e-mail address removed)"
'Output ZipCodes
Set rsR = CurrentDb.OpenRecordset("AllZips")
While rsR.EOF <> True
Debug.Print rsR.Fields
Print #lngFN, rsR.Fields
Wend
'Close rsR
Close rsR
'Output EOF
Print #lngFN, "EOF"
Close #lngFN
End Sub

I wrote a query that just returns zip codes. I defined a specification file
which removes the quotes when the zip codes are exported. I need to add an
email address to the very first line of the text file and the EOF indicator
at the very last line of the text file. I can only seem to export the very
first record from teh recordset. I can't seem to export all of the records.

Thanks,
Mike
 
After reviewing some previous posting on this, I tried using the following
code:
Private Sub Command17_Click()
Dim rsR As DAO.Recordset
Dim strline As String
Dim lngFN As Long
'Create empty text file
lngFN = FreeFile()
Open "C:\Documents and Settings\mjray\Desktop\mpedata.txt" For Output As
#lngFN
'Output Header
Print #lngFN, "(e-mail address removed)"
'Output ZipCodes
Set rsR = CurrentDb.OpenRecordset("AllZips")
Click to expand...

Do Until rsR.EOF
Debug.Print rsR.Fields(0).Value
Print #lngFN, rsR.Fields(0).Value
'You forgot to move to the next record!
rsr.MoveNext
Loop
rsr.Close
'Output EOF
Click to expand...

It's very unusual these days to include an explicit EOF symbol in a
text file because all the common modern operating systems store the
exact length of the file. If you have to do it for other reasons the
standard characters are Ctrl-Z on Windows and Ctrl-D on Unix, Linux
and - I guess - modern Macintosh systems.

Print #lngFN, Chr(26) ; 'Chr(26) is Ctrl-Z. Semicolon
'is to suppress the CRLF that
'Print normally emits.
 
Thanks. That worked nicely. I am trying to remove the null values from
printing to the file. I tried the following but it continues to print null
values:
Do Until rsR.EOF
Debug.Print rsR.Fields(0).Value
If rsR.Fields(0).Value = "" Then
rsR.MoveNext
Else
Print #lngFN, rsR.Fields(0).Value
rsR.MoveNext
End If
Loop
 
You need to check for Null, not just for an empty string.

Do Until rsR.EOF
Debug.Print blah blah
If Len(Nz(rsr.Fields(0).Value, "")) > 0 Then
Print #lngFN, rsR.Fields(0).Value
End If
rsR.MoveNext
Loop
 
Back
Top