sameera

Hi,

I need to store some additional data along with a cell in a VSTO app. This could for example, include an internal ID; data that the user need not see or edit. I would want to save this data along with the worksheet as well.

What we've done so far is to use Comments with the required cells. Comments however can easily be messed up by the user. Is there a more "elegant" way to achieve this (hopefully without the need for protected worksheets)...



Re: Visual Studio Tools for Office Stroing additional data along with a cell

Dennis Wallentin

Hi,

"Elegant" is for me robust and reliable solutions so let see if one of the following may be of interest:

  • Use a hidden native worksheet to store the extra information in or use a hidden column to store them. Use a Windows Form to let the end users view/edit the information.
  • Use names that instead of refering to cells refer to constant values in terms of the extra information. The built-in UI may not be a user friendly UI so it will require a customized UI.
  • Since it's a VSTO You can add an app.config file to the project and via My.Settings and via Windows Form(s) make them available both for editing and reading.

In terms of 'elegant' I would use the last above approach







Re: Visual Studio Tools for Office Stroing additional data along with a cell

sameera

Hi,

The hidden column wouldn't work because we are inserting the data into where ever the user wants them to be placed. That could mean one of his existing reports as well. But, a hidden worksheet + named ranges could work. Only problem with that is we'd be dealing with tables that are of 100s or 1000s of records. But, still that can work Smile


The info we trying to put are row-specific and have to be bound to the row itself. So, the config file solution wouldn't work.


Thanks.





Re: Visual Studio Tools for Office Stroing additional data along with a cell

Dennis Wallentin

Hi Sameera,


Only problem with that is we'd be dealing with tables that are of 100s or 1000s of records. But, still that can work

If You today use comments through the built-in UI then You face the same problem when it comes to the number of rows information. I would go with this approach and provide a user friendly UI to view/edit the additional information.






Re: Visual Studio Tools for Office Stroing additional data along with a cell

crissnet

Hi all,

How can I use a hidden native worksheet and a relative namedrange Do you have a C# code example please

I've try this:

Excel.Worksheet m_sheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;

NamedRange cellRange = (NamedRange)m_sheet.Cells[1, 1];

But I've an error when I try to create the cellRange object.

Thanks very much!!

Criss






Re: Visual Studio Tools for Office Stroing additional data along with a cell

crissnet

I've read that NamedRange objects are Host controls, that are only available in document-level customizations. I have an application-level customization and I can't add a NamedRange.....

If I have my WorkSheet object, is it possible to add a NamedRange object Else how can I use to store my additional data for some cells

Thanks

Criss






Re: Visual Studio Tools for Office Stroing additional data along with a cell

Dennis Wallentin

Hi Criss,

The following shows how we can add names to the workbook's name collection. You may consider to hide the added names from the end users.

Code Snippet

Sub Add_Name()

Dim RangeData = Globals.Sheet1.Range("A1:A2")
Globals.ThisWorkbook.Names.Add("Hello", RangeData)

Dim RangeNames As Excel.Name

For Each RangeNames In Globals.ThisWorkbook.Names
MessageBox.Show(RangeNames.Name.ToString & RangeNames.RefersTo.ToString)
'The following hide the name, i e cannot be viewed by the end users.
RangeNames.Visible = False
Next

End Sub

If You're working with an add-in the following shows how to:

Code Snippet

Dim xlWBook As Excel.Workbook = Globals.ThisAddIn.Application.ActiveWorkbook
Dim xlSheet As Excel.Worksheet = xlWBook.Worksheets(1)
Dim xlRange As Excel.Range = xlSheet.Range("A1:A2")
Dim xlName As Excel.Name

xlWBook.Names.Add("Test", xlRange)

For Each xlName In xlWBook.Names
MessageBox.Show(xlName.Name & xlName.RefersTo.ToString)
xlName.Visible = False
Next






Re: Visual Studio Tools for Office Stroing additional data along with a cell

crissnet

Thank you Dennis for the suggest,

but if I have many informations for any cell, how can I know in the Names List which informations is for which cell






Re: Visual Studio Tools for Office Stroing additional data along with a cell

crissnet

I have also another problem, I need to detect cells's changes and the Range object don't have ChangeEventHandler..... how can I made






Re: Visual Studio Tools for Office Stroing additional data along with a cell

Dennis Wallentin

Hi,

but if I have many informations for any cell, how can I know in the Names List which informations is for which cell

If You look on the code provide in my previously post You notice that I use "xlName.RefersTo" which gives the cell reference.


I have also another problem, I need to detect cells's changes and the Range object don't have ChangeEventHandler..... how can I made

I'm not sure what You're upto but suppose we want to track any changes for the range A1:A5.
We first need to get their values by for instance populating an 2D array (which also needs to hold the cells references).
Next we can check if the cells's values have been changed or not by using the event where we can iterate through the array:

Code Snippet

Private Sub Application_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Microsoft.Office.Interop.Excel.Range) Handles Application.SheetSelectionChange

However, this is not a good strategy and therefore You should consider to perhaps use another point to check like before saving or closing. These events are also available:

Code Snippet

Private Sub Application_WorkbookBeforeSave(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook, ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeSave


Private Sub Application_WorkbookBeforeClose(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeClose