Hi RBS,

Your macro works great for me.

I wanted to understand some of the new stuff in you code.

a) what does the following statement's do
> Print #hFile, strRange;

b) Whats the difference between "Byref" and "ByVal". While writing
sub/functions I have never used them. I directly used to write like -- Sub
ColumnRangeToText( rngCol As Range, strFile As String)
What would be the specific advantage of using "Byref" and "ByVal" within a
sub or a function

Thanks a lot,
Hari
India

"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:OHHS6SuBFHA.2032@tk2msftngp13.phx.gbl...
> 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" <excel_hari@hotmail.com> wrote in message
> news:uR33rytBFHA.1836@tk2msftngp13.phx.gbl...
>> 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
>>
>