Sub Test()
' Change file name to suit
Const FileName As String = "C:\TEMP\MyFile.txt"
Dim FileNo As Integer
Dim x As Long
Dim y As Integer
Dim TempArr() As Variant
Dim Txt
FileNo = FreeFile
Open FileName For Output As #FileNo
' Change sheet reference to suit
With Worksheets("Sheet1").Range("A1").CurrentRegion
ReDim Preserve TempArr(1 To .Columns.Count)
For x = 1 To .Rows.Count
For y = 1 To .Columns.Count
TempArr(y) = .Cells(x, y).Value
Next y
Txt = Join(TempArr, "|")
Print #FileNo, Txt
Next x
End With
Close #FileNo
End Sub
Sub Test()
Dim X As Long, FileNo As Long, FileText As String
[COLOR=#008000][B]' Change file name to suit[/B][/COLOR]
Const FileName As String = "C:\TEMP\MyFile.txt"
[COLOR=#008000][B]' Change sheet reference to suit[/B][/COLOR]
With Sheets("Sheet1").Range("A1").CurrentRegion
For X = 1 To .Rows.Count
FileText = FileText & vbCrLf & Join(Application.Index(.Rows(X).Value, 1, 0), "|")
Next
End With
FileNo = FreeFile
Open FileName For Output As #FileNo
Print #FileNo, Mid(FileText, 3)
Close #FileNo
End Sub[COLOR=#008000][/COLOR]
With Sheets("Sheet1").Range("A1").CurrentRegion
For X = 1 To .Rows.Count
FileText = FileText & vbCrLf & Join(Application.Index(.Rows(X).Value, 1, 0), "|")
I know this is an old thread, but it was recently referenced in more recent thread...
http://www.mrexcel.com/forum/excel-...ations-code-pasting-notepad-without-tabs.html
I just thought the readers of this thread might like to see a slightly different, slightly more compact macro which will do the same thing that Andrew's code does............
What you I need to change to this part of the code if my Range is A4:A87 only..Code:With Sheets("Sheet1").Range("A1").CurrentRegion For X = 1 To .Rows.Count FileText = FileText & vbCrLf & Join(Application.Index(.Rows(X).Value, 1, 0), "|")
Thanks
I know this is an old thread, but it was recently referenced in more recent thread...
I just thought the readers of this thread might like to see a slightly different, slightly more compact macro which will do the same thing that Andrew's code does...
Code:Sub Test() Dim X As Long, FileNo As Long, FileText As String [COLOR=#008000][B]' Change file name to suit[/B][/COLOR] Const FileName As String = "C:\TEMP\MyFile.txt" [COLOR=#008000][B]' Change sheet reference to suit[/B][/COLOR] With Sheets("Sheet1").Range("A1").CurrentRegion For X = 1 To .Rows.Count FileText = FileText & vbCrLf & Join(Application.Index(.Rows(X).Value, 1, 0), "|") Next End With FileNo = FreeFile Open FileName For Output As #FileNo Print #FileNo, Mid(FileText, 3) Close #FileNo End Sub
Do you mean the code in Message #3 above? If so, I did not need to do that when I tested the code before posting it.(P.s. I still had to make the minor mod in your last code of selecting the first cell in my sheet early on in the program to stop occasionally it not working (Maybe the problem is unique to me / my system))
Try changing it like this...What you I need to change to this part of the code if my Range is A4:A87 only..
Code:With Sheets("Sheet1").[COLOR=#FF0000][B]Range("A1").CurrentRegion[/B] [/COLOR] For X = 1 To .Rows.Count FileText = FileText & vbCrLf [COLOR=#FF0000][/COLOR]& Join(Application.Index(.Rows(X).Value, 1, 0), "|")
With Sheets("Sheet1").[COLOR=#FF0000][B]Range("A4:A87")[/B]
[/COLOR] For X = 1 To .Rows.Count
FileText = FileText & vbCrLf [COLOR=#FF0000][/COLOR]& Join(Application.Index(.Rows(X).Value, 1, 0), "|")
Const FileName As String = "J:\BRPR\PipeFile.txt"
With Sheets("Sheet1").Range("B6:B24").CurrentRegion
For X = 1 To .Rows.Count
FileText = FileText & vbCrLf & Join(Application.Index(.Rows(X).Value, 1, 0), "|")
Next
End With
FileNo = FreeFile
Open FileName For Output As #FileNo
Print #FileNo, Mid(FileText, 3)
Close #FileNo
Const FileName As String = "J:\BRPR\PipeFile.txt"
With Sheets("Sheet1").Range("B6:B24")
For X = 1 To .Rows.Count
FileText = FileText & vbCrLf & Join(Application.Index(.Rows(X).Value, 1, 0), "|")
Next
End With
FileNo = FreeFile
Open FileName For Output As #FileNo
Print #FileNo, Mid(FileText, 3)
Close #FileNo