To illustrate my problem, sample code given below:

set oEx = New Excel.Application
set oBk = oEx.Workbooks.Open("MyBook.xls")

dim oSh as Excel.Worksheet
For Each oSh In oBk.Worksheets
Debug.Print oSh.CodeName
Next

Sometimes I could not refer to the codename of worksheet. After I
intentionally create a tiny macro in the "MyBook.xls" and delete it, I could
refer to the codename of the sheets by the above code. I need to always
refer to the codename in my current project. Someone knows how?

Take a look at this bifurcated thread:
http://groups.google.com/groups?thre...GP10.phx .gbl
and
http://groups.google.com/groups?thre...0microsoft.com
(one line in your browser)

Shigeo Morita wrote:

To illustrate my problem, sample code given below:

set oEx = New Excel.Application
set oBk = oEx.Workbooks.Open("MyBook.xls")

dim oSh as Excel.Worksheet
For Each oSh In oBk.Worksheets
Debug.Print oSh.CodeName
Next

Sometimes I could not refer to the codename of worksheet. After I
intentionally create a tiny macro in the "MyBook.xls" and delete it, I could
refer to the codename of the sheets by the above code. I need to always
refer to the codename in my current project. Someone knows how?

--

Dave Peterson

Thanks, Dave.

I gave it a try, but found that Office 2003 VBA did not allow us to
manipulate VBProject object from code for security purpose. Precisely
speaking, my VBA code is running at Access 2003 to get codename of worksheet
in an Excel book user created. Name of worksheet may goes, but I hate the
possibility that users may change the name of worksheet unintentionnaly.

Any other tips to avoid NULL codename will be appreciated.

Regards,

Shigeo Morita

"Dave Peterson" wrote:

Take a look at this bifurcated thread:
http://groups.google.com/groups?thre...GP10.phx .gbl
and
http://groups.google.com/groups?thre...0microsoft.com
(one line in your browser)

Shigeo Morita wrote:

To illustrate my problem, sample code given below:

set oEx = New Excel.Application
set oBk = oEx.Workbooks.Open("MyBook.xls")

dim oSh as Excel.Worksheet
For Each oSh In oBk.Worksheets
Debug.Print oSh.CodeName
Next

Sometimes I could not refer to the codename of worksheet. After I
intentionally create a tiny macro in the "MyBook.xls" and delete it, I could
refer to the codename of the sheets by the above code. I need to always
refer to the codename in my current project. Someone knows how?

--

Dave Peterson

That thread was the best attempt I've seen. You could search google for more
suggestions (or maybe change that security setting???)

Shigeo Morita wrote:

Thanks, Dave.

I gave it a try, but found that Office 2003 VBA did not allow us to
manipulate VBProject object from code for security purpose. Precisely
speaking, my VBA code is running at Access 2003 to get codename of worksheet
in an Excel book user created. Name of worksheet may goes, but I hate the
possibility that users may change the name of worksheet unintentionnaly.

Any other tips to avoid NULL codename will be appreciated.

Regards,

Shigeo Morita

"Dave Peterson" wrote:

Take a look at this bifurcated thread:
http://groups.google.com/groups?thre...GP10.phx .gbl
and
http://groups.google.com/groups?thre...0microsoft.com
(one line in your browser)

Shigeo Morita wrote:

To illustrate my problem, sample code given below:

set oEx = New Excel.Application
set oBk = oEx.Workbooks.Open("MyBook.xls")

dim oSh as Excel.Worksheet
For Each oSh In oBk.Worksheets
Debug.Print oSh.CodeName
Next

Sometimes I could not refer to the codename of worksheet. After I
intentionally create a tiny macro in the "MyBook.xls" and delete it, I could
refer to the codename of the sheets by the above code. I need to always
refer to the codename in my current project. Someone knows how?

--

Dave Peterson


--

Dave Peterson