Thread: Export Multiple Tabs to Pipe Delimited Txt

Hello Machie,

Welcome to the Forum!

This macro will create a text file from worksheet, except "Outbound File", and delimit the data with the pipe character. The files will be saved in the default location for saved workbooks.

Sub SaveSheetsAsTextFiles()

  Dim Data() As Variant
  Dim FileName As String
  Dim FilePath As String
  Dim FSO As Object
  Dim I As Long
  Dim LastCol As Long
  Dim LastRow As Long
  Dim NamesWks As Worksheet
  Dim R As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Text As String
  Dim TextFile As Object
  Dim Wks As Worksheet
  
    Set NamesWks = Worksheets("OutBound File")
    FilePath = Application.DefaultFilePath & "\"
    
      Set Rng = NamesWks.Range("A1")
      Set RngEnd = NamesWks.Cells(Rows.Count, Rng.Column).End(xlUp)
      Set Rng = NamesWks.Range(Rng, RngEnd)
      
      Set FSO = CreateObject("Scripting.FileSystemObject")
    
        For Each FileName In Rng
          I = I + 1
          If I > Worksheets.Count Then GoTo Finished
          If Worksheets(I).Name <> NamesWks.Name Then
             Set TextFile = FSO.OpenTextFile(FilePath & FileName, 2, True, -2)
               LastCol = Wks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Column
               LastRow = Wks.Cells.Find(What:="*", SearchOrder:=xlByColumns).Row
               ReDim Data(1 To LastCol)
                 For R = 1 To LastRow
                   Data = Wks.Cells(R, 1).Resize(1, LastCol).Value
                   Text = Join(WorksheetFunction.Index(Data, 1, 0), "|") & vbCrLf
                   TextFile.Write Text
                 Next R
             TextFile.Close
          End If
        Next FileName
        
Finished:
    Set FSO = Nothing
    Set TextFile = Nothing
    
End Sub