Hi,
Is it possible to get/set the "sheet code name" of a worksheet similar to the codename property of the Excel automation component?
I browsed the Worksheet class properties but I was not able to find this property.
Thanks,
Omid.
What do you mean “sheet code name”? How can we see it in MS Excel?
It's not visible in Excel. It's only accessible with the object model.
The following link describes this property: http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel._worksheet.codename(VS.80).aspx
Omid.
The value that you see in the cell to the right of (Name) in the Properties window is the code name of the selected object. At design time, you can change the code name of an object by changing this value. You cannot programmatically change this property at run time.
The code name for an object can be used in place of an expression that returns the object.
It’s possible for the sheet name to be different from the code name. When you create a sheet, the sheet name and code name are the same, but changing the sheet name doesn’t change the code name, and changing the code name (using the Properties window in the Visual Basic Editor) doesn’t change the sheet name.
So I think get sheet code name is possible but set is impossible.
Why do you need this feature? Only if the worksheet contains VBA/macros, you can get it from worksheet. Otherwise, it will be same as worksheet name.
We need the CodeName because the current process uniquely identifies each worksheet among all the worksheets that a company will use. The CodeName that we generate is based on GUID and stored in a database. It allows us to uniquely identifiy it when the customer sends the file back in our application.
In our current development we use the Excel automation object to get the CodeName. As it is a read-only property we developed a workaround in order to programmatically change it.
Regards,
Omid.
Hi Omid,
I will check the feasibility of sheet code name get/set property. Will your Excel files contain VBA/macro? Changing the code name may fail VBA/macro.
Hi Laurence,
No our Excel files do not contain macros.
Thanks,
Omid.
OK. I will add a CodeName property for each worksheet in the future release.
Let me to add some comments to this thread as an excel programmer.
It can be useful if you want to add CodeName property to aspose.cells. Isn't it there, yet ?
I use CodeName property very often because is not accessible to a user outside VBA
.CodeName property represents the name of an object in VBA editor and can be used as and object in vba.
To use it you must have reference to Microsoft Visual Basic for Application Extensibility 5.3 in your project.
But there are some problems.
Look at this code
Sub InsertWorkbook()
Dim oSheet As Worksheet
Set oSheet = ActiveWorkbook.Worksheets.Add
MsgBox oSheet.CodeName
End Sub
It will display beautiful name on a screen when you run it from VBA environment. Like "Sheet4", "Sheet5" etc.
But try to run it from Workbook_open or assign it as a macro to a button on a worksheet and don't open VBA.
It will display empty string.
Why? Because CodeName property is not evaluated for newly inserted sheets if VBA environment is not open.
It must be opened at least once. Manually or programmatically.
so
Sub InsertWorkbook()
Dim oSheet As Worksheet
Set oSheet = ActiveWorkbook.Worksheets.Add
Dim vbp As VBProject
Set vbp = ThisWorkbook.VBProject
set vbp = Nothing
MsgBox oSheet.CodeName
End Sub
will work correctly, but you must enable "trust access to visual basic project" in Tools/Macro/Security.
Also everybody says the CodeName property is read only. No, it isn't. :)
Look at this procedure
Sub InsertWorkbook()
Dim oSheet As Worksheet
Dim vbp As VBProject
Dim codeName As String
Set oSheet = ActiveWorkbook.Worksheets.Add
Set vbp = ThisWorkbook.VBProject
MsgBox oSheet.codeName
Dim vbc As VBComponent
Set vbc = vbp.VBComponents(oSheet.codeName)
vbc.Properties("_CodeName") = oSheet.codeName & "Changed"
MsgBox oSheet.codeName
Set vbc = Nothing
Set vbp = Nothing
Set oSheet = Nothing
End Sub
It displays CodeName property before and after changes.
You can go to VBA and see that (Name) property has been changed.
Regards
Krzysztof