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
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
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
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
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.
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
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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.