Some worksheet returns NULL for CodeName property. Why?
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? |
Some worksheet returns NULL for CodeName property. Why?
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 |
Some worksheet returns NULL for CodeName property. Why?
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 |
Some worksheet returns NULL for CodeName property. Why?
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 |
Some worksheet returns NULL for CodeName property. Why?
There's still a problem with xl2002.
Peter T wrote: Hi Shigeo, Are your users creating a new workbook from "your" template. If so open the template in the VBE, select one of it's modules. Close everything down and save. I think this should be enough to update all the codenames in the template. Thereafter if user renames one of the existing sheets you should be able to return its original codename. Problem comes if user adds a new sheet, codename for this might not get updated. I don't know any solution other than one of the methods in the links Dave Peterson referred you to. I have found that simply setting a reference to the project is enough to update codenames. If it's acceptable to reference VBProject within the template (rather than your code), try this in the "ThisWorkbook" module of the template: Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim oVBProj As Object Set oVBProj = Me.VBProject Set oVBProj = Nothing End Sub If users are not using a template that you can adapt as above I think you're stuck! Try something like this: Sub UpDateCodename(sht As Worksheet, str As String) Dim oVBProj As Object Set oVBProj = sht.Parent.VBProject Set oVBProj = Nothing str = sht.CodeName End Sub Sub TestUpdate() 'with vbe closed run from Alt F8 Dim wb As Workbook, ws As Worksheet, sName As String Dim s, x Set wb = Workbooks.Add again: For Each ws In ActiveWorkbook.Worksheets s = "" sName = ws.CodeName If sName = "" Then UpDateCodename ws, sName s = "UpDateCodename called" End If MsgBox ws.Name & vbCr & s, , sName Next If x Then Exit Sub x = 1 wb.Worksheets.Add GoTo again ' is update required with the new sheet End Sub Until reading your post I thought the problem of updating codenames only related to XL97 and XL2000 - a) codename of new inserted sheet while the vbe is closed b) codenames of all sheets in a wb that has never seen the vbe and while the vbe is closed. So, does a) or b) also apply to later versions, anyone? Regards, Peter "Shigeo Morita" wrote in message ... 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...2MSFTNGP10.phx. gbl and http://groups.google.com/groups?thre...A-789230F614E6 %40microsoft.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 |
All times are GMT +1. The time now is 10:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com