Something like this will do it.


Sub ColumnRangeToText(ByRef rngCol As Range, _
ByVal strFile As String)

Dim strRange As String
Dim arr
Dim LR As Long
Dim i As Long
Dim hFile As Long

arr = rngCol

LR = UBound(arr)

For i = 1 To LR - 1
strRange = strRange & arr(i, 1) & vbCrLf
Next

strRange = strRange & arr(LR, 1)

hFile = FreeFile

Open strFile For Output As hFile

Print #hFile, strRange;
Close #hFile

End Sub


Sub test2()
ColumnRangeToText Range(Cells(1), Cells(6, 1)), "C:\test.sps"
End Sub


RBS



"Hari Prasadh" wrote in message
...
Hi,

I want to copy data in column A (starting from row number 2 to variable
length) and save it as a *.sps file. (Formats dont matter, just the
values to be copied)

Basically .sps is syntax file format of SPSS.

I Started the macro recorder and then copied the specified range, went
to -- Start -- Run -- Notepad - Ctrl +V - Ctrl +S - then choose file
extension as all files and typed the name as -- trying.sps -- (I didnt
open SPSS and then a new syntax file and then paste it there because it
takes some time, so i thought why not paste in to Notepad and save it as
*.Sps extension. --- Basically if a notepad is saved as .sps extension it
cane be opened up in spss for viewing)

And the result was

Sub Macro1()

Range("a2:a2050").Select
Selection.Copy

End Sub

Macro recorder has not recorded any action outside the excel environment.
How do I go about it?

I have modified the above code to below.. Please guide me beyond this.

Range(Cells(2, "a"), Selection.End(xlDown)).Select
Selection.Copy


Thanks a lot,
Hari
India