Save as pipe delimited in Excel using VBA 1

[MsHart2u]

Can you please tell me how I can Save an Excel worksheet as pipe delimited text file, using VBA?

Thank you in advance.

[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[mscallisto]

Maybe a version of this:

Sub pipey()
'Create a text file with each row cell delimited with "|"

Dim intUsedrows As Integer
Dim intUsedcolumns As Integer

Open "C:\Pipey.txt" For Output As #1

With Worksheets(1).Range("a:f")
intUsedrows = ActiveSheet.UsedRange.Rows.Count
intUsedcolumns = ActiveSheet.UsedRange.Columns.Count

For i = 1 To intUsedrows
For j = 1 To intUsedcolumns - 1
Print #1, .Cells(i, j); "|";
Next j
Print #1, .Cells(i, intUsedcolumns)
Next i
End With
Close #1
MsgBox ("Done Successfully")
End Sub

 

[DrSimon]

Not saying that mscallisto is wrong, and that way may be quicker, but alternative approaches are:

A:
Use Access to open the Excel file and use the TransferText method to export as pipe delimited.

B - more convoluted:
1) Save As say a Tab delimited type text
2) Open text file using Word
3) Find Tab replace with Pipe
4) Save File

Just giving you food for thought!

 

[SkipVought]

Here's a function I wrote several years ago to convert a list into a delimited string. I mainly use it for SQL IN () statements, but it can be used in lots of other circumstances as well. It has a DEFAULT delimiter, COMMA and text straddler, APOSTROPHY, but you can substitute your PIPE and no straddler.

Function MakeList(rng As Range, Optional TK As String = "'", Optional CM As String = ",") As String
'SkipVought/2005 Jun 13
'--------------------------------------------------
' Access: N/A
'--------------------------------------------------
':this function returns a single-quoted list that can be used, for instance _
in an IN Clause in SQL _
"WHERE PART_ID IN (" & MakeList([SomeRange]) & ")"
'--------------------------------------------------
    Dim r As Range
    
    For Each r In rng.SpecialCells(xlCellTypeVisible)
        With r
            If Not .EntireRow.Hidden Then _
                MakeList = MakeList & TK & Trim(.Value) & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

so if you used it on a blank sheet, referencing the data on the sheet you want to export, you can get a single column of pipe delimited data and just SaveAs a .prn.

Might take 20 seconds.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]