Saving as a .csv but with differnet deliminator.

[dpearson]

Sorry if this has been asked before but I couldn't find it.

I have a little problem, that goes like this.

I need to save a worksheet as a .CSV file but using | (pipe) as the
deliminator, I might be blind but I cannot see how I can do this.

If i need to use vba to do it that is possble as I could attach a toolbar to
the worksheet.

Any help would be greatly appricicated.


Dave

 

[Nikos Yannacopoulos]

Dave,

The only way I've found to do this is by means of VB code like the following
sample:

Dim ColCount As Long
Dim RowCount As Long
Dim lin As String

ColCount = 5
RowCount = 100
Range ("A1").Select
Open "PathAndFileName.txt" For Output As #1
For i = 0 To RowCount
lin = ""
For j = 0 to ColCount
if lin <> "" Then lin = lin & "|"
lin = lin & Activecell.Offset(i, j).Value
Next
Print #1, lin
Next
Close #1

This example will export a range of 100 rows and 5 columns starting in A1.
It can be easily modified to any constant range just by changing the cell
ref. and numbers, or it can be modified to work with variable size ranges
(provided there are no blanks), checking for a non-empty cell in each loop
iteration.

HTH,
Nikos

 

[Dave Pearson]

Cheers Mate,

This works perfect.

Many thanks again.

regards

Dave