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

Without any idea of what it is that you are writing to a text file and by what method, I can only point you to http://www.excel-easy.com/vba/examples/write-data-to-text-file.html

Answer 4

Am I totally off base or doesn't WriteLine include its own end-of-line character(s) of Chr(13) & Chr(10) (aka vbCrLf)?

Write, WriteLine Functions