Save selected cells to a Text file

[Al]

I am trying to write a macro to allow the user to select part of a column
then save the selected cells to a TXT file, here is what I have so far but it
does not work - there may also be a better way to code such a macro ? I am
pretty much a beginner.

Sub outputtext()

Set r = Application.InputBox(prompt:="Select a range of cells", Type:=8)
ActiveSheet.Range("r").Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Book1.txt", _
FileFormat:=xlText, CreateBackup:=False
Sheets("Book1").Select
ActiveWindow.SelectedSheets.Delete
End Sub

it failes at the line ActiveSheet.Range("r").Select

can somone help me?
Thanks
Al

 

[Will Cairns]

I am trying to write a macro to allow the user to select part of a column
then save the selected cells to a TXT file, here is what I have so far butit
does not work - there may also be a better way to code such a macro ? I am
pretty much a beginner.

Sub outputtext()

    Set r = Application.InputBox(prompt:="Select a range of cells", Type:=8)
    ActiveSheet.Range("r").Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:="C:\Book1.txt", _
        FileFormat:=xlText, CreateBackup:=False
       Sheets("Book1").Select
    ActiveWindow.SelectedSheets.Delete
End Sub

it failes at the line   ActiveSheet.Range("r").Select

can somone help me?
Thanks
Al

Should be r without quotes.

 

[Dave Peterson]

r is already a range, so you don't put it inside the activesheet.range().

Sub outputtext()

dim r as range
set r = nothing
on error resume next
Set r = Application.InputBox(prompt:="Select a range of cells", Type:=8)
on error goto 0

if r is nothing then
exit sub 'user hit cancel
end if

workbooks.add(1) 'new workbook, single sheet

r.copy _
destination:=activesheet.range("a1")

Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:="C:\Book1.txt", _
FileFormat:=xlText, CreateBackup:=False

activeworkbook.close savechanges:=false

End Sub

(Untested, uncompiled)

 

[Al]

Thank-you very much !!

Have a good day

Al

r is already a range, so you don't put it inside the activesheet.range().

Sub outputtext()

dim r as range
set r = nothing
on error resume next
Set r = Application.InputBox(prompt:="Select a range of cells", Type:=8)
on error goto 0

if r is nothing then
exit sub 'user hit cancel
end if

workbooks.add(1) 'new workbook, single sheet

r.copy _
destination:=activesheet.range("a1")

Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:="C:\Book1.txt", _
FileFormat:=xlText, CreateBackup:=False

activeworkbook.close savechanges:=false

End Sub

(Untested, uncompiled)

 

[Lars-Åke Aspelin]

I am trying to write a macro to allow the user to select part of a column
then save the selected cells to a TXT file, here is what I have so far but it
does not work - there may also be a better way to code such a macro ? I am
pretty much a beginner.

Sub outputtext()

Set r = Application.InputBox(prompt:="Select a range of cells", Type:=8)
ActiveSheet.Range("r").Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Book1.txt", _
FileFormat:=xlText, CreateBackup:=False
Sheets("Book1").Select
ActiveWindow.SelectedSheets.Delete
End Sub

it failes at the line ActiveSheet.Range("r").Select

can somone help me?
Thanks
Al

Replace the line Activesheet.Range("r").Select with just
r.Select

I also chose to replace "C:\Book1.txt" with just "Book1.txt" thus
having the file in the same directory as the Excel file.

Finally replace ActiveWindow.SelectedSheets.Delete with
ActiveWindow.SelectedSheets(1).Delete

Hope this helps. / Lars-Åke

 

[Al]

Thank-you all for the responses

I ran into a problem however and am hoping one of you can help

The selected text is a referenced cell and therefore I have to use Paste
Special, Values, and then to my dismay I noticed some of the cells contain a
double quote " and when I save those cells as value, Excel puts double-quotes
around each piece of text in that cell. It works OK if you paste directly
into Notapas but that is way beyond my knowledge of VBA -
Dave, is it possible to modify the macro you supplied to paste the cells
into Notepad instead?

Thanks in advance
Al

 

[Al]

Thank-you all for the responses

I ran into a problem however and am hoping one of you can help

The selected text is a referenced cell and therefore I have to use Paste
Special, Values, and then to my dismay I noticed some of the cells contain a
double quote " and when I save those cells as value, Excel puts double-quotes
around each piece of text in that cell. It works OK if you paste directly
into Notapas but that is way beyond my knowledge of VBA -
Dave, is it possible to modify the macro you supplied to paste the cells
into Notepad instead?

Thanks in advance
Al