Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

pipe delimited 2

Status
Not open for further replies.

dodge20

MIS
Jan 15, 2003
1,048
US
Is it possible to save an excel worksheet with | as the delimiter? Dodge20
If it ain't broke, don't fix it.
 
You can do it programmatically using sequential file access. Something like:

open "Pipey.txt" for output as #1
with activesheet.usedrange
for r=1 to .rows.count
print#1, .cells(r,1);
for c=1 to .columns.count-1
print#1, .cells(r,c); "|"
next c
print#1, .cells(r,.columns.count)
next r
end with
close #1


Rob
[flowerface]
 
I am by no means an excel expert. Can you explain to me how to use this code, and where to put it.

thanks Dodge20
If it ain't broke, don't fix it.
 
Within Excel, type Alt-F11 to go to the Visual Basic Editor (VBE). On the left side of the window, you'll see an explorer panel. Find the workbook into which you want to insert the macro, right-click on its name, and choose Insert-Module from the context menu. This will open a new macro module in the main panel to the right. In this panel, type

sub SaveAsPipeDelimited

VBE will fill in an "end sub" automatically. Between the two lines, paste the code I suggested above. You'll also want to declare your variables, using the statement

dim r as integer, c as integer

right underneath the sub statement.
Now you can go back to Excel, and type Alt-F8 to go to the Macros dialog. Double-click on the SaveAsPipeDelimited macro, and it will run. The file will be saved in the currently active folder (the last folder used to open or save a file).
Rob
[flowerface]
 
Rob

Thanks for your help, it is close, but the output isn't quite right. I did a test in excel

1 1 |
1 |
1 |
1
2 2 |
2 |
2 |
2
3 3 |
3 |
3 |
3
4 4 |
4 |
4 |
4
Dodge20
If it ain't broke, don't fix it.
 
I'm sorry, I forgot to delete a line, and somehow a semicolon went missing (I recoded for efficiency after my first draft, and introduced these flaws). Here's the correct code:

open "Pipey.txt" for output as #1
with activesheet.usedrange
for r=1 to .rows.count
for c=1 to .columns.count-1
print#1, .cells(r,c); "|";
next c
print#1, .cells(r,.columns.count)
next r
end with
close #1
Rob
[flowerface]
 
Thanks rob

It works perfectly. This is going to save me a lot of time. Dodge20
If it ain't broke, don't fix it.
 
Modify as follows to retain empty columns to left of data:

open "Pipey.txt" for output as #1
with activesheet.usedrange
for r=.row to .row+.rows.count-1
for c=1 to .column+.columns.count-2
print#1, activesheet.cells(r,c); "|";
next c
print#1, activesheet.cells(r,.column+.columns.count-1)
next r
end with
close #1

I haven't tested this - please let me know if it doesn't work right for you.
Rob
[flowerface]
 
Genius

Thanks again rob

||| 1 | 1 | 1 | 1 |||| 5
||| 2 | 2 | 2 | 2 |||| 5
||| 3 | 3 | 3 | 3 |||| 5
||| 4 | 4 | 4 | 4 |||| 5
Dodge20
If it ain't broke, don't fix it.
 
Hey - that's 2 cries of Genius for Rob in the last 2 days - maybe he is a bone fide genius ??? Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur
 
you'll have to code up
Code:
IsABonaFidaGenius(objRob As Genius) As Boolean
to find out ...

Nikki
 
LOL LOL LOL LOL LOL Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur
 
Nikki

I found that hilarious. I thought about telling it too my friends, but realized they wouldn't think it was very funny. Right then I realized I am a nerd.
THANKS A LOT!! (Sarcasm) Dodge20
If it ain't broke, don't fix it.
 
Bona fide? What's with Geoff and his obsession with Latin? ;-)

Quod licet Iovi, non licet bovi

Rob
[flowerface]
 
I'm still waiting for the function:

MakeMeAGenius(programmer as variant)

to be complete! I really need to use it!!

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!
 
:) blame xlhelp - he started it [ponder]
Gonna have to change my profile and quit with the Latin thing - it can get nastily addictive ;-) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur
 
I have one more question. Is it possible to eliminate the spaces that come before and after data. It is outputing like this

|| 3 | 8 |||| Dodge20
If it ain't broke, don't fix it.
 
open "Pipey.txt" for output as #1
with activesheet.usedrange
for r=.row to .row+.rows.count-1
for c=1 to .column+.columns.count-2
print#1, format(activesheet.cells(r,c)); "|";
next c
print#1, format(activesheet.cells(r,.column+.columns.count-1))
next r
end with
close #1

The format() function makes a string without leading or trailing spaces.
Rob
[flowerface]
 
Thanks again rob.
Is there a good text you can recommend, becuase I want to learn how to do this. Dodge20
If it ain't broke, don't fix it.
 
There are plenty of good books on VBA programming. The ones by John Walkenbach are always popular, and quite comprehensive. If I were shopping for one, I'd go to Amazon, search for "programming excel", and read the reviews on the books that pop up. You're bound to find one that fits your style.
Rob
[flowerface]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top