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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top