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.

 

[blondeambition]

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

Ugh, this did wrap the rows BUT it wrapped the cells in each column first, then the rows. I just want each row to follow each other, after the first row of data in Excel.

For example:

Row 1: Hi my name is
Row 2: The best person ever is 79222
Row 3: miles away from a bacon
Row 4: sandwich and a frozen anteater walking on clouds

Keep the first row as is and wrap the other rows below it

Row 1: Hi my name is
Row 2+3+4 (wrapped): The best person ever is 79222 miles away from a bacon sandwich and a frozen anteater walking on clouds

Is anyone awake that can help me please? LOL
Btw, this is being pasted into Notepad with pipe delimiters (I have the code for that already).

Thanks, ba

 

[Rick Rothstein]

Keep the first row as is and wrap the other rows below it

Row 1: Hi my name is
Row 2+3+4 (wrapped): The best person ever is 79222 miles away from a bacon sandwich and a frozen anteater walking on clouds

What exactly do you mean by Row 2+3+4(wrapped)? Do you mean merged so that they appear to be a single cell with the text wrapped within that merged cell?

 

[blondeambition]

What exactly do you mean by Row 2+3+4(wrapped)? Do you mean merged so that they appear to be a single cell with the text wrapped within that merged cell?

Yes, basically. I just need the rows (not including the first row) to display merged in a text file (Notepad) when its pasted there. Hope that makes sense.

 

[Rick Rothstein]

Yes, basically. I just need the rows (not including the first row) to display merged in a text file (Notepad) when its pasted there. Hope that makes sense.

Sorry, but I am still not clear on this. Let's say this is what your worksheet looks like...

	A	B	C
1	Hi my name is	One two
2	The best person ever	Three four five
3	is 79222 miles away	six seven
4	from a bacon sandwich.	eight nine ten.
5

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 162px;"><col style="width: 109px;"><col style="width: 32px;"></colgroup><tbody>
</tbody>

Show me how that should look in the text file that you are going to load into Notepad.

 

[blondeambition]

Sorry, but I am still not clear on this. Let's say this is what your worksheet looks like...

	A	B	C
1	Hi my name is	One two
2	The best person ever	Three four five
3	is 79222 miles away	six seven
4	from a bacon sandwich.	eight nine ten.
5

<TBODY>
</TBODY>

Show me how that should look in the text file that you are going to load into Notepad.

That's ok, I'm not the best at explaining with this.
This is what I'm looking for:

Hi my name is|One two|
The best person ever|Three four five|is 79222 miles away|six seven|from a bacon sandwich.|eight nine ten.|

 

[Rick Rothstein]

This is what I'm looking for:

Hi my name is|One two|
The best person ever|Three four five|is 79222 miles away|six seven|from a bacon sandwich.|eight nine ten.|

Just to clarify... will that be one single line or two separate lines in the text file?

 

[blondeambition]

Just to clarify... will that be one single line or two separate lines in the text file?

Two seprate lines. The first line (first row in Excel) will always be its own line, and the next line will be one long line depending on how many rows there are from Excel.

 

[Rick Rothstein]

Two seprate lines. The first line (first row in Excel) will always be its own line, and the next line will be one long line depending on how many rows there are from Excel.

Okay, I think I have it all straight now. Give this macro a try...

Sub PipeDelimitedRowJoins()
  Dim R As Long, C As Long, LastRow As Long, FileNo As Long
  Dim FileName As String, PipeDelimitedText As String
  Const ColCount As Long = [B][COLOR=#ff0000]26
[/COLOR][/B]  FileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\[COLOR=#0000FF][B]Testfile.txt[/B][/COLOR]"
  LastRow = Columns("[COLOR=#008000][B]A[/B][/COLOR]").Resize(, ColCount).Find("*", , xlValues, , xlRows, xlPrevious).Row
  PipeDelimitedText = Join(Application.Index(Range("[B][COLOR=#008000]A1[/COLOR][/B]").Resize(, ColCount).Value, 1, 0), "|") & "|" & vbCrLf
  For R = [COLOR=#800080][B]2[/B][/COLOR] To LastRow
    PipeDelimitedText = PipeDelimitedText & Join(Application.Index(Cells(R, "[COLOR=#008000][B]A[/B][/COLOR]").Resize(, ColCount).Value, 1, 0), "|") & "|"
  Next
  FileNo = FreeFile
  Open FileName For Output As #FileNo
  Print #FileNo, PipeDelimitedText
  Close #FileNo
End Sub

NOTE: You will have to change the highlighted item to match your actual setup...

1) The red 26 is the number of columns of data that you want to concatenate across.

2) The blue text is the file name you want to use for the text file that will be saved to your computer's Desktop (note the leading backslash... that must precede whatever file name you use).

3) The 3 green text snippets represent the first cell where your data starts (the code assumes that is cell A1).

4) The purple 2 is the row number for the second line of data.

 

[blondeambition]

Thanks Rick, let me try this and let you know how it works.

 

[blondeambition]

Hi Rick, it all worked except the formatting of two my my cells was changed when pasted onto the Notepad file. The cells have dates in the format of yyyymmdd in Excel, but in Notepad they changed to m/d/yyyy. Any idea why?