Pipe delimited files

[JMFW]

Hello

I have been asked to save an excel file using the pipe deliminitor. Can anyone tell me how I do this please.

 

[Andrew Poulsom]

Try:

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

 

[Rick Rothstein]

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...

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]

 

[Plokimu77]

What you I need to change to this part of the code if my Range is A4:A87 only..

With Sheets("Sheet1").Range("A1").CurrentRegion
    For X = 1 To .Rows.Count
      FileText = FileText & vbCrLf & Join(Application.Index(.Rows(X).Value, 1, 0), "|")

Thanks

 

[DocAElstein]

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............

Hi Rick
. I already have many alternative codes collected from You for reading and writing from Excel to simple Text Files. And now another: slicing up rows as it were and sticking them together with a carriage return.
. And my first introduction to Application.Index.
. I find the threads where multiple answers are given particularly useful.
. You may remember another one along these lines that we were both in which may also benefit future readers of this thread to reference here:
http://www.mrexcel.com/forum/excel-...ll-one-cell-need-comma-delimit-instead-4.html
Thanks so much for taking time “updating” old Threads.
Alan
(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))

 

[DocAElstein]

What you I need to change to this part of the code if my Range is A4:A87 only..

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 expect Rick's code would simply need to be modified so that "A1" is replaced with "A4". (Note the periphery of Range A4:A87 must be empty or the ,CurrenReegion Property will capture anything else bordering on this Range).
. But as you are considering just one column, there may be some way to use the Application.Index in conjunction with a code for writing it a text file function to just select and write that column in one go
……..

 

[blondeambition]

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...

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

What filename would I use? The same filename the macro is saved under?

 

[Rick Rothstein]

(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))

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.

 

[Rick Rothstein]

What you I need to change to this part of the code if my Range is A4:A87 only..

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), "|")

Try changing it like this...

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), "|")

 

[NewOrderFac33]

Rick,

Any suggestion as to why when I use .CurrentRegion, my code works fine

    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

but when I omit .CurrentRegion and use either an address range or a range name, I get the error message "Runtime Error 13: Type Mismatch"?

    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

Pete