Excel Forum

Code: Select all

Sub AddPipe()
Dim Handle As Integer, File_Name As String
Dim Row As Long, PutStr As String
Dim sh As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook
For Each sh In wb.Worksheets

File_Name = "D:\" & sh.Name & ".csv"
Handle = FreeFile
If Dir(File_Name) <> "" Then Kill File_Name
Open File_Name For Binary As Handle

Row = 0
Range("A1").Select
Do Until Selection.Value = ""
    Row = Row + 1
    PutStr = Range("A" & Row).Value & "|" & _
             Range("B" & Row).Value & "|" & _
             Range("C" & Row).Value & "|" & _
             Range("D" & Row).Value & "|" & _
             Range("E" & Row).Value & "|" & _
             Range("F" & Row).Value & "|" & _
             Range("G" & Row).Value & "|" & _
             Range("H" & Row).Value & "|" & _
             Range("I" & Row).Value & "|" & _
             Range("J" & Row).Value & "|" & _
             Range("K" & Row).Value & "|" & _
             Range("L" & Row).Value & "|" & _
             Range("M" & Row).Value & "|" & _
             Range("N" & Row).Value & "|" & _
             Range("O" & Row).Value & "|" & _
             Range("P" & Row).Value & "|" & _
             Range("Q" & Row).Value & "|" & _
             Range("R" & Row).Value & "|" & _
             Range("S" & Row).Value & "|" & _
             Range("T" & Row).Value & "|" & _
             Range("U" & Row).Value & "|" & _
             Range("V" & Row).Value & "|" & _
             Range("W" & Row).Value & "|" & _
             Range("X" & Row).Value & "|" & _
             Range("Y" & Row).Value & "|" & _
             Range("Z" & Row).Value & "|" & _
             Range("AA" & Row).Value & "|" & _
             Range("AB" & Row).Value & "|" & _
             Range("AC" & Row).Value & "|" & _
             Range("AD" & Row).Value & "|" & _
             Range("AE" & Row).Value & "|" & _
             Range("AF" & Row).Value & "|" & _
             Range("AG" & Row).Value & "|" & _
             Range("AH" & Row).Value & "|" & _
             Range("AI" & Row).Value & "|" & _
             Range("AJ" & Row).Value & "|" & _
             Range("AK" & Row).Value & "|" & _
             Range("AL" & Row).Value & "|" & _
             Range("AM" & Row).Value & "|" & _
             Range("AN" & Row).Value & "|" & _
             Range("AO" & Row).Value & vbCrLf
    Put Handle, , PutStr
    Selection.Offset(1, 0).Select
Loop
Close Handle
Next sh
End Sub

Code: Select all

Sub AddPipe()
    ...    
    Row = 0
    PutStr = ""
    Range("A1").Select
    Do Until Selection.Value = ""
        Row = Row + 1
        For Each r In Range("A" & Row, Range("AO" & Row))
            PutStr = PutStr & r & "|"
        Next r
        PutStr = VBA.Left(PutStr, Len(PutStr) - 1) & vbCrLf
        Put Handle, , PutStr
        Selection.Offset(1, 0).Select
    Loop
    ...
End Sub