Excel Worksheets to Text (Excel 2000)

Topic

New Reply

I have several workbooks containing a various number of worksheets (50+) each. Is it possible to automate the process of exporting each worksheet of a workbook to a text file?

Reply | Quote

Replies

This code will create a text file from each worksheet in the workbook and store the files in the directory C:Work with the same file name as the sheet name in the workbook.

{pre]
Public Sub CreateTxt()
Dim oWS As Worksheet, oWB As Workbook
Dim strPath As String, strFName As String
strPath = “C:Work”
For Each oWS In Worksheets
oWS.Copy
ActiveWorkbook.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlTextWindows
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Next oWS
End Sub
[/pre]

Reply | Quote

This code will create a text file from each worksheet in the workbook and store the files in the directory C:Work with the same file name as the sheet name in the workbook.

{pre]
Public Sub CreateTxt()
Dim oWS As Worksheet, oWB As Workbook
Dim strPath As String, strFName As String
strPath = “C:Work”
For Each oWS In Worksheets
oWS.Copy
ActiveWorkbook.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlTextWindows
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Next oWS
End Sub
[/pre]

Reply | Quote

Does something like this work? Change the path as appropriate and the fileformat if desired.

Option Explicit
Sub SaveEachAsText()
    Dim sOriName As String
    Dim wks As Worksheet
    Dim sPath As String
    Dim sFilename As String
    sOriName = ActiveWorkbook.FullName
    sPath = "C:"
    For Each wks In Worksheets
        sFilename = sPath & wks.Name & ".txt"
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs _
            FileName:=sFilename, _
            FileFormat:=xlTextMSDOS
    Next
    ActiveWorkbook.SaveAs _
        FileName:=sOriName, FileFormat:=xlNormal
    Application.DisplayAlerts = True
End Sub

Note: this will overwrite without warning any files that already have that file name! Remove the “Displayalerts = false” line if you want a warning.

Steve

Reply | Quote

Does something like this work? Change the path as appropriate and the fileformat if desired.

Option Explicit
Sub SaveEachAsText()
    Dim sOriName As String
    Dim wks As Worksheet
    Dim sPath As String
    Dim sFilename As String
    sOriName = ActiveWorkbook.FullName
    sPath = "C:"
    For Each wks In Worksheets
        sFilename = sPath & wks.Name & ".txt"
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs _
            FileName:=sFilename, _
            FileFormat:=xlTextMSDOS
    Next
    ActiveWorkbook.SaveAs _
        FileName:=sOriName, FileFormat:=xlNormal
    Application.DisplayAlerts = True
End Sub

Note: this will overwrite without warning any files that already have that file name! Remove the “Displayalerts = false” line if you want a warning.

Steve

Reply | Quote

Steve and Legare are too quick! One thought for both examples is that you might define the save location to be in the same folder as the current file, and to use the current file name as the first part of the file name, with:

strPath = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) – 4)

Reply | Quote

Thank you very much everyone … it works great. Now to maybe expand on this and get a little trickier. Is it possible to export a named range of a worksheet to a text file?

Reply | Quote

Something like this?

Option Explicit
Sub RangeAsText()
    Dim wkbS As Workbook
    Dim wkbNew As Workbook
    Set wkbS = ActiveWorkbook
    Set wkbNew = Workbooks.Add
    wkbS.Activate
        Range("ExportMe").Copy wkbNew.Sheets(1).Range("a1")
    Application.DisplayAlerts = False
    wkbNew.SaveAs _
        FileName:="C:ExportName.txt", _
        FileFormat:=xlTextMSDOS
    wkbNew.Close
    Application.DisplayAlerts = True
End Sub

Steve

Reply | Quote

Something like this?

Option Explicit
Sub RangeAsText()
    Dim wkbS As Workbook
    Dim wkbNew As Workbook
    Set wkbS = ActiveWorkbook
    Set wkbNew = Workbooks.Add
    wkbS.Activate
        Range("ExportMe").Copy wkbNew.Sheets(1).Range("a1")
    Application.DisplayAlerts = False
    wkbNew.SaveAs _
        FileName:="C:ExportName.txt", _
        FileFormat:=xlTextMSDOS
    wkbNew.Close
    Application.DisplayAlerts = True
End Sub

Steve

Reply | Quote

Thank you very much everyone … it works great. Now to maybe expand on this and get a little trickier. Is it possible to export a named range of a worksheet to a text file?

Reply | Quote

Steve and Legare are too quick! One thought for both examples is that you might define the save location to be in the same folder as the current file, and to use the current file name as the first part of the file name, with:

strPath = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) – 4)

Reply | Quote