Unwanted CR LF character when using Chr(26)

[petko]

Hi,

I'm using a form to collect the actual bank transfer data from the user and at submit the code prepares the requested string that is converted to a text file, that will be imported into the bank terminal.
After having prepared the string I write it into a text file:

Open TxtFileName For Output As #FileNum
Print #FileNum, StrBankTransfer
Close #FileNum

After this I have an unwanted CR LF character at the end of the text file causing error during importing it into the bank terminal . These are definitely not comming from the string but are added during the Print command.

Do you have any idea how I could avoid this or get rid of it with from code?

Appreciating any suggestion

petko

 

[JHB]

I think that explain it:
Unlike the Print # statement, the Write # statement inserts commas between items and quotation marks around strings as they are written to the file. You don't have to put explicit delimiters in the list. Write # inserts a newline character, that is, a carriage return-linefeed (Chr(13) + Chr(10) ), after it has written the final character in outputlist to the file.
Source document:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/writestatement

 

[petko]

Thanks for your suggestion. I tried to Write statement, however the only difference was that it inserted a quotation mark at the beginning and end, which is also not wanted. And the CR LF was still there that I'm trying to avoid.

I would appreciate any further ideas.

many thanks

petko

 

[gemma-the-husky]

does your strBanklTransfer END with a control character?

 

[Solo712]

Try appending a semi-colon.

Print #FileNum, StrBankTransfer[COLOR="Red"];[/COLOR]

Best,
Jiri

 

[gemma-the-husky]

Hmm. I just created a text file (a csv file) in a different project, and there is a vbCrLf at the end of the last row.

Are you sure that's producing an error, as it's a standard feature. If you edit it out manually does it fix it? Are you sure you aren't putting your own control character into the print line as well?

 

[The_Doc_Man]

OK, just for clarification, I can think of three different interpretations and want to clarify which one is being used. CHR$(26) is ASCII "SUB" for decimal 26 or ASCII "SYN" for octal 26, and it is the Ampersand character ("&") for hexadecimal 26.

If it happens that you really meant CHR$(26) with decimal 26, then that character represents a "substitution" marker. I.e. ASCII SUB is standing in place of a character that cannot be represented by some other ASCII character. I don't know enough about VBA "WRITE" statements to be sure because I always used PRINT statements, which behave differently.

 

[gemma-the-husky]

I also meant to comment on chr(26)

vbCrLf (Carriage Return - LineFeed) is chr(13) And chr(10). (which is what you described) - and I assumed your reference to chr(26) was just a mistype - but are you really putting a chr(26) in your "printed" line?

 

[petko]

According to the bank's requirement strBankTransfer ends with Chr(26), the Substitution character.

After using Print statement the text file is created. Checking the text file with a help of an editor Notepad++ I find the Sub character at the end and following it the unwanted CR LF character that is definitely created during Print - it is not in strBankTransfer.
Having this CR LF at the end of the file the bank terminal rejects the import with error message.

As soon I delete CR LF manually from the text file the import runs fine.

 

[rpeare]

Have you tried using filesystemobject commands instead?

with filesystemobject you should be able to define your own end of line character

Sub main()

Dim f
Dim fs

Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.createtextfile(Replace(CurrentProject.Path & "\", "\\", "\") & "TEST.txt", True)

f.write "this is a test" & Chr(26)
f.write "omg now what" & Chr(26)

f.Close
Set fs = Nothing

End Sub

 

[gemma-the-husky]

@petko

it's hard to resovle, I imagine, since print # will always oput the crlf at the end of a line.

I can't even think you could edit the text file automatically, becuase when you print the output back, the crlf at the end will re-appear.

You might be able to do it with write, but I am not sure. Write is really used to write records, rather than text. you could probably assemble the records with a trailing crlf on all but the last, and a trailing chr(26) on the last, and then write them out to the file.

 

[JHB]

..
As soon I delete CR LF manually from the text file the import runs fine.

How do you delete it, do you save the file afterwards?

 

[JHB]

I think rpeare had the solution with the Filesystemobject. Looking at the output it looks perfect.

 

[gemma-the-husky]

@JHB

I think you are right. I think he wanted this though

f.write "this is a test" & Chr(13) & chr(10)
f.write "this is another test" & Chr(13) & chr(10)
f.write "omg now what" & Chr(26)

So you only get chr(26) on the last one, which is why print # wasn't working.

He can do this your way, and I imagine he could also do it directly with wrte, rather than print #, but I hadn't tested it.

 

[rpeare]

You may be right about it being an end of file character gemma, even so filesystemobject would still work you'd just use 'writeline' for everything except the last line and 'write' for the last line

 

[The_Doc_Man]

Actually, <SUB> could also be an <EOF> character. In my younger days, CTRL/Z (which IS Chr$(26)) WAS <EOF> when you were inputting text to a TELETYPE(TM)-oriented text editor. So if that is an older app that requires it, it suddenly makes sense. They call it SUB but it used to be EOF.

 

[JHB]

..
and I imagine he could also do it directly with wrte, rather than print #, but I hadn't tested it.

No - I just tested it.

 

[petko]

@rpeare,

Filesystemobject solved my problem entirely.
Thanks a lot!

 

[rpeare]

mark the thread solved!