I have an application that create a number of separate workbooks, identicai
in format.. Each workbook has a series of worksheets, each having a codename.

I now have master workbook that opens each of the above in turn. I wish to
refer to the worksheets by their codename, but my master application fails to
compile as the codename is not recognised.

Is there a way around this? I do not wish to refer to the remote worksheets
by name as these may have changed.

Cheers

What you can do is get the worksheet name from the codename and use that.
For example

With Workbooks("Some other workbook.xls")
Set sh =
..Worksheets(CStr(.VBProject.VBComponents("sheet_c odename").Properties("Name")))
MsgBox sh.Name
End With


--
__________________________________
HTH

Bob

"Nigel RS" wrote in message
...
I have an application that create a number of separate workbooks, identicai
in format.. Each workbook has a series of worksheets, each having a
codename.

I now have master workbook that opens each of the above in turn. I wish
to
refer to the worksheets by their codename, but my master application fails
to
compile as the codename is not recognised.

Is there a way around this? I do not wish to refer to the remote
worksheets
by name as these may have changed.

Cheers

Thanks Bob, I will give it a try

"Bob Phillips" wrote:

What you can do is get the worksheet name from the codename and use that.
For example

With Workbooks("Some other workbook.xls")
Set sh =
..Worksheets(CStr(.VBProject.VBComponents("sheet_c odename").Properties("Name")))
MsgBox sh.Name
End With


--
__________________________________
HTH

Bob

"Nigel RS" wrote in message
...
I have an application that create a number of separate workbooks, identicai
in format.. Each workbook has a series of worksheets, each having a
codename.

I now have master workbook that opens each of the above in turn. I wish
to
refer to the worksheets by their codename, but my master application fails
to
compile as the codename is not recognised.

Is there a way around this? I do not wish to refer to the remote
worksheets
by name as these may have changed.

Cheers

Bob,

I suspect that additional resources need to be selected. Let me know if I'm
wrong.

Thanks,
Barb Reinhardt




"Nigel RS" wrote:

Thanks Bob, I will give it a try

"Bob Phillips" wrote:

What you can do is get the worksheet name from the codename and use that.
For example

With Workbooks("Some other workbook.xls")
Set sh =
..Worksheets(CStr(.VBProject.VBComponents("sheet_c odename").Properties("Name")))
MsgBox sh.Name
End With


--
__________________________________
HTH

Bob

"Nigel RS" wrote in message
...
I have an application that create a number of separate workbooks, identicai
in format.. Each workbook has a series of worksheets, each having a
codename.

I now have master workbook that opens each of the above in turn. I wish
to
refer to the worksheets by their codename, but my master application fails
to
compile as the codename is not recognised.

Is there a way around this? I do not wish to refer to the remote
worksheets
by name as these may have changed.

Cheers

What are you thinking about Barb?

--
__________________________________
HTH

Bob

"Barb Reinhardt" wrote in message
...
Bob,

I suspect that additional resources need to be selected. Let me know if
I'm
wrong.

Thanks,
Barb Reinhardt




"Nigel RS" wrote:

Thanks Bob, I will give it a try

"Bob Phillips" wrote:

What you can do is get the worksheet name from the codename and use
that.
For example

With Workbooks("Some other workbook.xls")
Set sh =
..Worksheets(CStr(.VBProject.VBComponents("sheet_c odename").Properties("Name")))
MsgBox sh.Name
End With


--
__________________________________
HTH

Bob

"Nigel RS" wrote in message
...
I have an application that create a number of separate workbooks,
identicai
in format.. Each workbook has a series of worksheets, each having a
codename.

I now have master workbook that opens each of the above in turn. I
wish
to
refer to the worksheets by their codename, but my master application
fails
to
compile as the codename is not recognised.

Is there a way around this? I do not wish to refer to the remote
worksheets
by name as these may have changed.

Cheers

I'm thinking about the resources that are used when you do things in the VBE
programmatically. The Extensibility one? I've since tested this and I've
got what I need. I like your way much better than mine!

Thanks,
Barb Reinhardt




"Bob Phillips" wrote:

What are you thinking about Barb?

--
__________________________________
HTH

Bob

"Barb Reinhardt" wrote in message
...
Bob,

I suspect that additional resources need to be selected. Let me know if
I'm
wrong.

Thanks,
Barb Reinhardt




"Nigel RS" wrote:

Thanks Bob, I will give it a try

"Bob Phillips" wrote:

What you can do is get the worksheet name from the codename and use
that.
For example

With Workbooks("Some other workbook.xls")
Set sh =
..Worksheets(CStr(.VBProject.VBComponents("sheet_c odename").Properties("Name")))
MsgBox sh.Name
End With


--
__________________________________
HTH

Bob

"Nigel RS" wrote in message
...
I have an application that create a number of separate workbooks,
identicai
in format.. Each workbook has a series of worksheets, each having a
codename.

I now have master workbook that opens each of the above in turn. I
wish
to
refer to the worksheets by their codename, but my master application
fails
to
compile as the codename is not recognised.

Is there a way around this? I do not wish to refer to the remote
worksheets
by name as these may have changed.

Cheers

You'd have to do something like this

Public Function FindWorksheet(myXLBook As Excel.Workbook, _
myCodeName as String) As Excel.Worksheet

Dim aWS As Excel.Worksheet
For Each aWS In myXLBook.Worksheets

If aWS.CodeName = myCodeName Then
Set FindWorksheet = aWS
Exit Function
End If
Next aWS

Set aWS = Nothing

End Function
--
HTH,
Barb Reinhardt




"Nigel RS" wrote:

I have an application that create a number of separate workbooks, identicai
in format.. Each workbook has a series of worksheets, each having a codename.

I now have master workbook that opens each of the above in turn. I wish to
refer to the worksheets by their codename, but my master application fails to
compile as the codename is not recognised.

Is there a way around this? I do not wish to refer to the remote worksheets
by name as these may have changed.

Cheers

Many thanks, ideal

"Barb Reinhardt" wrote:

You'd have to do something like this

Public Function FindWorksheet(myXLBook As Excel.Workbook, _
myCodeName as String) As Excel.Worksheet

Dim aWS As Excel.Worksheet
For Each aWS In myXLBook.Worksheets

If aWS.CodeName = myCodeName Then
Set FindWorksheet = aWS
Exit Function
End If
Next aWS

Set aWS = Nothing

End Function
--
HTH,
Barb Reinhardt




"Nigel RS" wrote:

I have an application that create a number of separate workbooks, identicai
in format.. Each workbook has a series of worksheets, each having a codename.

I now have master workbook that opens each of the above in turn. I wish to
refer to the worksheets by their codename, but my master application fails to
compile as the codename is not recognised.

Is there a way around this? I do not wish to refer to the remote worksheets
by name as these may have changed.

Cheers