Questions

How would one export multi worksheet excel file to a single text file?

Tags:
+
0 Votes
Locked

How would one export multi worksheet excel file to a single text file?

wagara1
I am attempting to use VBA code to export a multi worksheet excel file to a single text file with each worksheet appearing as a separate line in the text file. Any insight would be appreciated.
  • +
    0 Votes
    Darryl~ Moderator

    It appears to do what you're after.

    Exporting To A Text File

    This procedure allows you to export data from a worksheet range to a text file. You may specify the character (e.g, a space, tab, pipe, comma, etc) that separates the exported elements. Each row of cells in the worksheet is written to one line within the text file, and each item in that line is separated by the specified delimiter character. Any single character may be used as the delimiter.

    The ExportToTextFile procedure follows. The parameters to ExportToTextFile are described in the following table:

    Parameter Description
    FName The name of the file to which the data will be written. The file will be created if it does not exist. See AppendData below.


    Sep The character that is to separate the elements on each row of the exported file. Typically, this is vbTab, a space, a comma, semicolor, or pipe ( | ). Any character may be used.


    SelectionOnly If True, only the currently selected cells are exported. If False, the entire used range of the worksheet is exported.


    AppendData If True and FName exists, data is written to the end of the text file, preserving the existing contents. If False, the existing contents of FName are destroyed and only the newly exported data will appear in the output file.



    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' ExportToTextFile
    ' This exports a sheet or range to a text file, using a
    ' user-defined separator character.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)

    Dim WholeLine As String
    Dim FNum As Integer
    Dim RowNdx As Long
    Dim ColNdx As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim StartCol As Integer
    Dim EndCol As Integer
    Dim CellValue As String


    Application.ScreenUpdating = False
    On Error GoTo EndMacro:
    FNum = FreeFile

    If SelectionOnly = True Then
    With Selection
    StartRow = .Cells(1).Row
    StartCol = .Cells(1).Column
    EndRow = .Cells(.Cells.Count).Row
    EndCol = .Cells(.Cells.Count).Column
    End With
    Else
    With ActiveSheet.UsedRange
    StartRow = .Cells(1).Row
    StartCol = .Cells(1).Column
    EndRow = .Cells(.Cells.Count).Row
    EndCol = .Cells(.Cells.Count).Column
    End With
    End If

    If AppendData = True Then
    Open FName For Append Access Write As #FNum
    Else
    Open FName For Output Access Write As #FNum
    End If

    For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
    If Cells(RowNdx, ColNdx).Value = "" Then
    CellValue = Chr(34) & Chr(34)
    Else
    CellValue = Cells(RowNdx, ColNdx).Value
    End If
    WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
    Next RowNdx

    EndMacro:
    On Error GoTo 0
    Application.ScreenUpdating = True
    Close #FNum

    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' END ExportTextFile
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Because the ExportToTextFile procedure accepts input parameters, you must call it from other VBA code, such as the following:

    Sub DoTheExport()
    ExportToTextFile FName:="C:\Test.txt", Sep:=";", _
    SelectionOnly:=False, AppendData:=True
    End Sub

    In the example DoTheExport procedure above, the file name and the separator character are hard coded in to the code. If you want to prompt the user for the file name and the separator character, use code like the following:

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' DoTheExport
    ' This prompts the user for the FileName and the separtor
    ' character and then calls the ExportToTextFile procedure.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sub DoTheExport()
    Dim FileName As Variant
    Dim Sep As String
    FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
    If FileName = False Then
    ''''''''''''''''''''''''''
    ' user cancelled, get out
    ''''''''''''''''''''''''''
    Exit Sub
    End If
    Sep = Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
    ''''''''''''''''''''''''''
    ' user cancelled, get out
    ''''''''''''''''''''''''''
    Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
    SelectionOnly:=False, AppendData:=True
    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' END DoTheExport
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    This code will prompt the user for a text file name in which to save the file and for the separator character. If the user cancels either of these dialogs, the procedure is terminated and no export operation is carried out.

  • +
    0 Votes
    Darryl~ Moderator

    It appears to do what you're after.

    Exporting To A Text File

    This procedure allows you to export data from a worksheet range to a text file. You may specify the character (e.g, a space, tab, pipe, comma, etc) that separates the exported elements. Each row of cells in the worksheet is written to one line within the text file, and each item in that line is separated by the specified delimiter character. Any single character may be used as the delimiter.

    The ExportToTextFile procedure follows. The parameters to ExportToTextFile are described in the following table:

    Parameter Description
    FName The name of the file to which the data will be written. The file will be created if it does not exist. See AppendData below.


    Sep The character that is to separate the elements on each row of the exported file. Typically, this is vbTab, a space, a comma, semicolor, or pipe ( | ). Any character may be used.


    SelectionOnly If True, only the currently selected cells are exported. If False, the entire used range of the worksheet is exported.


    AppendData If True and FName exists, data is written to the end of the text file, preserving the existing contents. If False, the existing contents of FName are destroyed and only the newly exported data will appear in the output file.



    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' ExportToTextFile
    ' This exports a sheet or range to a text file, using a
    ' user-defined separator character.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)

    Dim WholeLine As String
    Dim FNum As Integer
    Dim RowNdx As Long
    Dim ColNdx As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim StartCol As Integer
    Dim EndCol As Integer
    Dim CellValue As String


    Application.ScreenUpdating = False
    On Error GoTo EndMacro:
    FNum = FreeFile

    If SelectionOnly = True Then
    With Selection
    StartRow = .Cells(1).Row
    StartCol = .Cells(1).Column
    EndRow = .Cells(.Cells.Count).Row
    EndCol = .Cells(.Cells.Count).Column
    End With
    Else
    With ActiveSheet.UsedRange
    StartRow = .Cells(1).Row
    StartCol = .Cells(1).Column
    EndRow = .Cells(.Cells.Count).Row
    EndCol = .Cells(.Cells.Count).Column
    End With
    End If

    If AppendData = True Then
    Open FName For Append Access Write As #FNum
    Else
    Open FName For Output Access Write As #FNum
    End If

    For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
    If Cells(RowNdx, ColNdx).Value = "" Then
    CellValue = Chr(34) & Chr(34)
    Else
    CellValue = Cells(RowNdx, ColNdx).Value
    End If
    WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
    Next RowNdx

    EndMacro:
    On Error GoTo 0
    Application.ScreenUpdating = True
    Close #FNum

    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' END ExportTextFile
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Because the ExportToTextFile procedure accepts input parameters, you must call it from other VBA code, such as the following:

    Sub DoTheExport()
    ExportToTextFile FName:="C:\Test.txt", Sep:=";", _
    SelectionOnly:=False, AppendData:=True
    End Sub

    In the example DoTheExport procedure above, the file name and the separator character are hard coded in to the code. If you want to prompt the user for the file name and the separator character, use code like the following:

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' DoTheExport
    ' This prompts the user for the FileName and the separtor
    ' character and then calls the ExportToTextFile procedure.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sub DoTheExport()
    Dim FileName As Variant
    Dim Sep As String
    FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
    If FileName = False Then
    ''''''''''''''''''''''''''
    ' user cancelled, get out
    ''''''''''''''''''''''''''
    Exit Sub
    End If
    Sep = Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
    ''''''''''''''''''''''''''
    ' user cancelled, get out
    ''''''''''''''''''''''''''
    Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
    SelectionOnly:=False, AppendData:=True
    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' END DoTheExport
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    This code will prompt the user for a text file name in which to save the file and for the separator character. If the user cancels either of these dialogs, the procedure is terminated and no export operation is carried out.