Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Worksheet Codename to Determine Worksheet Existence
Greetings,
I've got a worksheet codename (activeworkbook.activesheet.codename) and wish to use it to determine if the worksheet exists at a later point in time. Presuming I've stored the codename, looking for a way to see if worksheet exists (i.e. open) without knowing it's "friendly" name or worksheet index. Any ideas? Is it possible to get "friendly" name from codename? (then check friendly name) Other methods welcome. Thanks, Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Worksheet Codename to Determine Worksheet Existence
David,
you'll have to add some errorhandling yourself, but this works for me: Sub tst() MsgBox Code2Name(ActiveWorkbook, "sheet2") End Sub Function Code2Name(wb As Workbook, sCodeName As String) Dim c As New Collection, sh For Each sh In wb.Sheets c.Add sh.Name, sh.CodeName Next Code2Name = c(sCodeName) End Function keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David Copp" wrote: Greetings, I've got a worksheet codename (activeworkbook.activesheet.codename) and wish to use it to determine if the worksheet exists at a later point in time. Presuming I've stored the codename, looking for a way to see if worksheet exists (i.e. open) without knowing it's "friendly" name or worksheet index. Any ideas? Is it possible to get "friendly" name from codename? (then check friendly name) Other methods welcome. Thanks, Dave |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Worksheet Codename to Determine Worksheet Existence
Hi,
Thanks for the option. I was aware of this method but I'm looking for a direct method instead of iterating through all open workbooks. Thoughts? Thanks, Dave "keepITcool" wrote in message ... David, you'll have to add some errorhandling yourself, but this works for me: Sub tst() MsgBox Code2Name(ActiveWorkbook, "sheet2") End Sub Function Code2Name(wb As Workbook, sCodeName As String) Dim c As New Collection, sh For Each sh In wb.Sheets c.Add sh.Name, sh.CodeName Next Code2Name = c(sCodeName) End Function keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David Copp" wrote: Greetings, I've got a worksheet codename (activeworkbook.activesheet.codename) and wish to use it to determine if the worksheet exists at a later point in time. Presuming I've stored the codename, looking for a way to see if worksheet exists (i.e. open) without knowing it's "friendly" name or worksheet index. Any ideas? Is it possible to get "friendly" name from codename? (then check friendly name) Other methods welcome. Thanks, Dave |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Worksheet Codename to Determine Worksheet Existence
Can't imagine..
100 books 100 sheets in each book = .15 secs and why do you need to iterate thru workbooks? there's a direct way too, BUT your user needs to enable Access to Visual Basic Project Function CodeToName(wb As Workbook, codename As String) As String Dim vbp As Object On Error Resume Next Set vbp = wb.VBProject If Err = 1004 Then MsgBox _ "Please enable:" & vbNewLine & _ "'Trust access to Visual Basic Project'" & vbNewLine & _ "in Tools/Macro/Security (2ndtab)", vbExclamation, "Error" Exit Function End If With vbp.vbcomponents(codename) CodeToName = .Properties("Name") ' .Properties("Index") End With End Function keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David Copp" wrote: Hi, Thanks for the option. I was aware of this method but I'm looking for a direct method instead of iterating through all open workbooks. Thoughts? Thanks, Dave "keepITcool" wrote in message ... David, you'll have to add some errorhandling yourself, but this works for me: Sub tst() MsgBox Code2Name(ActiveWorkbook, "sheet2") End Sub Function Code2Name(wb As Workbook, sCodeName As String) Dim c As New Collection, sh For Each sh In wb.Sheets c.Add sh.Name, sh.CodeName Next Code2Name = c(sCodeName) End Function keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David Copp" wrote: Greetings, I've got a worksheet codename (activeworkbook.activesheet.codename) and wish to use it to determine if the worksheet exists at a later point in time. Presuming I've stored the codename, looking for a way to see if worksheet exists (i.e. open) without knowing it's "friendly" name or worksheet index. Any ideas? Is it possible to get "friendly" name from codename? (then check friendly name) Other methods welcome. Thanks, Dave |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Worksheet Codename to Determine Worksheet Existence
Thanks... it appears my assumption of a strong performance penalty may be in
correct... will test asap.. Enabling Visual Project option is unattractive. Thank you again Dave "keepITcool" wrote in message ... Can't imagine.. 100 books 100 sheets in each book = .15 secs and why do you need to iterate thru workbooks? there's a direct way too, BUT your user needs to enable Access to Visual Basic Project Function CodeToName(wb As Workbook, codename As String) As String Dim vbp As Object On Error Resume Next Set vbp = wb.VBProject If Err = 1004 Then MsgBox _ "Please enable:" & vbNewLine & _ "'Trust access to Visual Basic Project'" & vbNewLine & _ "in Tools/Macro/Security (2ndtab)", vbExclamation, "Error" Exit Function End If With vbp.vbcomponents(codename) CodeToName = .Properties("Name") ' .Properties("Index") End With End Function keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David Copp" wrote: Hi, Thanks for the option. I was aware of this method but I'm looking for a direct method instead of iterating through all open workbooks. Thoughts? Thanks, Dave "keepITcool" wrote in message ... David, you'll have to add some errorhandling yourself, but this works for me: Sub tst() MsgBox Code2Name(ActiveWorkbook, "sheet2") End Sub Function Code2Name(wb As Workbook, sCodeName As String) Dim c As New Collection, sh For Each sh In wb.Sheets c.Add sh.Name, sh.CodeName Next Code2Name = c(sCodeName) End Function keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David Copp" wrote: Greetings, I've got a worksheet codename (activeworkbook.activesheet.codename) and wish to use it to determine if the worksheet exists at a later point in time. Presuming I've stored the codename, looking for a way to see if worksheet exists (i.e. open) without knowing it's "friendly" name or worksheet index. Any ideas? Is it possible to get "friendly" name from codename? (then check friendly name) Other methods welcome. Thanks, Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for the existence of a worksheet | Excel Discussion (Misc queries) | |||
How do I check for existence of a worksheet? | Excel Worksheet Functions | |||
Referencing worksheet CODENAME in a chart object. | Excel Discussion (Misc queries) | |||
Worksheet codename | Excel Programming | |||
How do I determine the Acrobat Distiller codename? | Excel Programming |