Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Texfile Help needed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Texfile Help needed
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Texfile Help needed
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" wrote in message ... 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Texfile Help needed
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. Edwin Niemoller wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Help Needed | New Users to Excel | |||
Excel help needed. | Excel Discussion (Misc queries) | |||
Excel VBA Help Needed | Excel Discussion (Misc queries) | |||
Excel Help Needed!!! | Excel Worksheet Functions | |||
Excel Help Needed | Excel Worksheet Functions |