Unwanted CR LF character when using Chr(26) (1 Viewer)

petko · May 21, 2019

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 · May 21, 2019

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 · May 27, 2019

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 · May 27, 2019

does your strBanklTransfer END with a control character?

Solo712 · May 27, 2019

Try appending a semi-colon.

Code:

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

Best,
Jiri

gemma-the-husky · May 27, 2019

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 · May 27, 2019

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 · May 27, 2019

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 · Jun 1, 2019

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 · Jun 1, 2019

Have you tried using filesystemobject commands instead?

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

Code:

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 · Jun 1, 2019

@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 · Jun 1, 2019

petko said:
..
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 · Jun 1, 2019

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

gemma-the-husky · Jun 1, 2019

@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 · Jun 1, 2019

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 · Jun 2, 2019

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 · Jun 2, 2019

gemma-the-husky said:
..
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 · Jun 3, 2019

@rpeare,

Filesystemobject solved my problem entirely.
Thanks a lot!

rpeare · Jun 4, 2019

mark the thread solved!

Unwanted CR LF character when using Chr(26) (1 Viewer)

Registered User.

Have been here a while

Registered User.

Super Moderator

Registered User.

Super Moderator

Immoderate Moderator

Super Moderator

Registered User.

Registered User.

Super Moderator

Have been here a while

Have been here a while

Attachments

Super Moderator

Registered User.

Immoderate Moderator

Have been here a while

Registered User.

Registered User.

Similar threads

Users who are viewing this thread