│ seperator in text file

[jakobt]

I want to save a excel table to a text file and each cell must be separated by│in the text file?

 

[Rick Rothstein]

I want to save a excel table to a text file and each cell must be separated by│in the text file?

Is each row to be a separate line in the text file or do you want one humongous line containing all of the cell values?

How many columns are there? If the number is not fixed, what is the maximum number of columns you might anticipate?

How many rows maximum would you expect there to be?

 

[jakobt]

The sheet has 30 rows and 30 columns. But this is a generic issue, so in principle it can be something else.

 

[Rick Rothstein]

The sheet has 30 rows and 30 columns. But this is a generic issue, so in principle it can be something else.

You did not answer the first question I asked, so I'll assume you want each row on its own line within the outputted file. I have assumed your data starts in cell A1... you need to change the red highlighted text to the path/filename for the outputted file.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MakeTextWithVerticalBarDelimiter()
  Dim R As Long, LastRow As Long, LastCol As Long, FF As Long
  Dim PathFilename As String, Result As String
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For R = 1 To LastRow
    LastCol = Cells(R, Columns.Count).End(xlToLeft).Column
    Result = Result & vbNewLine & Join(Application.Index(Cells(R, 1).Resize(, LastCol).Value, 1, 0), "|")
  Next
  PathFilename = "[B][color="#FF0000"]c:\temp\test.txt[/COLOR][/B]"
  FF = FreeFile
  Open PathFilename For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF"]#FF[/URL] 
    Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF"]#FF[/URL] , Mid(Result, Len(vbNewLine))
  Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF"]#FF[/URL] 
End Sub[/TD]
[/TR]
</tbody>[/TABLE]