saving selection as text file

[brian1]

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?

 

[u742884]

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

 

[steve case]

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]

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]

This works GREAT. Thanks

 

[megnin]

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:

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]

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]

Like this

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]

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]

This will save the file as an MSDOS text file with a file extension of cnc

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