referencing previously selected cells after macro execution begins

[Guest]

Apologies in advance for this stupid question. I am attempting to write a
short macro for a co-worker that allows a user to select a range of
spreadsheet data then save the selected data to a text file. How do you
select the data then start the macro and reference the selected data within
the macro? Everything I've tried chokes during debug. The operation seems
trivial but apparently to this old boy , isn't.

 

[Tom Ogilvy]

Sub MyMacro()
Dim rng as Range
On Error Resume Next
set rng = application.InputBox("With the mouse, " & _
"Select the range to convert to text file",type:=8)
On Error goto 0
if rng is nothing then
msgbox "You hit cancel, bye!"
exit sub
End if
rng.Select
msgbox rng.Address
End Sub

 

[GJones]

Hi Glen;

Try this

Sub try()
Cells.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Administrator\My
Documents\MyTextFile.csv", _
FileFormat:=xlCSV, CreateBackup:=False
Range("A1").Select
End Sub

 

[Tom Ogilvy]

you can address the selection with the object

selection

strangely enough

msgbox Selection.Address

note that selection is not always a range and would raise an error if it
were not (but then you couldn't use it anyway).

 

[Guest]

Tom, this is great for selecting the range but now what are the commands to
convert that range to text?

Thanks,
Mark Dyches

 

[Dave Peterson]

Here's a couple of links that'll get you further along.

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html