codenames

[CTSCampbell]

Hi all,

I was wondering if there is anyway to reference a sheet by codename
that is in a different workbook than the one in which your macro
resides. The reason is, I need to access specific series of sheets
arbitrary in number in a series of other workbooks, the sheet names are
a character string as in "X sheet 1 of Y" Where Y is unknown; however,
the codenames are an incremented number, which would be much easier to
deal with.

Thanks for you help in advance,
Chris

 

[Tom Ogilvy]

for a codename of Sheet2 for example: (the tab name is not sheet2)

dim s as String, sh as worksheet
Dim bk as Workbook
set bk = Workbooks("OtherWorkbookName.xls")
s = bk.vbProject.VBComponents("Sheet2").Properties("Name").Value
set sh = bk.Worksheets(s)

in xl2003, these are the properties of a worksheet: (and values for this
specifc sheet as an example):

1 Application
2 Creator 1480803660
3 Parent
4 CodeName Sheet2
5 _CodeName Sheet2
6 Index 2
7 Name MDMS_20061018_FY09_13
8 Next
9 OnDoubleClick
10 OnSheetActivate
11 OnSheetDeactivate
12 PageSetup
13 Previous
14 ProtectContents False
15 ProtectDrawingObjects False
16 ProtectionMode False
17 ProtectScenarios False
18 Visible -1
19 Shapes
20 TransitionExpEval False
21 AutoFilterMode True
22 EnableCalculation True
23 Cells
24 CircularReference
25 Columns
26 ConsolidationFunction -4157
27 ConsolidationOptions
28 ConsolidationSources
29 DisplayAutomaticPageBreaks False
30 EnableAutoFilter False
31 EnableSelection 0
32 EnableOutlining False
33 EnablePivotTable False
34 FilterMode True
35 Names
36 OnCalculate
37 OnData
38 OnEntry
39 Outline
41 Rows
42 ScrollArea
43 StandardHeight 12.75
44 StandardWidth 8.43
45 TransitionFormEntry False
46 Type -4167
47 UsedRange
48 HPageBreaks
49 VPageBreaks
50 QueryTables
51 DisplayPageBreaks False
52 Comments
53 Hyperlinks
54 _DisplayRightToLeft False
55 AutoFilter
56 DisplayRightToLeft False
57 Scripts
58 Tab
59 MailEnvelope
60 CustomProperties
61 SmartTags
62 Protection
63 ListObjects

code to get them (in the immediate window)

Sub abc()
On Error Resume Next
i = 1
For Each pr In
Workbooks("MDMS_20061018_FY09_13.xls").VBProject.VBComponents("Sheet2").Properties
Debug.Print i, pr.Name, pr.Value
i = i + 1
Next
On Error GoTo 0
End Sub

 

[Peter T]

Perhaps you could loop worksheet names until you find your hard-coded
CodeName. Or store all in a collection, eg

Dim mColWSnames As Collection

Sub SetWScollection(wb As Workbook)
Dim ws As Worksheet

Set mColWSnames = New Collection
For Each ws In wb.Worksheets
mColWSnames.Add ws.Name, ws.CodeName
Next

End Sub

Sub Test()
Dim sCodename As String
Dim ws As Worksheet

sCodename = ActiveWorkbook.Worksheets(2).CodeName 'normally already known
ActiveWorkbook.Worksheets(2).Name = "NewName"
SetWScollection ActiveWorkbook

Set ws = ActiveWorkbook.Worksheets(mColWSnames("Sheet2"))
MsgBox ws.Name, , sCodename
End Sub

Regards,
Peter T

 

[CTSCampbell]

Tom,

Thanks for your help and prompt response.

Chris