CODEWITHSUNDEEP
vbaexcel
teepee
teepee

Reputation: 2714

how to save each individual sheet as a txt file

I have a code that creates sheets in a certain format that I then want to save as text files. I have been using Sheet.SaveAs and then naming the file differently. Is there a more robust way of saving files and moving them around? My current code runs as follows:

    OldPath = ThisWorkbook.Path & "\"     ' current path to this workbook
    OldFile = OldPath & ShtName & ".txt"  ' location of file upon creation

    NewPath = OldPath & FldName & "\"     ' path for the folder where file will be moved
    NewFile = NewPath & ShtName & ".txt"  ' location of file after moving
                                                                                             '[3] CREATE INPUT FILES
    ThisWorkbook.Sheets(ShtName).SaveAs OldFile, FileFormat:=xlTextWindows
    ThisWorkbook.SaveAs OldPath & ThisFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    If Len(Dir(NewPath, vbDirectory)) <> 0 And NewPath <> "" Then  'MOVE FILES TO A FOLDER
    Else
        MkDir NewPath  ' create folder for input files to be moved if not yet created
    End If

    If Len(Dir(NewFile)) <> 0 Then  
    ' delete an old version of file if it is already in folder
        SetAttr NewFile, vbNormal
        Kill NewFile
    End If

    Name OldFile As NewFile

This method feels cumbersome but I don't want to have to resort to using Shell, as I feel that would be less robust, unless someone recommends that instead.

Upvotes: 1

Views: 122

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

You can use a Generic Text printer, and the PrintOut method to achieve this

First, if you havn't already, add a Generic Text Printer

  1. From Add Printer dialog, select File port
  2. Select Generic then Generic / Text Only
  3. Name it as you wish

This code sends each worksheet to this printer

Sub SaveWorkbookAsText(wb As Workbook, Optional FldName As String = vbNullString)
    Dim NewPath As String
    Dim GenericTextOnlyPrinter As String
    Dim ws As Worksheet

    '<~~~ Change this string to match your Generic Text Only Printer Name
    GenericTextOnlyPrinter = "Text Only (File)"

    NewPath = ThisWorkbook.Path & Application.PathSeparator

    If FldName <> vbNullString Then
        NewPath = NewPath & FldName
        If Right$(NewPath, 1) <> Application.PathSeparator Then
            NewPath = NewPath & Application.PathSeparator
        End If
    End If

    For Each ws In wb.Worksheets
        ws.PrintOut _
          ActivePrinter:=GenericTextOnlyPrinter, _
          PrintToFile:=True, _
          PrToFileName:=NewPath & ws.Name & ".txt", _
          IgnorePrintAreas:=True
    Next
End Sub

Alternatively, without depending on a printer, generate the file in code

Sub SaveWorkbookAsText(wb As Workbook, Optional FldName As String = vbNullString)
    Dim NewPath As String
    Dim ws As Worksheet
    Dim dat As Variant
    Dim rw As Long, cl As Long
    Dim FileNum As Integer
    Dim Line As String

    NewPath = ThisWorkbook.Path & Application.PathSeparator

    If FldName <> vbNullString Then
        NewPath = NewPath & FldName
        If Right$(NewPath, 1) <> Application.PathSeparator Then
            NewPath = NewPath & Application.PathSeparator
        End If
    End If

    For Each ws In wb.Worksheets
        FileNum = FreeFile
        Open NewPath & ws.Name & ".txt" For Output As #FileNum    ' creates the file

        dat = ws.UsedRange.Value
        ' in case the sheet contains only 0 or 1 cells
        If TypeName(dat) <> "Variant()" Then
            dat = ws.UsedRange.Resize(, 2)
        End If

        For rw = 1 To UBound(dat, 1)
            Line = vbNullString
            For cl = 1 To UBound(dat, 2) - 1
                Line = Line & dat(rw, cl) & vbTab
            Next
            Print #FileNum, Line & dat(rw, cl)
        Next
        Close #FileNum
    Next
End Sub

Upvotes: 1

Related Questions