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.

 

[Alex Blakenburg]

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

                    Case UsedColumns    ' Last Column end of line
                        Print #1, .Cells(R, C)

To this: (added to the end of the line --> ;"|" )

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

 

[mamun_ges]

Sorry to Say That Adding; "|"; after
Print #1, .Cells(R, C)
Is not worked.

Any Other suggestion.

 

[Marc L]

Hi,​

​

share at least a source worksheet …​

 

[Joe4]

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:

Number in decimal Format in text file generate from Excel VBA

Hi, I have searched a lot but find no answer. Hope someone helps me in this context. I am using a vba code to create a text file from an excel sheet. The code works fine. But there is a number format in decimal in excel which converts as a number without a decimal place in the text file. below...

www.mrexcel.com

 

[Marc L]

Without any attachment it's like « No arm, no chocolate ! » …​

 

[Joe4]

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.

 

[Marc L]

​

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 …​

 

[Marc L]

mamun_ges,​

​

you should be inspired with this sample :​

VBA save range as CSV file and add an extra column with fixed text

Hi I have a range in Excel i would like to save as CSV file in C:\test\ with filename: PROD + date and time (Format DD-MM-YYYY HH MM) C:\test\PROD 22-09-2021 12 00.CSV Besides the three column in my sheet i would like to add a 4. column with a fixed text like "LAG" ABC1Column 1Column 2Column...

www.mrexcel.com

 

[Alex Blakenburg]

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.

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