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.

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



Jeremy Town wrote:

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

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.

"Dave Peterson" wrote:

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



Jeremy Town wrote:

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