Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
codenames
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
codenames
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("Na me").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.V BComponents("Sheet2").Properties Debug.Print i, pr.Name, pr.Value i = i + 1 Next On Error GoTo 0 End Sub -- Regards, Tom Ogilvy " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
codenames
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 wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Codenames | Excel Programming | |||
how to use codenames with an addin? | Excel Programming | |||
using variables in codenames | Excel Programming | |||
Excel Worksheet Codenames 2 | Excel Programming | |||
Using worksheet codenames | Excel Programming |