Playing around with Syntax

[Hari Prasadh]

Hi,

Some problem with syntax below.

sub try()
dim sh as worksheet
Dim NameOfOSWorkbook As Workbook
Dim NameOfOEWorkbook As Workbook
NameOfOSWorkbook.Name = "Open end data (OS).xls"
NameOfOEWorkbook.Name = "Open end data (OE).xls"
For each sh in NameOfOSWorkbook
...
...
...
Call readingarrayofuniquewords(sh)
...
...
...
Next sh

End sub


Im getting -- Compile error : cant assign to read-only property-- at the
line -- NameOfOSWorkbook.Name = "Open end data (OS).xls"

Why am I getting the same. If possible please point out the flaw in my
understanding (When we could write a statement like --
activeworkbook.name -- then why not -- NameOfOSWorkbook.Name --

Basically I wanted to store a workbook's name in one place so that if it
changes I could change it at one place.

I have gotten around the above problem in another circuitous manner, given
below.

Dim sh As Worksheet
Dim NameOfOSWorkbook As String
Dim NameOfOEWorkbook As String
NameOfOSWorkbook = "Open end data (OS).xls"
NameOfOEWorkbook = "Open end data (OE).xls"
For each sh in Workbooks(NameOfOSWorkbook)
...
...
...
Call readingarrayofuniquewords(sh)
...
...
...
Next sh

End sub

But now am getting -- compile error ByRef argument type mismatch -- in the
line Call readingarrayofuniquewords(sh). Basically I wanted to pass the name
of the worksheet sh. When I changed it to -- Call
readingarrayofuniquewords(sh.name) -- things were fine.

Is there a more efficient way to code the above (I keep doing things by
trial and error).

Thanks a lot,
Hari
India

[ben]

notice that error message
"Can not assign property to READ-ONLY"
workbooks("whatever").name
is READ ONLY you can not assign it, as far as I know the only way to change
a workbook name is to use
SaveAs
method. Anyone else know of something else?

"Hari Prasadh" wrote:

> Hi,
>
> Some problem with syntax below.
>
> sub try()
> dim sh as worksheet
> Dim NameOfOSWorkbook As Workbook
> Dim NameOfOEWorkbook As Workbook
> NameOfOSWorkbook.Name = "Open end data (OS).xls"
> NameOfOEWorkbook.Name = "Open end data (OE).xls"
> For each sh in NameOfOSWorkbook
> ...
> ...
> ...
> Call readingarrayofuniquewords(sh)
> ...
> ...
> ...
> Next sh
>
> End sub
>
>
> Im getting -- Compile error : cant assign to read-only property-- at the
> line -- NameOfOSWorkbook.Name = "Open end data (OS).xls"
>
> Why am I getting the same. If possible please point out the flaw in my
> understanding (When we could write a statement like --
> activeworkbook.name -- then why not -- NameOfOSWorkbook.Name --
>
> Basically I wanted to store a workbook's name in one place so that if it
> changes I could change it at one place.
>
> I have gotten around the above problem in another circuitous manner, given
> below.
>
> Dim sh As Worksheet
> Dim NameOfOSWorkbook As String
> Dim NameOfOEWorkbook As String
> NameOfOSWorkbook = "Open end data (OS).xls"
> NameOfOEWorkbook = "Open end data (OE).xls"
> For each sh in Workbooks(NameOfOSWorkbook)
> ...
> ...
> ...
> Call readingarrayofuniquewords(sh)
> ...
> ...
> ...
> Next sh
>
> End sub
>
> But now am getting -- compile error ByRef argument type mismatch -- in the
> line Call readingarrayofuniquewords(sh). Basically I wanted to pass the name
> of the worksheet sh. When I changed it to -- Call
> readingarrayofuniquewords(sh.name) -- things were fine.
>
> Is there a more efficient way to code the above (I keep doing things by
> trial and error).
>
> Thanks a lot,
> Hari
> India
>
>
>

[Hari Prasadh]

Hi Ben,

Thnx for the reply.

Actually my intention was not to change the Name of the workbook. Rather I
want to store the name of the workbook in a String such as --
NameOfOSWorkbook -- and then through out my code whenever I wanted to refer
the workbook I wanted to use the name of the string -- NameOfOSWorkbook --
rather than using the actual name of the workbook.

This way if the workbook name changes I would have to change the code at one
line, which is to change the right hand side of the code --
NameOfOSWorkbook.Name = "Open end data (OS).xls" --

Ok, now I understand that NameOfOSWorkbook.Name is erroneous because it
implies changing name of the workbook.

So, does that mean the following is the ONLY way to reference a workbook
(NameOfOSWorkbook). Or is there a more efficient method?

Dim sh As Worksheet
Dim NameOfOSWorkbook As String
Dim NameOfOEWorkbook As String
NameOfOSWorkbook = "Open end data (OS).xls"
NameOfOEWorkbook = "Open end data (OE).xls"
For Each sh In Workbooks(NameOfOSWorkbook).Worksheets
...
...
...
Call readingarrayofuniquewords(sh.name)
...
...
...
Next sh



Thanks a lot,
Hari
India