Programmatically determining CODE NAME for sheet based upon Sheet

[Barb Reinhardt]

I have about 20 workbooks that I want to extract the code name for a workbook
I'll call oWB. Let's say the the sheet names are "SUMMARY_1" and
"SUMMARY_2". How do I determine what the code names are for each sheet in
the workbook?

Thanks,
Barb Reinhardt

[Jim Thomlinson]

This code should be close... ( I tested it locally but you should be able to
adapt it for your 20 workbooks)

Sub test()
Dim oWB As Workbook
Dim oSH As Worksheet

Set oWB = ThisWorkbook
On Error Resume Next
Set oSH = oWB.Sheets("SUMMARY_1")
On Error GoTo 0
If oSH Is Nothing Then
MsgBox "SUMMARY_1 does not exist"
Else
MsgBox "SUMMARY_1's code name is " & oSH.CodeName
End If
End Sub

--
HTH...

Jim Thomlinson


"Barb Reinhardt" wrote:

> I have about 20 workbooks that I want to extract the code name for a workbook
> I'll call oWB. Let's say the the sheet names are "SUMMARY_1" and
> "SUMMARY_2". How do I determine what the code names are for each sheet in
> the workbook?
>
> Thanks,
> Barb Reinhardt
>
>

[Tom Ogilvy]

In some versions of Excel, this might be problemmatic if the VBE isn't open.
I believe Chip Pearson shows a more robust way at
http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

> This code should be close... ( I tested it locally but you should be able to
> adapt it for your 20 workbooks)
>
> Sub test()
> Dim oWB As Workbook
> Dim oSH As Worksheet
>
> Set oWB = ThisWorkbook
> On Error Resume Next
> Set oSH = oWB.Sheets("SUMMARY_1")
> On Error GoTo 0
> If oSH Is Nothing Then
> MsgBox "SUMMARY_1 does not exist"
> Else
> MsgBox "SUMMARY_1's code name is " & oSH.CodeName
> End If
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Barb Reinhardt" wrote:
>
> > I have about 20 workbooks that I want to extract the code name for a workbook
> > I'll call oWB. Let's say the the sheet names are "SUMMARY_1" and
> > "SUMMARY_2". How do I determine what the code names are for each sheet in
> > the workbook?
> >
> > Thanks,
> > Barb Reinhardt
> >
> >

[Jim Thomlinson]

Ok, I'm lost. What part of my code requires the VBE to be open? I don't doubt
you (since that has almost never worked out in the past) but I fail to see
what part of my code requires the VBE as I do not work with VBE components in
any way...
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

> In some versions of Excel, this might be problemmatic if the VBE isn't open.
> I believe Chip Pearson shows a more robust way at
> http://www.cpearson.com/excel/vbe.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jim Thomlinson" wrote:
>
> > This code should be close... ( I tested it locally but you should be able to
> > adapt it for your 20 workbooks)
> >
> > Sub test()
> > Dim oWB As Workbook
> > Dim oSH As Worksheet
> >
> > Set oWB = ThisWorkbook
> > On Error Resume Next
> > Set oSH = oWB.Sheets("SUMMARY_1")
> > On Error GoTo 0
> > If oSH Is Nothing Then
> > MsgBox "SUMMARY_1 does not exist"
> > Else
> > MsgBox "SUMMARY_1's code name is " & oSH.CodeName
> > End If
> > End Sub
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > I have about 20 workbooks that I want to extract the code name for a workbook
> > > I'll call oWB. Let's say the the sheet names are "SUMMARY_1" and
> > > "SUMMARY_2". How do I determine what the code names are for each sheet in
> > > the workbook?
> > >
> > > Thanks,
> > > Barb Reinhardt
> > >
> > >

[Dave Peterson]

IIRC, there can be a problem getting the codename of a worksheet that is added
in code. Maybe Tom is remembering that--or I'm remembering incorrectly.



Jim Thomlinson wrote:
>
> Ok, I'm lost. What part of my code requires the VBE to be open? I don't doubt
> you (since that has almost never worked out in the past) but I fail to see
> what part of my code requires the VBE as I do not work with VBE components in
> any way...
> --
> HTH...
>
> Jim Thomlinson
>
> "Tom Ogilvy" wrote:
>
> > In some versions of Excel, this might be problemmatic if the VBE isn't open.
> > I believe Chip Pearson shows a more robust way at
> > http://www.cpearson.com/excel/vbe.htm
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > This code should be close... ( I tested it locally but you should be able to
> > > adapt it for your 20 workbooks)
> > >
> > > Sub test()
> > > Dim oWB As Workbook
> > > Dim oSH As Worksheet
> > >
> > > Set oWB = ThisWorkbook
> > > On Error Resume Next
> > > Set oSH = oWB.Sheets("SUMMARY_1")
> > > On Error GoTo 0
> > > If oSH Is Nothing Then
> > > MsgBox "SUMMARY_1 does not exist"
> > > Else
> > > MsgBox "SUMMARY_1's code name is " & oSH.CodeName
> > > End If
> > > End Sub
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > I have about 20 workbooks that I want to extract the code name for a workbook
> > > > I'll call oWB. Let's say the the sheet names are "SUMMARY_1" and
> > > > "SUMMARY_2". How do I determine what the code names are for each sheet in
> > > > the workbook?
> > > >
> > > > Thanks,
> > > > Barb Reinhardt
> > > >
> > > >

--

Dave Peterson

[Jim Thomlinson]

I've seen that (and I agree) but it seems more like Barb is trying to get the
code name of an existing sheet not a sheet she just added. Chip's site
alludes to something about xl95 and code names but nothing that indicates
that a sheet code name will be an issue...

--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

> IIRC, there can be a problem getting the codename of a worksheet that is added
> in code. Maybe Tom is remembering that--or I'm remembering incorrectly.
>
>
>
> Jim Thomlinson wrote:
> >
> > Ok, I'm lost. What part of my code requires the VBE to be open? I don't doubt
> > you (since that has almost never worked out in the past) but I fail to see
> > what part of my code requires the VBE as I do not work with VBE components in
> > any way...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> > "Tom Ogilvy" wrote:
> >
> > > In some versions of Excel, this might be problemmatic if the VBE isn't open.
> > > I believe Chip Pearson shows a more robust way at
> > > http://www.cpearson.com/excel/vbe.htm
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > This code should be close... ( I tested it locally but you should be able to
> > > > adapt it for your 20 workbooks)
> > > >
> > > > Sub test()
> > > > Dim oWB As Workbook
> > > > Dim oSH As Worksheet
> > > >
> > > > Set oWB = ThisWorkbook
> > > > On Error Resume Next
> > > > Set oSH = oWB.Sheets("SUMMARY_1")
> > > > On Error GoTo 0
> > > > If oSH Is Nothing Then
> > > > MsgBox "SUMMARY_1 does not exist"
> > > > Else
> > > > MsgBox "SUMMARY_1's code name is " & oSH.CodeName
> > > > End If
> > > > End Sub
> > > >
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Barb Reinhardt" wrote:
> > > >
> > > > > I have about 20 workbooks that I want to extract the code name for a workbook
> > > > > I'll call oWB. Let's say the the sheet names are "SUMMARY_1" and
> > > > > "SUMMARY_2". How do I determine what the code names are for each sheet in
> > > > > the workbook?
> > > > >
> > > > > Thanks,
> > > > > Barb Reinhardt
> > > > >
> > > > >
>
> --
>
> Dave Peterson
>

[Rob Bovey]

Hi Jim,

If the VBProject for a workbook has never been altered (e.g. the
workbook has only been operated on from the Excel UI), then it really
doesn't have a VBProject and you can't even depend on the CodeNames. For
example, if you insert a new worksheet in the middle of existing sheets the
CodeNames of all worksheets after the one you inserted will change.

Try this: Create a new workbook with two worksheets and save it. Open
the VBE and you'll see that the CodeNames correspond to the sheet tab names.
Insert a new worksheet between Sheet1 and Sheet2. In the VBE it still shows
the CodeNames as you'd expect. Now save the workbook, close it and reopen
it. The CodeNames are now out of order. Sheet3 has a CodeName of Sheet2 and
Sheet2 has a CodeName of Sheet3.

The reason this happens is because until you do something to manually
alter the VBProject of a workbook, VBA considers it not to have one. VBA
regenerates the VBProject each time you open the workbook, sometimes with
conflicting results.

This also will occasionally cause problems getting access to anything
below the VBProject object of the workbook when the VBE is not open because
VBA may not yet have generated a default VBProject for workbooks with
unedited VBProjects. In my experience you can force VBA to "wake up" by
using the VBProject object of the target workbook in some trivial way:

If wkbBook.VBProject.Protection = 0 Then
''' Calls on VBComponents should succeed now
End If

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:453CB085-7574-4C79-AC30-B7262B1ABF0B@microsoft.com...
> Ok, I'm lost. What part of my code requires the VBE to be open? I don't
> doubt
> you (since that has almost never worked out in the past) but I fail to see
> what part of my code requires the VBE as I do not work with VBE components
> in
> any way...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Tom Ogilvy" wrote:
>
>> In some versions of Excel, this might be problemmatic if the VBE isn't
>> open.
>> I believe Chip Pearson shows a more robust way at
>> http://www.cpearson.com/excel/vbe.htm
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Jim Thomlinson" wrote:
>>
>> > This code should be close... ( I tested it locally but you should be
>> > able to
>> > adapt it for your 20 workbooks)
>> >
>> > Sub test()
>> > Dim oWB As Workbook
>> > Dim oSH As Worksheet
>> >
>> > Set oWB = ThisWorkbook
>> > On Error Resume Next
>> > Set oSH = oWB.Sheets("SUMMARY_1")
>> > On Error GoTo 0
>> > If oSH Is Nothing Then
>> > MsgBox "SUMMARY_1 does not exist"
>> > Else
>> > MsgBox "SUMMARY_1's code name is " & oSH.CodeName
>> > End If
>> > End Sub
>> >
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "Barb Reinhardt" wrote:
>> >
>> > > I have about 20 workbooks that I want to extract the code name for a
>> > > workbook
>> > > I'll call oWB. Let's say the the sheet names are "SUMMARY_1" and
>> > > "SUMMARY_2". How do I determine what the code names are for each
>> > > sheet in
>> > > the workbook?
>> > >
>> > > Thanks,
>> > > Barb Reinhardt
>> > >
>> > >

[Dave Peterson]

Nicely explained.

But...

I have sheet.xlt in my XLStart folder. When I used that to insert the sheet, I
couldn't duplicate what you wrote (I did try a few times).

But when I started excel in Safe mode, everything worked exactly the way you
described.

I remember the workaround that I saw was a simple assignment:
set myProject = someworkbook.vbproject

(just as an aside)

Rob Bovey wrote:
>
> Hi Jim,
>
> If the VBProject for a workbook has never been altered (e.g. the
> workbook has only been operated on from the Excel UI), then it really
> doesn't have a VBProject and you can't even depend on the CodeNames. For
> example, if you insert a new worksheet in the middle of existing sheets the
> CodeNames of all worksheets after the one you inserted will change.
>
> Try this: Create a new workbook with two worksheets and save it. Open
> the VBE and you'll see that the CodeNames correspond to the sheet tab names.
> Insert a new worksheet between Sheet1 and Sheet2. In the VBE it still shows
> the CodeNames as you'd expect. Now save the workbook, close it and reopen
> it. The CodeNames are now out of order. Sheet3 has a CodeName of Sheet2 and
> Sheet2 has a CodeName of Sheet3.
>
> The reason this happens is because until you do something to manually
> alter the VBProject of a workbook, VBA considers it not to have one. VBA
> regenerates the VBProject each time you open the workbook, sometimes with
> conflicting results.
>
> This also will occasionally cause problems getting access to anything
> below the VBProject object of the workbook when the VBE is not open because
> VBA may not yet have generated a default VBProject for workbooks with
> unedited VBProjects. In my experience you can force VBA to "wake up" by
> using the VBProject object of the target workbook in some trivial way:
>
> If wkbBook.VBProject.Protection = 0 Then
> ''' Calls on VBComponents should succeed now
> End If
>
> --
> Rob Bovey, Excel MVP
> Application Professionals
> http://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Development
> http://www.appspro.com/Books/Books.htm
>
> "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
> news:453CB085-7574-4C79-AC30-B7262B1ABF0B@microsoft.com...
> > Ok, I'm lost. What part of my code requires the VBE to be open? I don't
> > doubt
> > you (since that has almost never worked out in the past) but I fail to see
> > what part of my code requires the VBE as I do not work with VBE components
> > in
> > any way...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Tom Ogilvy" wrote:
> >
> >> In some versions of Excel, this might be problemmatic if the VBE isn't
> >> open.
> >> I believe Chip Pearson shows a more robust way at
> >> http://www.cpearson.com/excel/vbe.htm
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "Jim Thomlinson" wrote:
> >>
> >> > This code should be close... ( I tested it locally but you should be
> >> > able to
> >> > adapt it for your 20 workbooks)
> >> >
> >> > Sub test()
> >> > Dim oWB As Workbook
> >> > Dim oSH As Worksheet
> >> >
> >> > Set oWB = ThisWorkbook
> >> > On Error Resume Next
> >> > Set oSH = oWB.Sheets("SUMMARY_1")
> >> > On Error GoTo 0
> >> > If oSH Is Nothing Then
> >> > MsgBox "SUMMARY_1 does not exist"
> >> > Else
> >> > MsgBox "SUMMARY_1's code name is " & oSH.CodeName
> >> > End If
> >> > End Sub
> >> >
> >> > --
> >> > HTH...
> >> >
> >> > Jim Thomlinson
> >> >
> >> >
> >> > "Barb Reinhardt" wrote:
> >> >
> >> > > I have about 20 workbooks that I want to extract the code name for a
> >> > > workbook
> >> > > I'll call oWB. Let's say the the sheet names are "SUMMARY_1" and
> >> > > "SUMMARY_2". How do I determine what the code names are for each
> >> > > sheet in
> >> > > the workbook?
> >> > >
> >> > > Thanks,
> >> > > Barb Reinhardt
> >> > >
> >> > >

--

Dave Peterson