Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: saving selection as text file

  1. #1
    New Member
    Join Date
    Jun 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can't figure out how to save a selection as a text file. I just want to save a highlighted section of a workbook as a text file to use in access. Any suggestions?

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You have to copy your range to another worksheet then save it as a .txt or .csv

  3. #3
    Board Regular steve case's Avatar
    Join Date
    Apr 2002
    Posts
    786
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Copy the selection to the clipboard, open up Notebook or what ever text application you use, paste it in there and save as. Not elegant and sexy, but it works. Making Excel save as text is a whole lot more work and fooling around. But if you want a Macro to do it, then that's a different story.

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Add this code to a module and run it. The code will ask you to select the range to save and then ask you for the folder to save to. JSW

    Sub myTSave()
    Dim myFolder As String
    'By Joe Was.
    'Save Range as Text File.

    ActiveSheet.Activate
    'Ask user to select range for text file.
    Set myRange = Application.InputBox(prompt:="Please select a range!", _
    Title:="Text File Range!", Type:=8)
    myRange.Select
    Selection.Copy
    'This temporarily adds a sheet named "Test."
    Sheets.Add.Name = "Test"
    Sheets("Test").Select
    ActiveSheet.Paste
    'Ask user for folder to save text file to.
    myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    'Save selected data as text file in users selected folder.
    ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
    'Remove temporary sheet.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Indicate save action.
    MsgBox "Text File: " & myFolder & "Saved!"
    'Go to top of sheet.
    Range("A1").Select
    End Sub

  5. #5
    New Member
    Join Date
    Jul 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This works GREAT. Thanks

  6. #6
    Board Regular megnin's Avatar
    Join Date
    Feb 2002
    Location
    Fort Lauderdale, FL USA
    Posts
    340
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-07-02 15:05, Joe Was wrote:
    Add this code to a module and run it. The code will ask you to select the range to save and then ask you for the folder to save to. JSW

    Sub myTSave()
    Dim myFolder As String
    'By Joe Was.
    'Save Range as Text File.

    ActiveSheet.Activate
    'Ask user to select range for text file.
    Set myRange = Application.InputBox(prompt:="Please select a range!", _
    Title:="Text File Range!", Type:=8)
    myRange.Select
    Selection.Copy
    'This temporarily adds a sheet named "Test."
    Sheets.Add.Name = "Test"
    Sheets("Test").Select
    ActiveSheet.Paste
    'Ask user for folder to save text file to.
    myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    'Save selected data as text file in users selected folder.
    ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
    'Remove temporary sheet.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Indicate save action.
    MsgBox "Text File: " & myFolder & "Saved!"
    'Go to top of sheet.
    Range("A1").Select
    End Sub
    This is like something I need as well.

    Instead of saving a range of cells I need to select the contents of a Controls TextBox and save it to a text file.

    How do you select the contents of a TextBox to do that?

    Oh, when I clicked Cancel I got an error. This modification fixes that:
    Code:
    On Error Resume Next 
    
    Set myRange = Application.InputBox(prompt:="Please select a range!", _ 
    
    errorcode = Err.Number 
    On Error GoTo 0 
    If errorcode <> 0 Then 
    	Exit Sub 
    End If
    Thank you!

  7. #7
    New Member
    Join Date
    Nov 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: saving selection as text file

    Iím wondering if you could show a version of this code showing how to lock in the cell range and eliminate the cell range question.

    Thanks in advance for your assistance.

    JS

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,881
    Post Thanks / Like
    Mentioned
    384 Post(s)
    Tagged
    41 Thread(s)

    Default Re: saving selection as text file

    Like this
    Code:
    Sub myTSave()
    Dim myFolder As String
    'By Joe Was.
    'Save Range as Text File.
    
        ActiveSheet.Activate
        Range("A1:C10").Select
        Selection.Copy
        'This temporarily adds a sheet named "Test."
        Sheets.Add.Name = "Test"
        Sheets("Test").Select
        ActiveSheet.Paste
        'Ask user for folder to save text file to.
        myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
        'Save selected data as text file in users selected folder.
        ActiveWorkbook.SaveAs filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
        'Remove temporary sheet.
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        ActiveWindow.SelectedSheets.Delete
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        'Indicate save action.
        MsgBox "Text File: " & myFolder & "Saved!"
        'Go to top of sheet.
        Range("A1").Select
    End Sub
    Where the portion in red is the range to copy
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  9. #9
    New Member
    Join Date
    Nov 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: saving selection as text file

    Thank you for reviewing and correctly editing the cell range. However the result did not do what I was hoping for. This macro seems to be saving everything to text and in the excel text format. I was hoping to be able to save the desired cell data (H5:H23 data) to a simple note pad text file. Thanks again for your assistance and time. PS: Is it possible to also change the text file extension from "*.txt" to "*.CNC"?

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,881
    Post Thanks / Like
    Mentioned
    384 Post(s)
    Tagged
    41 Thread(s)

    Default Re: saving selection as text file

    This will save the file as an MSDOS text file with a file extension of cnc
    Code:
    Sub myTSave()
    Dim myFolder As String
    'By Joe Was.
    'Save Range as Text File.
    
        ActiveSheet.Activate
        Range("H5:H23").Select
        Selection.Copy
        'This temporarily adds a sheet named "Test."
        Sheets.Add.Name = "Test"
        Sheets("Test").Select
        ActiveSheet.Paste
        'Ask user for folder to save text file to.
        myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.cnc), *.cnc")
        'Save selected data as text file in users selected folder.
        ActiveWorkbook.SaveAs filename:=myFolder, FileFormat:=xlTextMSDOS, CreateBackup:=False
        'Remove temporary sheet.
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        ActiveWindow.SelectedSheets.Delete
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        'Indicate save action.
        MsgBox "Text File: " & myFolder & "Saved!"
        'Go to top of sheet.
        Range("A1").Select
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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