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.

 

[Plokimu77]

Thank you.

 

[Blade Hunter]

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

Where were you 5 years ago mate, I could have really done with this lol. I have been using this horrible code I wrote all this time:

Sub SaveCopy()
    'This savecopy routine will output to a pipe delimited file, good for bulk inserting into an Oracle DB
    Dim vFileName As Variant
    Dim rngLastCell As Range
    Dim lLastRow As Long
    Dim nLastCol As Integer
    Dim lCurrRow As Long
    Dim nCurrCol As Integer
    Dim sRowString As String
    Dim ArchiveFolder As String
    ArchiveFolder = "C:\Temp\"
    Application.DisplayAlerts = False
    vFileName = ArchiveFolder & "Spins_" & Format(Now(), "YYYYMMDDHHMMSS") & ".txt"
    Open vFileName For Output As #1
    Set rngLastCell = ActiveSheet.Range("A1").SpecialCells(xlLastCell)
    lLastRow = Range("A" & Rows.Count).End(xlUp).Row
    nLastCol = Range("XFD1").End(xlToLeft).Column
    For lCurrRow = 1 To lLastRow
        sRowString = ActiveSheet.Cells(lCurrRow, 1).Formula
        For nCurrCol = 2 To nLastCol
            sRowString = sRowString & "|" & ActiveSheet.Cells(lCurrRow, nCurrCol).Formula
        Next nCurrCol
        If Len(sRowString) = nLastCol - 1 Then
            Print #1,
        Else
            Print #1, sRowString
        End If
    Next lCurrRow
    Close #1
    ActiveWindow.Close False
    Application.DisplayAlerts = True
End Sub

 

[DocAElstein]

Where were you 5 years ago mate, I could have really done with this lol. I have been using this horrible code I wrote all this time:
........

… he was probably doing then wot he is doing Now: Churning out good codes AND updating continually old ones. And good for us that he does…lurking through his old stuff is always worth it

 

[blondeambition]

I am sorry, but my answer in Message #9 has led this thread astray. If all you want to do is concatenate a single column of contiguous cells together to form a pipe delimited text string, you can do that with one line of code...

PipeDelimitedText = Join(Application.Transpose(Sheets("Sheet1").Range("A4:A87").Value), "|")

Hi Rick, would concatenation be the answer if I wanted to wrap rows? For instance, if the range was A1:Z20.

 

[Rick Rothstein]

Hi Rick, would concatenation be the answer if I wanted to wrap rows? For instance, if the range was A1:Z20.

Yes, something like this...

For Col = 1 To 26
  PipeDelimitedText = PipeDelimitedText & "|" & Join(Application.Transpose(Sheets( _
                      "Sheet3").Range("A1:A20").Offset(, Col - 1).Value), "|")
Next

 

[blondeambition]

Yes, something like this...

For Col = 1 To 26
  PipeDelimitedText = PipeDelimitedText & "|" & Join(Application.Transpose(Sheets( _
                      "Sheet3").Range("A1:A20").Offset(, Col - 1).Value), "|")
Next

Thanks Rick. I'm having trouble with this line of code I think b/c I need to indicate a valid file name.

Open FileName For Output As #FileNo

The problem is, I just want to have the data pasted to a text file on the desktop (so anyone can use this).

Dim X As Long, FileNo As Long, FileText As String
    '  Change file name to suit
    Const FileName As String = "[B]Desktop:\Testfile.txt[/B]"
    '  Change sheet reference to suit
        With Sheets("NTFile").Range("A1:EX20")
            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

Thoughts?

 

[Blade Hunter]

Use the environment variables:

Const FileName As String = environ("HOMEDRIVE") & Environ("HOMEPATH") & "\Desktop\Testfile.txt"

The system doesn't understand just "Desktop"

If that doesn't work your Environment variable are different to mine. Just go to a command prompt (Windows key - R, Type CMD and press enter) then type Set and press enter. Scroll through what is in there to find the details you need and they are the variable you sub into my code above.

 

[Rick Rothstein]

Use the environment variables:

Const FileName As String = environ("HOMEDRIVE") & Environ("HOMEPATH") & "\Desktop\Testfile.txt"

You cannot use functions when assigning values to constants in a Const statement. FileName will have to be Dim'med as a String variable. Once that is done, you can use this to assign the path/filename to it...

Dim FileName As String
FileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Testfile.txt"

Note the backslash in front of the file name... that is required as the path from CreateObject("WScript.Shell").SpecialFolders("Desktop") does not end with one.

 

[Blade Hunter]

You cannot use functions when assigning values to constants in a Const statement. FileName will have to be Dim'med as a String variable. Once that is done, you can use this to assign the path/filename to it...

I have once again learned something today .

I have never actually used constants to be honest, I always use string variables (I don't know why).

Thanks for the info Rick

 

[Rick Rothstein]

I have once again learned something today .

I have never actually used constants to be honest, I always use string variables (I don't know why).

The reason is that VB physically substitutes the constant's value for the constant's name throughout the program prior to compiling it... since the program is not yet compiled at this point, function evaluation cannot occur.