Writing File in VBA with Unix line break

[Steven Drenker]

Using Mac, OS X 10.3.9, Excel X for Mac.

I need to write a text file in VBA with a Unix line break (ASCII 10 / x0A).
I'm iterating over a range of cells and writing out lines with:

Print #FileNum, Cell.Text & Chr(10)

Anyway, when I get my file written out, it's not performing as I expected. I
did a hex dump with TextWrangler and found that I'm not getting a linefeed
character written out. Here's a sample hex line:

0050: 00 0D 00 0D 00 64 00 69 00 67 00 20 00 31 00 39 .....d.i.g. .1.9

The first two characters should be a single line feed (x0A), but I'm getting
a double carriage return (x00 x0D).

Two questions:
1. It looks like Excel is putting out double-byte ASCII (all the extra 00 in
front of the actual ASCII characters). Is this correct?

2. Why am I getting ASCII 13 / x0D when I'm explicitly writing out ASCII 10
/ x0A?
-------------------

P.S. I discovered that some common Mac VBA constants are not as expected:
vbCr 13
vbCrLf 13
vbFormFeed 12
vbLf 13
vbNewLine 13
vbNullChar 0
vbObjectError 45
vbTab 9

 

[LenB]

I can only test this on a PC with Excel97, but I expect it is the same
on the Mac. Try a trailing semicolon, like
Print #FileNum, Cell.Text & Chr(10);
On mine, that suppresses the usual CR LF. Looks like you are creating
unicode text with the extra zero bytes. Is that a Mac thing too?

Len

 

[Steven Drenker]

Thanks, Len. I'll try the trailing semicolon.

Yes, I figure that's Unicode with the extra leading x00. I can't figure out
how to suppress it. It doesn't seem to hurt.

 

[Howard Kaikow]

Using Mac, OS X 10.3.9, Excel X for Mac.

I need to write a text file in VBA with a Unix line break (ASCII 10 / x0A).
I'm iterating over a range of cells and writing out lines with:

Print #FileNum, Cell.Text & Chr(10)

Anyway, when I get my file written out, it's not performing as I expected. I
did a hex dump with TextWrangler and found that I'm not getting a linefeed
character written out. Here's a sample hex line:

0050: 00 0D 00 0D 00 64 00 69 00 67 00 20 00 31 00 39 .....d.i.g. .1.9

The first two characters should be a single line feed (x0A), but I'm getting
a double carriage return (x00 x0D).

Two questions:
1. It looks like Excel is putting out double-byte ASCII (all the extra 00 in
front of the actual ASCII characters). Is this correct?

2. Why am I getting ASCII 13 / x0D when I'm explicitly writing out ASCII 10
/ x0A?
-------------------

P.S. I discovered that some common Mac VBA constants are not as expected:
vbCr 13
vbCrLf 13
vbFormFeed 12
vbLf 13
vbNewLine 13
vbNullChar 0
vbObjectError 45
vbTab 9

If the Mac has the above values, then that's a serious bug.

vbLF should have the decimal value 10.

If your code is not explicitly causing the x0D, then there's an additional
bug somewhere.

The following works as expected in Excel 2003, and is using single byte
characters.

Option Explicit

Private Sub TestvbLF()
Dim FileNum As Integer

FileNum = FreeFile
Open "d:\XYZ.txt" For Output As #FileNum
Print #FileNum, "Pizza is yummy!" & Chr(10) & "So are bagels!"
Close #FileNum
End Sub