How can I convert tab delimited files to pipe delimited?

[Jeremy Town]

I currently have some .txt files which are tab delimited, and I need to
convert them so that they are pipe(|) delimited. Any ideas how to do this?
Excel seems to support comma, tab and space delimiting, not sure how to use
pipe or tilda.

 

[Dave Peterson]

I'd copy the text files into their own folder--just in case!

Then run a macro like:

Option Explicit
Sub testme01()

Dim myFileNames As Variant
Dim wkbk As Workbook
Dim fCtr As Long
Dim myCDP As DocumentProperties

myFileNames = Application.GetOpenFilename("Text Files, *.Txt", _
MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub
End If

For fCtr = LBound(myFileNames) To UBound(myFileNames)
Call DoTheWork(myFileNames(fCtr))
Next fCtr

End Sub

Sub DoTheWork(myFileName As Variant)

Dim FSO As Object
Dim RegEx As Object
Dim myFile As Object
Dim myContents As String

Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.fileexists(myFileName) = False Then
'Do nothing
Else
Set myFile = FSO.OpenTextFile(myFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
.Pattern = vbTab
myContents = .Replace(myContents, "|")
End With

Set myFile = FSO.CreateTextFile(myFileName, True)
myFile.Write myContents
myFile.Close
End If

End Sub

 

[Julie Swatling]

Did this solution work? Is there an easier answer? I am currently using
Office 2000 and am willing to update for this feature as a simple "save as"
option.