Excel Forum

Sub ActiveSht_PiPe()

Const myDelim As String = "|"
Dim bb As Variant
Dim ws As Worksheet
Set ws = ActiveSheet

Dim r As Long, c As Long, i As Long, j As Long
r = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
c = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Dim myPath As String
myPath = ThisWorkbook.Path & "\"
Dim myFile As String
myFile = myPath & "pipe file.txt"
Dim obj As Object
Set obj = CreateObject("ADODB.Stream")
obj.Type = 2
obj.Charset = "unicode"
obj.Open
Dim v() As Variant
ReDim v(1 To c)
For i = 1 To r
For j = 1 To c
v(j) = Chr(34) & ws.Cells(i, j).Text & Chr(34)
bb = Replace(v(j), """", "")
Next
obj.WriteText Join(bb, myDelim), 1
Next
obj.SaveToFile myFile, 2
Dim Npad
Npad = Shell("C:\WINDOWS\notepad.exe " & myFile, 1)
End Sub