0

Is it possible to set a Unique GUID Property of each the excel workbook sheet. So that even when user renames the sheet I could be able to retrive the correct sheet programmatically via sheet Unique GUID.

We are creating a plugin which should be able to point to correct excel sheet even when user renames it to a different value.

Could not find any handle or property in excel sheet object.

Microsoft.Office.Interop.Excel.Worksheet sheet1 in ActiveWorkbook.Worksheets

enter image description here

abksharma
  • 576
  • 7
  • 26
  • 1
    In Excel directly, you use the "Name" property with the VBA editor. It's a technical name for the woksheet that's relevant for the project. Is that "CodeName" in the interop approach? Not sure but worth looking into if you're not sure. Looks like you may be able to if the workbook is something you can edit ... https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.codename – Skin Apr 18 '19 at 11:18
  • Its not code name if I try to access or write anything in code name it does not allow. for example I was able to set workbook information using (Microsoft.Office.Core.DocumentProperties)ActiveWorkbook.CustomDocumentProperties – abksharma Apr 18 '19 at 11:20
  • 1
    Do you have access to the workbook? If you do, open it, go to the VBA editor, change the CodeName of the sheet in the properties of the sheet in question, save it and then go back to your interop implementation and see if CodeName is now set to what you set it to in the VBA edtior. If so, you can use that. Of course, you can't use that if you generate the workbook, only if you're opening a pre-existing workbook you have control over. Make sense? I use that exact approach in VBA because that's the whole point of that property. – Skin Apr 18 '19 at 11:23
  • Yes Got it trying Now. Thanks in advance – abksharma Apr 18 '19 at 11:24
  • If this works, I'll throw it in as an answer. ;-) – Skin Apr 18 '19 at 11:28
  • Myworkbook is new and I am not expecting anyone to change the Codename proprety. But Can not seem to set CodeName Propert via C# Interop VSTO code. – abksharma Apr 18 '19 at 11:39
  • No, i mean set it directly in Excel, you won't be able to set it at run time, it's a design time only field. That field is for that exact purpose. It's used so if the name of the sheet changes or it's index changes, you can still reference it via the technical name (CodeName). I'll add some instructions in the answer and you can try. If it doesn't work then no harm done. – Skin Apr 18 '19 at 11:42

1 Answers1

1

This is a long shot but I'd set the (Name) field for the worksheet in Excel directly (from the VBA Editor) ...

enter image description here

... and see if that is then accessible from the CodeName field in the interop properties for the workbook in C# implementation.

If you're not familiar with how to get to that editor, in Excel, press Alt + F11

Obviously after you've made that change, save the workbook and then try again from your C# project.

That field is for that exact purpose but it's a design time field only, you can't set it at run time. It's used so if the name of the sheet changes or it's index changes, you can still reference it via the technical name (CodeName).

I'd be interested to see how that goes and if it then works for you.

Skin
  • 9,085
  • 2
  • 13
  • 29
  • I was able to access this CodeName which was different from sheetName from Code by sheet1.CodeName (Microsoft.Office.Interop.Excel.Worksheet) – abksharma Apr 18 '19 at 12:00
  • 1
    Perfect ...! A user will never find that, it's buried away, as you can see. Glad that worked for you and glad I could help. – Skin Apr 18 '19 at 12:01