Programmatically determining CODE NAME for sheet based upon Sheet
 

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

Programmatically determining CODE NAME for sheet based upon Sheet
 

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

Programmatically determining CODE NAME for sheet based upon Sh
 

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

Programmatically determining CODE NAME for sheet based upon Sh
 

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

Programmatically determining CODE NAME for sheet based upon Sh
 

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

Programmatically determining CODE NAME for sheet based upon Sh
 

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

Programmatically determining CODE NAME for sheet based upon Sh
 

I'm hoping that it's Tom's memory (and not mine!) <vbg.

Tom????

Jim Thomlinson wrote:

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


--

Dave Peterson

Programmatically determining CODE NAME for sheet based upon Sh
 

It was more nefarious than after adding a worksheet:

http://tinyurl.com/eeewp

Unfortunately the thread is incomplete, but the topic was about what I said.
By the way, I never got their solutions to work for me.

The solution I used and which at one time was on Chip's Site was to use the
properties in the VBE sequence of objects.

Probably fixed by now, but can't say.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I'm hoping that it's Tom's memory (and not mine!) <vbg.

Tom????

Jim Thomlinson wrote:

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


--

Dave Peterson

Programmatically determining CODE NAME for sheet based upon Sh
 

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

Programmatically determining CODE NAME for sheet based upon Sh
 

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

Programmatically determining CODE NAME for sheet based upon Sh
 

Hi Dave,

Interesting observation about Sheet.xlt. I hadn't noticed that before.
This definitely isn't a well documented or absolutely repeatable phenomenon,
although someone from MS did once confirm they were aware of the problem but
weren't likely to fix it.

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

Yeah, that should work as well. I think just about anything that
exercises the VBProject object of a workbook will bootstrap the process of
creating the whole VBProject for that workbook if it doesn't already exist.

--
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

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

Programmatically determining CODE NAME for sheet based upon Sh
 

Correct, I'm trying to get the code name of an existing sheet in the
workbook, not one that I've added previously.

Help me understand something. If I have Sheet1, Sheet2 and Sheet3 in the
workbook when I start and I change the Sheet names to say Name1, Name2 and
Name3. I then add a sheet between Name2 and Name3. Do the code names of
the sheets change??? I just checked it and the code name for Sheet3 changes
to Sheet4. I presume that if I changed the code names in the Properties
window for each sheet, they would be static.

Thanks everyone for your assistance.
Barb

"Jim Thomlinson" wrote:

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

Programmatically determining CODE NAME for sheet based upon Sh
 

Well, you did get colored tabs!



Rob Bovey wrote:

Hi Dave,

Interesting observation about Sheet.xlt. I hadn't noticed that before.
This definitely isn't a well documented or absolutely repeatable phenomenon,
although someone from MS did once confirm they were aware of the problem but
weren't likely to fix it.

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

Yeah, that should work as well. I think just about anything that
exercises the VBProject object of a workbook will bootstrap the process of
creating the whole VBProject for that workbook if it doesn't already exist.

--
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

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

--

Dave Peterson

Programmatically determining CODE NAME for sheet based upon Sh
 

"Barb Reinhardt" wrote in message
...
I presume that if I changed the code names in the Properties
window for each sheet, they would be static.

Hi Barb,

That's correct. As soon as you modify anything in the Visual Basic
Project of a workbook, all aspects of that project will become persistent,
including those you haven't modified.

--
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

Programmatically determining CODE NAME for sheet based upon Sh
 

I remember seeing the problem when new sheets were added (a blank codename was
returned). But I guess I didn't see (or dodn't remember seeing <vbg) the other
ways that it can manifest itself.

Glad to see that your memory is up to snuff!



Tom Ogilvy wrote:

I replied to this last night (twice), but it still doesn't appear to have
showed up.

http://tinyurl.com/zrkld

http://tinyurl.com/paeoq

http://tinyurl.com/e95qd

are some links to what I was describing. Think most of it was before you
were a regular here Dave, so it might have gone away by xl2000.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote:

I'm hoping that it's Tom's memory (and not mine!) <vbg.

Tom????

Jim Thomlinson wrote:

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


--

Dave Peterson


--

Dave Peterson