Removing a Carriage Return

[aziz rasul]

I have the following code behind a command button: -

Private Sub Command0_Click()

Dim rst As DAO.Recordset
Dim x As Integer
Dim intLen As Integer

Set rst = CurrentDb.OpenRecordset("Table2", dbOpenDynaset)

DoCmd.TransferText acExportDelim, "", "Table1", "D:\Table1.txt", False
DoCmd.TransferText acExportDelim, "", "Table2", "D:\Table2.txt", False

Open "D:\Table1.txt" For Append As #1
Open "D:\Table2.txt" For Input As #2

Do While Not rst.EOF
Print #1, rst!FirstName
rst.MoveNext
Loop

Close #1
Close #2

Kill ("D:\Table2.txt")
'Open "D:\Table1.txt" For Binary Access Write As #3
'Put #3, , Replace(EOF(3), vbCrLf, "")
'Put #3, , Replace(EOF(3), Chr(10) & Chr(13), "")
'Close #3

End Sub

How do I change the commented out lines to delete the last line in "D:\Table1.txt" to remove the carriage return?

db attached.

 

[Guus2005]

Using the immediate window you can see that echoing a semicolon ";" the cursor waits behind it. A comma "," represents a Tab.

So writing a line which ends with a semicolon should prevent entering a return. This doesn't remove the return, it prevents the return from showing up.

Enjoy!

 

[aziz rasul]

I'm not sure I have understood you.

The lines of data can't end in a semi-colon in the file. The created file is used to upload on the web and this is a strict requirement. Does that make sense?

 

[Guus2005]

Try this:

Do While Not rst.EOF
Print #1, rst!FirstName & ";" ' This semicolon was added!
rst.MoveNext

 

[aziz rasul]

I ended up with semi-colon's which I can't do but I still ended up with a carriage return. Thanks for trying.

 

[pdx_man]

You are going to have to calculate your position using the LOC keyword to know when you are on the last row, then remove the CR/LF.

 

[aziz rasul]

Thanks for that.

Assuming I can use the LOC function, how do I actually delete the last vbCrLf?

 

[Guus2005]

perhaps:

Print #1, Left$(strLine,loc(1)-1)

HTH

 

[aziz rasul]

I get run-time error 5 (Invalid procedure or argument) with the following code: -

Private Sub Command0_Click()

Dim rst As DAO.Recordset
Dim x As Integer

Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)

If Dir("D:\Table1.txt") <> "" Then
Kill ("D:\Table1.txt")
End If

Open "D:\Table1.txt" For Output As #1
For x = 1 To 2
Print #1, rst!FirstName
rst.MoveNext
Next x
Print #1, Left$(rst!FirstName, Loc(1) - 1) ' run-time error 5 here
Close #1

rst.Close
Set rst = Nothing

End Sub

I am testing the code with 3 records in a single field.

 

[Guus2005]

Ever tried the put command?

 

[aziz rasul]

I'll try that.

 

[pdx_man]

To suppress a CR, you need to have a semi-colon at the end of your print statement. I found an easier way to do this using the LOF instead of the LOC command:

Public Sub PDX_Man_Test()
Dim MyLocation, MyLine
' Open file just created.
Open "c:\Table1.txt" For Binary As #1
Open "c:\Table2.txt" For Output As #2
Debug.Print LOF(1)

    ' Read character into variable.
    MyLine = Input(LOF(1) - 2, #1)  'CR/LF, so you need to go back 2
    Debug.Print MyLine
        Print #2, MyLine;

Close #1    ' Close file.
Close #2
    
End Sub