I made a mistake by assuming that the file you wanted to write to, exists ( Assumption = the mother of all FU).
- Added testing if the file exists and creating if not.
The code attempts to makes a ADODB connection with the file, so it must be existing.....
Sub testText()
Dim str As String
Dim fsT, tFilePath As String
Dim i As Long
Dim fs, obj As Object
tFilePath = "H:\001ExcelForum\2014\jan\30\TextFile3.txt"
'check if file exists
If (Dir$(tFilePath, vbDirectory) = "") Then
Set fs = CreateObject("Scripting.FileSystemObject")
Set obj = fs.CreateTextFile(tFilePath, True)
obj.Close
Set fs = Nothing
End If
On Error Resume Next
Set fsT = CreateObject("ADODB.Stream")
fsT.Type = 2
fsT.Charset = "utf-8"
fsT.Open
'create text string
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
str = Cells(i, 11).Text & "^" & Cells(i, 12).Text & _
"^" & Cells(i, 2).Text & "^" & Cells(i, 14).Text & _
"^" & Cells(i, 15).Text & "^" & Cells(i, 16).Text & _
"^" & Cells(i, 17).Text & "|" & Cells(i, 18).Text & _
"|" & Cells(i, 19).Text & "|" & Cells(i, 20).Text & _
"^" & Cells(i, 21).Text & "^" & Cells(i, 22).Text _
& "^" & Cells(i, 8).Text & vbNewLine
fsT.writetext str
Next i
fsT.SaveToFile tFilePath, 2
fsT.Close
End Sub
Bookmarks