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

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

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

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

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

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

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" wrote in message
...
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

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" wrote in message
...
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