Creating a text file in VBA from a column in Excel2016

[Pianoman23]

Hi
I'm sure this is simple, but it's driving me mad at the moment.

I have a column of data that I need to convert to a text file.

The code I'm using is:
Set rng = ActiveSheet.Range("O5", "O40000") 'this is the range which will be written to text file
stFilename = "E:\Rajja\TxtFiles\AAH.txt"
stSeparator = "|"
stEncoding = "UTF-8" 'e.g. "UTF-8", "ASCII"
'-------------------------------------------------------------------------------------

For lRow = 1 To rng.Rows.count
If rng.Columns.count = 1 Then
stNextLine = rng.Rows(lRow).Value
Else
stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
End If
If stOutput = "" Then
'stOutput = stNextLine
Else
stOutput = stOutput & vbCrLf & stNextLine
End If
Next lRow

Set fso = CreateObject("ADODB.Stream")
With fso
.Type = 2
.Charset = stEncoding
.Open
.WriteText stOutput
.SaveToFile stFilename, 2
End With
Set fso = Nothing


The problem I'm having is that because the first 140 cells are empty, the text file starts with the first filled cell and I want to include the empty cells. What am I missing?

[shg]

Please take a few minutes to re-read the forum rules, and then edit your post to add CODE tags.

Thanks.

[AlphaFrog]

Please Login or Register  to view this content.