Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Worksheets to Text (Excel 2000)

    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?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Worksheets to Text (Excel 2000)

    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]
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Worksheets to Text (Excel 2000)

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

    <pre>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</pre>


    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

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel Worksheets to Text (Excel 2000)

    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)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Worksheets to Text (Excel 2000)

    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?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Worksheets to Text (Excel 2000)

    Something like this?

    <pre>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</pre>


    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •