Pipe-delimited Text File Format

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
58
Hi, I am using the below code to Generate a text File in pipe Delimited format.
VBA Code:
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
The code you had was pretty clearly documented.
PS: I have the benefit of having seen your previous post on this where your requirement was "I need to create the text file where pipe "|" will be after the last text" in other words at the end of each line. (not as in the above where is says after the "last line")

Just change this:-
VBA Code:
                    Case UsedColumns    ' Last Column end of line
                        Print #1, .Cells(R, C)

To this: (added to the end of the line --> ;"|" )
VBA Code:
                    Case UsedColumns    ' Last Column end of line
                        Print #1, .Cells(R, C); "|"
 
Last edited:
Upvote 0
Hi,​
share at least a source worksheet …​
 
Upvote 0
share at least a source worksheet …
The question is straightforward enough that it should not really be necessary (they just want add a "|" after the last field in each row of the file being created), but if you really want to see some sample data, have a look at the previous thread:
 
Upvote 0

Without any attachment it's like « No arm, no chocolate ! » …​
 
Upvote 0
Without any attachment it's like « No arm, no chocolate ! » …
It is simply a question about adding a pipe symbol (the delimiter) after the last value in each row.
If you are familar with creating text files from Excel, you would know that by default it does not add a delimiter after the last value in each row.
From the images he posted in the other thread, it looks like all rows end in the same column.

So what is it that you are failing to understand about the question that downloading an attachment would help you with?
You could literally test this on any Excel file you want where the the data ends in the same column for each row.
 
Upvote 0
As it already works on my side but as he stated « Is not worked » so I won't waste any time without any attachment …​
And as it seems he confuses line and column …​
 
Upvote 0
mamun_ges,​
you should be inspired with this sample :​
 
Upvote 0
Sorry to Say That Adding; "|"; after
Print #1, .Cells(R, C)
Is not worked.

Any Other suggestion.

Please copy these 2 lines over the top of yours for the last row case.

VBA Code:
                    Case UsedColumns    ' Last Column end of line
                        Print #1, .Cells(R, C); "|"
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top