Refering to Codenames in Remote Workbooks

  • Thread starter Thread starter Nigel RS
  • Start date Start date
N

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
 
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
 
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
 
Many thanks, ideal

Barb Reinhardt said:
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
Click to expand...
 
Thanks Bob, I will give it a try

Bob Phillips said:
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
Click to expand...
 
Bob,

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

Thanks,
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
 
Back
Top