Pipe-delimited Text File Format

[mamun_ges]

Hi, I am using the below code to Generate a text File in pipe Delimited format.

Sub ExportTxt()

    Dim UsedRows As Long
    Dim UsedColumns As Long
    Dim R As Long, C As Long
  
    '// Define a suitable file name
    Open "E:\Folder\" & "TD.txt" For Output As #1
    With ActiveSheet
        UsedRows = .UsedRange.Rows.Count
        UsedColumns = .UsedRange.Columns.Count
        For R = 2 To UsedRows
            For C = 1 To UsedColumns
                Select Case C
                    Case 2              ' Numeric Column with 2 decimals
                        Print #1, Format(.Cells(R, C), "#.00"); "|";
                  
                    Case UsedColumns    ' Last Column end of line
                        Print #1, .Cells(R, C)
                  
                    Case Else
                        Print #1, .Cells(R, C); "|";
                  
                End Select
            Next C
        Next R
    End With

    Close #1

End Sub

But now I have to have a Pipe after the last line.
Like below (As an example)
ASD|DEPFR|2451|ASDFERTT|

What to change in the code to achieve the expected result.
Any suggestion?

Thanks and Best regards.

 

[mamun_ges]

Thanks All for the response.

Yes, The pipe worked at the last used column but it breaks the line. (See Test image)
The line should be maintained properly same as the excel file. (See the expected Result Image)

Here I don't get any option to attach the file.
I am attaching a Sample File to download the file from my google drive. Hope It helps understand my worksheet.


Thanks and best regards

 

[Alex Blakenburg]

Can you post the code you tried.
(The whole routine like in your original post)

 

[Alex Blakenburg]

I specifically asked that you COPY in the code I changed since you said it did not work previously.
You have not done so. You have added a semi-colon to the end of the line of code I provided which is what is causing the text output be continuous without a line break.

How about you just replace your entire code with this:

    Dim UsedRows As Long
    Dim UsedColumns As Long
    Dim R As Long, C As Long
 
    '// Define a suitable file name
    Open "E:\Upload Folder\" & "TD.txt" For Output As #1
    With ActiveSheet
        UsedRows = .UsedRange.Rows.Count
        UsedColumns = .UsedRange.Columns.Count
        For R = 2 To UsedRows
            For C = 1 To UsedColumns
                Select Case C
                    Case 2              ' Numeric Column with 2 decimals
                        Print #1, Format(.Cells(R, C), "#.00"); "|";
              
                    Case UsedColumns    ' Last Column end of line
                        Print #1, .Cells(R, C); "|"
              
                    Case Else
                        Print #1, .Cells(R, C); "|";
              
                End Select
            Next C
        Next R
    End With

    Close #1

End Sub

Result

 

[Marc L]

According to the post #11 an easy VBA demonstration for starters :​

Sub Demo1()
  Const D = "|", T = "E:\Upload Folder\TD.txt"
    Dim F%, R&
        F = FreeFile
        Open T For Output As #F
    With [A1].CurrentRegion.Rows
        For R = 2 To .Count
            Print #F, Join(Application.Index(.Item(R).Value, 1, 0), D); D
        Next
    End With
        Close #F
        Shell "Notepad " & T, 1
End Sub

 

[mamun_ges]

Thanks

Alex Blakenburg​

For the correction of the code.

With Best Regards

 

[Marc L]

The correction of the initial VBA procedure - revamped without the useless ! - in case the cells formatting must be exported too :​

Sub ExportTxt()
    Dim F%, R As Long, C%
        F = FreeFile
        Open "E:\Folder\TD.txt" For Output As #F
    With [A1].CurrentRegion
        For R = 2 To .Rows.Count
            For C = 1 To .Columns.Count:  Print #F, .Cells(R, C).Text; "|";:  Next
            Print #F,
        Next
    End With
        Close #F
End Sub

 

[Alex Blakenburg]

Thanks

Alex Blakenburg​

For the correction of the code.

With Best Regards

Glad we were able to help & thanks for the feedback.

 

[Alex Blakenburg]

@mamun_ges - I was just replying to Marc and ended up revisited your old post.
You seem to have started this thread using code before changing Case 2 to Case 5.
I am pretty sure your numbers are in Column 5 so don't forget to change the Case 2 to be Case 5

 

[Alex Blakenburg]

The correction of the initial VBA procedure - revamped without the useless ! - in case the cells formatting must be exported too :

Hello Marc, typically in interfaces you want to keep the formatting for date columns but ignore it for numbers.
General format for numbers sometimes works but often you want to show 2 decimal places.

 

[Marc L]

​

As it depends on the expected result … Post #16 code keeps the cell formatting without any useless Select Case block …​

As any good enough application does not need 2 decimal places to import data, in this case post #14 does the job.​