write one column range to text avoiding final linebreak

[RB Smissaert]

I need to write a one-column range to a text file.
This text file has to be all the cells separated by linebreaks.
So it has to come out like this:

Cell1
Cell2
Cell3
etc.

The problem I have is that I can't avoid the text file ending with a
linebreak.
So in the above example when the cursor in the text file is after Cell3, you
can
still move one line further by pressing the down key.
This last linebreak is causing problems when the textfile will be written
back to an array.

Thanks for any advice on this.


RBS

 

[Tom Ogilvy]

Why not post the code, then we don't have to go from scratch. I assume you
are not using SaveAs, but are using low level file IO.

If not look at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;151262&Product=xlw
Working with Sequential Access Files

 

[RB Smissaert]

I tried several different methods. This is one of them:

Sub SaveColumnToText(ByVal txtFile As String, _
ByRef arr As Variant, _
Optional ByVal UBRow As Long = -1)

Dim R As Long
Dim hFile As Long

If UBRow = -1 Then
UBRow = UBound(arr)
End If

hFile = FreeFile

Open txtFile For Output As hFile

For R = 1 To UBRow
Write #hFile, arr(R, 1)
Next

Close #hFile

End Sub


RBS

 

[RB Smissaert]

Just found one method that seems to do what I want:

Sub ColumnRangeToText(ByRef rngCol As Range, ByVal strFile As String)

Dim strRange As String
Dim arr
Dim LR As Long
Dim i As Long
Dim hFile As Long

arr = rngCol
LR = UBound(arr)

For i = 1 To LR - 1
strRange = strRange & arr(i, 1) & vbCrLf
Next

strRange = strRange & arr(LR, 1)

hFile = FreeFile
Open strFile For Output As hFile

Print #hFile, strRange;
Close #hFile

End Sub


RBS

 

[Tom Ogilvy]

Sub SaveColumnToText(ByVal txtFile As String, _
ByRef arr As Variant, _
Optional ByVal UBRow As Long = -1)

Dim R As Long
Dim hFile As Long
Dim sStr as String

If UBRow = -1 Then
UBRow = UBound(arr)
End If

hFile = FreeFile

Open txtFile For Output As hFile

For R = 1 To UBRow - 1
Write #hFile, arr(R, 1)
Next
sStr = Trim(arr(UBRow, 1).Text)
If Not IsNumeric(sStr) Then _
sStr = Chr(34) & sStr & Chr(34)
Print #hFile, sStr;
Close #hFile

End Sub

You may have to add some more if statements/code to get sStr to behave the
same as Write would (for dates perhaps), but this doesn't put in the vbCrLf

 

[RB Smissaert]

Thanks.
I think I figured something out that will work for my particular situation.
I take it you put the quotes in to avoid problems with linebreaks within
the array element.
In my situation element one is a string, but never has linebreaks.
The other elements are all integer numbers.

To get the text file back to the same array I now have:

Function OpenTextFileToString2(ByVal strFile As String) As String

Dim hFile As Long
hFile = FreeFile

Open strFile For Input As #hFile

OpenTextFileToString2 = Input$(LOF(hFile), hFile)

Close #hFile

End Function

Function OpenTextFileToArray3(ByVal txtFile As String) As Variant

'THIS ONE IS FOR ONE-COLUMN 2-D ARRAYS
'--------------------------------------------
'where the text file has strings or values
'separated by linebreaks, this is for example
'the text file produced by a sheet ID import
'will produce a 1-based 2D, one column array
'as a one column sheet range
'--------------------------------------------
Dim str As String
Dim arr
Dim arr2
Dim i As Long

str = OpenTextFileToString2(txtFile)

arr = Split(str, Chr(13) & Chr(10))

ReDim arr2(1 To UBound(arr) + 1, 1 To 1)

For i = 0 To UBound(arr)
arr2(i + 1, 1) = arr(i)
Next

OpenTextFileToArray3 = arr2

End Function

And that seems to work as well as the conditions are as above.


RBS

 

[Tom Ogilvy]

No, I put the quotes in because that is what Write does. I assumed you
wanted the file to look the same as your original code, but perhaps not.
Since you say everything after the first is an integer, then it doesn't make
any difference in my case. For your solution, the first element will not
be written as it was previously, but again, may not make a difference.

the easier fix would have been

Function OpenTextFileToString2(ByVal strFile As String) As String

Dim hFile As Long
hFile = FreeFile

Open strFile For Input As #hFile

sStr = Input$(LOF(hFile), hFile)
OpenTextFileToString2 = Left(sStr,len(sStr)-2)

Close #hFile

End Function

However, if you data starts life as an array, you might skip a lot of your
machinations with this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;165942&Product=vb6
How To Write Data to a File Using WriteFile API

 

[RB Smissaert]

Tom,

Thanks for that, that is an interesting one.
Will have a look at that and I am sure
it will come very useful.

RBS