Excel to Texfile Help needed

[Edwin Niemoller]

Hello group,

I'm creating a textfile from Excel that needs to be imported by a third
party.

code that writes the file

fStatus ("Opening Export File")
Close #1
Open "theFile.txt" For Output As #1
Print #1, header
fStatus ("Writing Export File")
For Each cell In Selection
If cell.Value <> "no entry" Then
Print #1, cell.Text
End If
Next cell
Print #1, trailer
fStatus ("Closing Export File")
Close #1

The routine works like a charm. The third party is complaining about the Hex
0A and Hex 0D (CR & LF) at the end of each record because it's making the
record length too long for his import process.
They claim that they need only one character and advised me to use the
command unixTOdos the realize this

Questions

- anyone have a clue how to approach this?
- how can i replace CHR(10)&CHR(13) with just CHR(10) after the file is
created
- is there a direct way to avoid the chr(10) & chr(13) to be written at the
end of each record from VBA?


Thanks a bunch

Edwin

 

[Dave Peterson]

There are a lot of UnixToDOS and DOSToUnix converters out there. You may want
to visit www.shareware.com to find one. (You may find it useful for other
things.)

But something like this may help you get started:

Option Explicit
Sub testme()

Dim Cell As Range
Dim Header As String
Dim Trailer As String

Header = "This is the header"
Trailer = "This is the trailer"

' fStatus ("Opening Export File")
Close #1
Open "theFile.txt" For Output As #1
Print #1, Header; vbLf;
' fStatus ("Writing Export File")
For Each Cell In Selection
If Cell.Value <> "no entry" Then
Print #1, Cell.Text; vbLf;
End If
Next Cell
Print #1, Trailer;
' fStatus ("Closing Export File")
Close #1


End Sub

 

[Edwin Niemoller]

Dave,

Although the vbLf is added to the Print # statement it is still resulting in
Hex 0A and 0D at the end of each record where I hoped it would only generate
Hex 0A [ LF - Chr(10) ]

The VBA help file suggests that only the Write # statement would ad both
control characters


Further thoughts are appreciated

Edwin

 

[Dave Peterson]

You sure?

I looked at the output in my favorite text editor (UltraEdit) and viewed it in
hex mode and it looked ok.

Although, I'm not sure how a record is defined.

I got each cell on a different line but with only the LF at the end of the line.

Maybe the viewer you used converted the LF to CRLFs.

Dave,

Although the vbLf is added to the Print # statement it is still resulting in
Hex 0A and 0D at the end of each record where I hoped it would only generate
Hex 0A [ LF - Chr(10) ]

The VBA help file suggests that only the Write # statement would ad both
control characters

Further thoughts are appreciated

Edwin

There are a lot of UnixToDOS and DOSToUnix converters out there. You may want
to visit www.shareware.com to find one. (You may find it useful for other
things.)

But something like this may help you get started:

Option Explicit
Sub testme()

Dim Cell As Range
Dim Header As String
Dim Trailer As String

Header = "This is the header"
Trailer = "This is the trailer"

' fStatus ("Opening Export File")
Close #1
Open "theFile.txt" For Output As #1
Print #1, Header; vbLf;
' fStatus ("Writing Export File")
For Each Cell In Selection
If Cell.Value <> "no entry" Then
Print #1, Cell.Text; vbLf;
End If
Next Cell
Print #1, Trailer;
' fStatus ("Closing Export File")
Close #1


End Sub