saving selection as text file

brian1

New Member
Joined
Jun 25, 2002
Messages
23
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?
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

u742884

Board Regular
Joined
Jun 23, 2002
Messages
126
You have to copy your range to another worksheet then save it as a .txt or .csv
 

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
815
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.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

lezloh

New Member
Joined
Jul 23, 2002
Messages
5
This works GREAT. Thanks
 

megnin

Active Member
Joined
Feb 27, 2002
Messages
340
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!
 

jaystub

New Member
Joined
Nov 12, 2017
Messages
6
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,053
Office Version
365
Platform
Windows
Like this
Code:
Sub myTSave()
Dim myFolder As String
'By Joe Was.
'Save Range as Text File.

    ActiveSheet.Activate
    [COLOR=#ff0000]Range("A1:C10")[/COLOR].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
 

jaystub

New Member
Joined
Nov 12, 2017
Messages
6
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"?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,053
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,288
Messages
5,449,470
Members
405,566
Latest member
JeIIyfish

This Week's Hot Topics

Top