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]

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