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.

 

[DocAElstein]

Do you mean the code in Message #3 above? If so, I did not need to do that when I tested the code before posting it.

. Thanks for the reply Rick
. As I mentioned (possibly in that other Thread?) it is aproblem I often have and so may be something specific to my system.
. Genarally your code is working fine for me
Thanks again
Alan

 

[DocAElstein]

Rick,

Any suggestion as to why when I use .CurrentRegion, my code works fine
......................

Pete

Hi Pete,
. I am not sure if it helps you.
. But I just learnt something useful as I tried to figure out your problem...
. I get the same error you do with your second code if any any cells in the range B6 to B24 are empty
. Using the extra .CurrentRegion bit seems a neat way to get over that problem.
. --: Using your second code it will work with empty cells as well

 

[DocAElstein]

.......
. --: Using your second code it will work with empty cells as well

... I mean when you tack on the extra .CurrentRegion bit, as in your first code

 

[NewOrderFac33]

Hi, Doc - still puzzling my way through it, as I have no empty cells in the range
I thought that having numbers, as against strings in the range might have been the culprit, so replaced them with strings, but it wasn't that either.

I'll get there, I'm sure, but thanks for your suggestion!

Pete

 

[NewOrderFac33]

Got it (and it's a daft one!)

You can't pipe-separate a single column (for obvious reasons) - as soon as I changed my range reference to include two or more columns, it worked fine.

Doh!

Pete

 

[Plokimu77]

Both Answers:

With Sheets("Sheet1").[COLOR=#FF0000][B]Range("A4:A87")[/B] [/COLOR]

and

Replace A1 with A4 give me a:

Run Time Error: '13':
Type mismatch

Debugger Stops at

FileText = FileText & vbCrLf & Join(Application.Index(.Rows(X).Value, 1, 0), "|")

Maybe because all my info is on column A?

Please Advise.

Thanks

 

[NewOrderFac33]

That's right - because this code is designed to delimit columns of data with a "|" character, it stands to reason that there needs to be at least two columns of data.

I wish I'd realised that earlier, though!

 

[Rick Rothstein]

That's right - because this code is designed to delimit columns of data with a "|" character, it stands to reason that there needs to be at least two columns of data.

I wish I'd realised that earlier, though!

It is my fault... I had some chores to do this morning/early afternoon and rushed my answer in Message #9 which, as you and others have discovered, was incorrect (I left out the CurrentRegion property). As it turns out, though, that answer appears not to be what the poster in Message #4 wanted. See my next message as I address that issue with the poster directly.

 

[Rick Rothstein]

Both Answers:

With Sheets("Sheet1").[COLOR=#ff0000][B]Range("A4:A87")[/B] [/COLOR]

and

Replace A1 with A4 give me a:

Run Time Error: '13':
Type mismatch

I am sorry, but my answer in Message #9 has led this thread astray. If all you want to do is concatenate a single column of contiguous cells together to form a pipe delimited text string, you can do that with one line of code...

PipeDelimitedText = Join(Application.Transpose(Sheets("Sheet1").Range("A4:A87").Value), "|")

 

[NewOrderFac33]

I am sorry, but my answer in Message #9 has led this thread astray. If all you want to do is concatenate a single column of contiguous cells together to form a pipe delimited text string, you can do that with one line of code...

PipeDelimitedText = Join(Application.Transpose(Sheets("Sheet1").Range("A4:A87").Value), "|")

It's that man again!
Happy New Year, Rick!
I shall give this a try at work tomorrow.
Cheers
Pete