Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing File in VBA with Unix line break
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing File in VBA with Unix line break
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing File in VBA with Unix line break
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. 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing File in VBA with Unix line break
"Steven Drenker" wrote in message
t... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a line break writing text within a cell? | Excel Discussion (Misc queries) | |||
Break cell into multiple lines by line break | Excel Discussion (Misc queries) | |||
Writing a line in to a file | Excel Programming | |||
read a Unix binary file | Excel Programming | |||
Unix Directory/File Listing | Excel Programming |