SOLVED

i need some help to understand line 23

Occasional Contributor

 

Dim fso As Scripting.FileSystemObject
    Dim ts As Scripting.TextStream
    Dim r As Range
    Dim ColumnCount As Integer
    Dim i As Integer
    
    Set fso = New Scripting.FileSystemObject
    
    ' Open for writing 
	Set ts = fso.OpenTextFile( _
        Environ("UserProfile") & "\Desktop\TestExcelFile.txt", ForWriting, True)
    
    ' activate Sheet1
	Sheet1.Activate
    
    ' determine number of columns to copy
	ColumnCount = Range("A1", Range("A1").End(xlToRight)).Cells.Count
    
    ' iterate through rows and columns
	For Each r In Range("A1", Range("A1").End(xlDown))
        For i = 1 To ColumnCount
            ts.Write r.Offset(0, i - 1).Value & vbTab
            If i < ColumnCount Then ts.Write vbTab
        Next i
        ts.WriteLine
    Next r
    
    ts.Close
    Set fso = Nothing

 

3 Replies
best response confirmed by adnan2004 (Occasional Contributor)
Solution

@adnan2004

The variable ts is a TextStream object in the Scripting library. It represents a plain text file.

The lines

	Set ts = fso.OpenTextFile( _
        Environ("UserProfile") & "\Desktop\TestExcelFile.txt", ForWriting, True)

create a new text file TestExcelFile.txt, ready to be written to (if there is already a file of that name, it will be removed first).

 

The code then loops through the rows and columns of the used range of Sheet1.

It writes the value of each cell in a row followed by a Tab character to the same line of the text file.

Line 23 adds an extra Tab character between those values.

For example, if you have a sheet looking like this:

a b c
1 2 3

the text file will look like this:

 

a<tab><tab>b<tab><tab>c<tab>

1<tab><tab>2<tab><tab>3<tab>

Thank you, Hans Vogelaar, you are amazing!
Now I know what this line of code does, but what is the point of adding another tab if the previous line does it after each cell?

@adnan2004 

I don't know, I would use

            ts.Write r.Offset(0, i - 1).Value
            If i < ColumnCount Then ts.Write vbTab