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

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...62&Product=xlw
Working with Sequential Access Files


--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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

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


"Tom Ogilvy" wrote in message
...
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...62&Product=xlw
Working with Sequential Access Files


--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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

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" wrote in message
...
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...62&Product=xlw
Working with Sequential Access Files


--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
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...62&Product=xlw
Working with Sequential Access Files


--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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

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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
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...62&Product=xlw
Working with Sequential Access Files


--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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

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...42&Product=vb6
How To Write Data to a File Using WriteFile API

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
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...62&Product=xlw
Working with Sequential Access Files


--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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,

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

RBS

"Tom Ogilvy" wrote in message
...
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...42&Product=vb6
How To Write Data to a File Using WriteFile API

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
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...62&Product=xlw
Working with Sequential Access Files


--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
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