Saving a column of data in another file format

[Hari Prasadh]

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

[RB Smissaert]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Bob Phillips]

Hari,

How about this

Sub CreateSPS()
ActiveSheet.Copy
Rows("1:1").Delete Shift:=xlUp
Columns("B:IV").Delete Shift:=xlToLeft
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"C:\MyTest\Hari.sps", FileFormat:=xlTextMSDOS
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hari Prasadh" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Hari Prasadh]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>

[Hari Prasadh]

Hi Bob,

Thnx for a different way. Now, I know that anytime I want to save a workbook
in text file kind of thing I can use -- FileFormat:=xlTextMSDOS--

It's a little funny. If in VBA help I type -- xlTextMSDOS-- it will say --
please rephrase your question-- On the other hand if I type -- save as -- in
search and then go to -- save as method -- SaveAs method as it applies to
the Chart and Worksheet objects.-- then click on --see the FileFormat
property-- Read-only XlFileFormat -- I get a list which includes
xlTextMSDOS.

If the text xlTextMSDOS is there in the help file why doesnt my help show it
in the first enter itself. Does everybody have to go on a treasure hunt for
finding info or can i tweak something to do it in a more efficient manner.

Thanks a lot,
Hari
India

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Hari,
>
> How about this
>
> Sub CreateSPS()
> ActiveSheet.Copy
> Rows("1:1").Delete Shift:=xlUp
> Columns("B:IV").Delete Shift:=xlToLeft
> Application.DisplayAlerts = False
> ActiveWorkbook.SaveAs Filename:= _
> "C:\MyTest\Hari.sps", FileFormat:=xlTextMSDOS
> End Sub
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Hari Prasadh" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

[Hari Prasadh]

Hi Bob,

Thnx for a different way. Now, I know that anytime I want to save a workbook
in text file kind of thing I can use -- FileFormat:=xlTextMSDOS--

It's a little funny. If in VBA help I type -- xlTextMSDOS-- it will say --
please rephrase your question-- On the other hand if I type -- save as -- in
search and then go to -- save as method -- SaveAs method as it applies to
the Chart and Worksheet objects.-- then click on --see the FileFormat
property-- Read-only XlFileFormat -- I get a list which includes
xlTextMSDOS.

If the text xlTextMSDOS is there in the help file why doesnt my help show it
in the first enter itself. Does everybody have to go on a treasure hunt for
finding info or can i tweak something to do it in a more efficient manner.

Thanks a lot,
Hari
India

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Hari,
>
> How about this
>
> Sub CreateSPS()
> ActiveSheet.Copy
> Rows("1:1").Delete Shift:=xlUp
> Columns("B:IV").Delete Shift:=xlToLeft
> Application.DisplayAlerts = False
> ActiveWorkbook.SaveAs Filename:= _
> "C:\MyTest\Hari.sps", FileFormat:=xlTextMSDOS
> End Sub
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Hari Prasadh" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

[RB Smissaert]

a) Just type ByVal in the VBA help.

b) Plenty of information available about this, for example:
http://www.applecore99.com/gen/gen029.asp

RBS

"Hari Prasadh" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>
>

[RB Smissaert]

a) VBA help will tell you as well. Just type print. It is somewhere near the
bottom
of the list.

RBS


"Hari Prasadh" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>
>

[Bob Phillips]

Hi Hari,

I guess the reason is that xlTextMSDOS is an Excel constant, not a VBA
constant, so you don't see it in VBA help. But SaveAs can save in many
formats, so it provides a link of the available formats. To check this, I
just opened Word, and went into Word VBA and looked up SaveAs. I expected to
see the same help, but FileFormat to show me a different list. IT was very
similar, but it had a link called SaveAsFormat, not FileFormat. But the rest
was as expected.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hari Prasadh" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> Thnx for a different way. Now, I know that anytime I want to save a
workbook
> in text file kind of thing I can use -- FileFormat:=xlTextMSDOS--
>
> It's a little funny. If in VBA help I type -- xlTextMSDOS-- it will say --
> please rephrase your question-- On the other hand if I type -- save as --
in
> search and then go to -- save as method -- SaveAs method as it applies to
> the Chart and Worksheet objects.-- then click on --see the FileFormat
> property-- Read-only XlFileFormat -- I get a list which includes
> xlTextMSDOS.
>
> If the text xlTextMSDOS is there in the help file why doesnt my help show
it
> in the first enter itself. Does everybody have to go on a treasure hunt
for
> finding info or can i tweak something to do it in a more efficient manner.
>
> Thanks a lot,
> Hari
> India
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Hari,
> >
> > How about this
> >
> > Sub CreateSPS()
> > ActiveSheet.Copy
> > Rows("1:1").Delete Shift:=xlUp
> > Columns("B:IV").Delete Shift:=xlToLeft
> > Application.DisplayAlerts = False
> > ActiveWorkbook.SaveAs Filename:= _
> > "C:\MyTest\Hari.sps", FileFormat:=xlTextMSDOS
> > End Sub
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Hari Prasadh" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>
>

[Hari Prasadh]

Hi Bob,

> I guess the reason is that xlTextMSDOS is an Excel constant,...

I lost you there. If its not a Excel VBA but an excel constant then atleast
in the normal excel file help if i type -- xlTextMSDOS -- it should show,
but it doesnt. Im losing track of ur explanation somewhere.

Thanks a lot,
Hari
India

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Hi Hari,
>
> I guess the reason is that xlTextMSDOS is an Excel constant, not a VBA
> constant, so you don't see it in VBA help. But SaveAs can save in many
> formats, so it provides a link of the available formats. To check this, I
> just opened Word, and went into Word VBA and looked up SaveAs. I expected
> to
> see the same help, but FileFormat to show me a different list. IT was very
> similar, but it had a link called SaveAsFormat, not FileFormat. But the
> rest
> was as expected.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>

[Tom Ogilvy]

Help is designed to support the way one would normally look for help. If you
wanted to find help on saving files, you would look for help on save or save
as. You would probably have no knowledge that xlTextMSDos even existed as a
constant.

You can however go to the Object browser, select the excel library and
search for xlTextMSDos and it comes right up. You can see that it is part
of the constant group xlfileformat. Unfortunately, that does not lead you
directly to Saveas but you could search then on FileFormat (which does take
you to the list of fileformats).

Once you were familiar with the help on SAVEAS, you would remember the link
to fileformat and see what constants were available.

--
Regards,
Tom Ogilvy


"Hari Prasadh" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> > I guess the reason is that xlTextMSDOS is an Excel constant,...
>
> I lost you there. If its not a Excel VBA but an excel constant then
atleast
> in the normal excel file help if i type -- xlTextMSDOS -- it should show,
> but it doesnt. Im losing track of ur explanation somewhere.
>
> Thanks a lot,
> Hari
> India
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Hari,
> >
> > I guess the reason is that xlTextMSDOS is an Excel constant, not a VBA
> > constant, so you don't see it in VBA help. But SaveAs can save in many
> > formats, so it provides a link of the available formats. To check this,
I
> > just opened Word, and went into Word VBA and looked up SaveAs. I
expected
> > to
> > see the same help, but FileFormat to show me a different list. IT was
very
> > similar, but it had a link called SaveAsFormat, not FileFormat. But the
> > rest
> > was as expected.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
>
>

[Hari Prasadh]

Hi Tom,

Thnx for the reply.

>You would probably have no knowledge that xlTextMSDos even existed as a
>constant.
I agree with that statement fully. Actually many times what happens is I
might have only a faint idea of a particular keyword/concept (Like I might
come across a new keyword/concept in NG post or some website or through
personal experimentation). In order to get a better idea of the same I would
go to help. But it seems to be a catch-22 situation. In order to use Help
better you first need to have an idea of that so and so is part of a bigger
concept/keyword (Like Save As is parent of Xltextmsdos). What Help assumes
is that one is privileged to know about Child, only if one knows about the
parent, or one follows the path through parent. This is somewhat alien to
me. Havent the MS folks created a Complete INDEX of all the words (excluding
a, an, the etc) within Help?

To illustrate my point let me tell u this. Sometime back I was
programmatically opening a text file in XL and then saving it as a normal Xl
file (I have pasted that code at the end). This I did through macro
recording. When i examined the code it showed me that -- Xlnormal -- is the
syntax for saving a nonXL kind of file as XL.

Now, I wanted to learn more about this. So I typed --- xlnormal -- in Help
and I got the standard -- Please rephrase your question. What I want to say
from this is that in this case I was aware to a certain extent about the
Child, which is --- xlnormal -- but Help doesnt let me directly *google* it.
Seeing from the result I thought that probably --Xlnormal -- is not
documented in Help.

To add to the above I also couldnt understand that if I access Match
Function in VBA help, the same write-up comes up as in Excel Help. As far as
I understand when Match is to be used within VBA then one uses
Application.worksheetfunction.Match (please correct me if am wrong) , so
shouldnt the examples documented for match function within VBA help be
presented in a way which is suitable to the way we use it in VBA.

I will start getting used to Object browser. As compared to help I find
those things a little sophisticated, not comfortable - but as they say....
If I use it a few times then the interface would seem a little more
intuitive.

Regards,
Hari
India


Sub RenamingLSTasXLS()

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\abc\Desktop\Automate\Dev\From Client\Raw
Data\week 1\dev11112.lst", Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\abc\Desktop\Automate\Dev\Working
Files\Renaming\dev11112.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

[Tom Ogilvy]

Help on worksheet functions in the VBE are, as you observed, using the same
file as in Excel itself. so there is no specific help on using individual
worksheet functions in VBA.

Regards,
Tom Ogilvy

"Hari Prasadh" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom,
>
> Thnx for the reply.
>
> >You would probably have no knowledge that xlTextMSDos even existed as a
> >constant.
> I agree with that statement fully. Actually many times what happens is I
> might have only a faint idea of a particular keyword/concept (Like I might
> come across a new keyword/concept in NG post or some website or through
> personal experimentation). In order to get a better idea of the same I
would
> go to help. But it seems to be a catch-22 situation. In order to use Help
> better you first need to have an idea of that so and so is part of a
bigger
> concept/keyword (Like Save As is parent of Xltextmsdos). What Help assumes
> is that one is privileged to know about Child, only if one knows about the
> parent, or one follows the path through parent. This is somewhat alien to
> me. Havent the MS folks created a Complete INDEX of all the words
(excluding
> a, an, the etc) within Help?
>
> To illustrate my point let me tell u this. Sometime back I was
> programmatically opening a text file in XL and then saving it as a normal
Xl
> file (I have pasted that code at the end). This I did through macro
> recording. When i examined the code it showed me that -- Xlnormal -- is
the
> syntax for saving a nonXL kind of file as XL.
>
> Now, I wanted to learn more about this. So I typed --- xlnormal -- in Help
> and I got the standard -- Please rephrase your question. What I want to
say
> from this is that in this case I was aware to a certain extent about the
> Child, which is --- xlnormal -- but Help doesnt let me directly *google*
it.
> Seeing from the result I thought that probably --Xlnormal -- is not
> documented in Help.
>
> To add to the above I also couldnt understand that if I access Match
> Function in VBA help, the same write-up comes up as in Excel Help. As far
as
> I understand when Match is to be used within VBA then one uses
> Application.worksheetfunction.Match (please correct me if am wrong) , so
> shouldnt the examples documented for match function within VBA help be
> presented in a way which is suitable to the way we use it in VBA.
>
> I will start getting used to Object browser. As compared to help I find
> those things a little sophisticated, not comfortable - but as they say....
> If I use it a few times then the interface would seem a little more
> intuitive.
>
> Regards,
> Hari
> India
>
>
> Sub RenamingLSTasXLS()
>
> Workbooks.OpenText Filename:= _
> "C:\Documents and Settings\abc\Desktop\Automate\Dev\From
Client\Raw
> Data\week 1\dev11112.lst", Origin:=437, StartRow _
> :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
> Comma:=False _
> , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
> TrailingMinusNumbers:=True
>
> Application.DisplayAlerts = False
>
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Documents and Settings\abc\Desktop\Automate\Dev\Working
> Files\Renaming\dev11112.xls", FileFormat:=xlNormal, _
> Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
> CreateBackup:=False
>
> ActiveWorkbook.Close
> Application.DisplayAlerts = True
> End Sub
>
>