New Line character in Excel VBA compatible with Notepad

Question

I am trying to use excel vba to create an ascii text file to be uploaded into an online application that requires a precise format.

Before I upload my ascii file to that application, I am trying to validate that my file has the correct format by viewing it in notepad.

My file seems to look ok in wordpad, but the support staff for this application I am trying to upload to are telling me I need to view the file in notepad to validate whether it is formatted correctly.

I have tried vbCr, vbLf, vbCrLf but I can't get my text to format correctly in notepad.

vbCr and vbLf seem to produce no line breaks. vbCrLf creates an unwanted space between lines.

Does anyone know how to create new lines  in excel vba that are compatible with notepad viewing?

I am using CreateTextFile and WriteLine.

Answer 1

Notepad is a basic text editor utility that comes with every Windows operating system and as such has some limitations; in your case this would be the 1024 character line limitation. This does not mean that there is a problem with your TXT file. It simply means that Notepad does not have the internal capacity to properly display individual lines longer than 1024 characters.

Use a good free alternate text editor for jobs that will include lines longer than 1024 characters. Personally, I use NotePad++, a pseudo-programming editor with many features not found in Windows' Notepad.

NotePad++

FWIW, that 1024 limit was not chosen at random. One way of looking at it is that ASCII has 256 characters and 1024 is 256 × 4 or 256 × 2 × 2. 256 can also be expressed as 2⁸.

Answer 2

Well, I'm not sure just what this shows/proves, but if nothing else, it shows that notepad, in full screen mode can't display lines of 1470 characters on a single line.  But that makes sense.

I put the following code into a workbook, saved it (so it would have a path to find in the code) and ran it

Sub WriteLongLinesToTextFile()

  'this will create a text file in the same folder that

  'you have saved this file into named LongText.txt

  'Each line will have been written with 1,470 characters,

  '52 lines will have been written (a-z, A-Z)

  'it will echo the output to the first sheet in the workbook

  '

  Dim filePath As String

  Dim textToWrite As String

  Dim LC As Integer

  Dim bufferNumber As Integer

  Dim myWS As Worksheet

  Dim rowPtr As Long

  Set myWS = ThisWorkbook.Worksheets(1)

  myWS.Cells.ClearContents

  rowPtr = 1

  filePath = ThisWorkbook.Path & "/LongText.txt"

  bufferNumber = FreeFile()

  Open filePath For Output As #bufferNumber

  Application.ScreenUpdating = False

  For LC = Asc("a") To Asc("z")

    textToWrite = String(1470, Chr$(LC))

    Print #bufferNumber, textToWrite

    myWS.Cells(rowPtr, 1) = textToWrite

    rowPtr = rowPtr + 1

  Next

  For LC = Asc("A") To Asc("Z")

    textToWrite = String(1470, Chr$(LC))

    Print #bufferNumber, textToWrite

    myWS.Cells(rowPtr, 1) = textToWrite

    rowPtr = rowPtr + 1

  Next

  Close #bufferNumber

  MsgBox "File created"

End Sub

After running it, I opened the created text file with Notepad and here's a screen shot of the right edge of the Notepad window showing the first several rows of data in it:

There don't appear to be any actual extra line feeds in the file, the text just had to wrap in order to be completely displayed.  Copying one of the 'full' lines before it wrapped confirms that those are 1,024 characters long.

Another interesting thing about it is that when displayed in a proportional font, some of the lines appear longer than others, for example, 1024 W characters are much wider than anything else!  Q comes in close behind, and because of that, other text streams appear to terminate mid-page, when they are all terminating at the 1024th character.

Answer 3

"Perhaps Notepad may also need the new line character to be Chr(10) & Chr(13)?"

I would not worry about it -- your 3rd party vendor has said Notepad works, so they are looking for the end of line marker used by Notepad, whether it's 10&13, 10 only, 13 only or even 0.  However, if you look at a .txt file such as have been created so far and that are readable with Notepad with a hex editor, you will find that the end of line characters are indeed Chr(13) followed by Chr(10)  (hex 0D, 0A).

I ran TasosK's code and looked at the created .txt file and it appeared similar to the one I created earlier.  The differences between ANSI text file and Unicode text file are illustrated in the graphics below.

What I do find interesting is that the ADODB created Unicode file has 2 extra characters right at the beginning of it that are read with you do a Line Input # operation with the file, and I'm not sure how your 3rd party processing will deal with them: may ignore them, may use them to realize it's a Unicode file, or get confused completely - I simply do not know.

Here are some screen shots from both types of file, first one of each pair shows the very beginning of the .txt file, the second one shows where the line breaks are between 1st line and 2nd.

start of file written in vba using Print #

Showing the line break within the Print # created file

The start of the ADODB created file, also note the 00 bytes between each character

And the line break character within the ADODB created file.

I think you're overthinking the issue - I'm betting the "standard" notepad file will be just fine.  Your fear is over the formatting of the output in it.  And that's kind of analogous to using a number format in a cell in Excel:  type .250663 into a cell and it looks like that, change the format to $ and it become $ 0.25; change the format to % and it becomes 25% and change it to one of the time formats and it becomes 6:00:57 AM.  But behind it all is still that number: .250663

Answer 4

I ended up using vbCrLF or equivalently Chr(13) & Chr(10) at the end of each line, including the last line and the very wide file was accepted by this particular application I was trying to upload to. In my VBA code, I used Write and added the end of line codes to my VBA code on my own, but in hindsight it appears that I could have called WriteLine repeatedly and not added any end of line codes.

It turns out that Notepad made it appear that there were extra spaces between rows because I had a lot of empty spaces at the end of each of my very long  (1470 character) lines which Notepad was wrapping without my knowledge. If I used Chr(10) or Chr(13) alone as the end of line character, Notepad would not recognize these as line breaks at all, but I never did test out this external application to see whether it would accept my file with just Chr(10) or Chr(13). Wordpad made my file look fine regardless of the end of line characters I used. Thanks for the information about the hex editor. Still not sure I understand the interchangeability between unicode and ascii files but I will certainly refer back to the code samples provided and all the helpful responses to this post if I need to create a text file in the future. Very grateful to everyone who contributed responses.

Answer 5

"Thanks for the information about the hex editor. Still not sure I understand the interchangeability between unicode and ascii files ..."

Actually, to be honest, neither am I.  That's why I said I'm not at all certain how the receiving end of your process would 'see' the Unicode file and how it would deal with those zero bytes.

Thanks for the feedback and for choosing my post as 'Answer'.  I believe that I'll also go mark, I believe it was Jeeped's post as answer - the one that first put us on to the 1024 character limit of Notepad itself.  I think that was an important step in this.  Yes, MSFT has granted me that power (I can mark others, just not my own :) )

There are also a lot that certainly fall into the 'Found this helpful' category also.