Refering to Codenames in Remote Workbooks

[Nigel RS]

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 Phillips]

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_codename").Properties("Name")))
MsgBox sh.Name
End With

 

[Barb Reinhardt]

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

 

[Nigel RS]

Many thanks, ideal

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

 

[Nigel RS]

Thanks Bob, I will give it a try

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_codename").Properties("Name")))
MsgBox sh.Name
End With


--
__________________________________
HTH

Bob

 

[Barb Reinhardt]

Bob,

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

Thanks,
Barb Reinhardt

 

[Bob Phillips]

What are you thinking about Barb?

 

[Barb Reinhardt]

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