Sep 05 2022 01:37 AM - edited Sep 05 2022 02:05 AM
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
Sep 05 2022 03:30 AM
SolutionThe 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>
Sep 06 2022 12:08 AM
Sep 06 2022 02:29 AM
I don't know, I would use
ts.Write r.Offset(0, i - 1).Value
If i < ColumnCount Then ts.Write vbTab
Sep 05 2022 03:30 AM
SolutionThe 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>