none
re-create VSTO host Named Range control during Add-In startup RRS feed

  • Question

  • Hi

    Have workbook that has host NamedRanges added dyanamically from a VSTO Add-In. As defined in MSDN these are not persisted after save and close. So am re-creating the during Workbook open event trapped by the VSTO Add-In. I am only recreating Host NamedRange for Interop Named Ranges that EXIST in the Workbook. However after doing so the Workbook becomes dirty which is unfortunate as the user is not aware they have made any changes.

    I'm recreating them with:

    Int32 iCtrl=vstoWorksheet.Controls.IndexOf(RangeName);  //-1 if not exist

    if (iCtrl>=0) vstoWorksheet.Controls.Remove(RangeName);

    vstoRng=vstoWorksheet.Controls.AddNamedRange(rng,RangeName);

    Can anyone advise if there is another way to do this that does not make the workbook dirty? There seems to be no method to convert an Interop Named Range to a Host Named Range. So the only method is to Remove and Add which I presue is also removing and adding the Interop Named Range, hence making the Workbook dirty. Is a shame we don't have a method to add a Host Named Range from an existing Interop Named Range.

    Thanks for your attention.


    • Edited by jeffmaultby Wednesday, September 4, 2013 1:22 PM
    Wednesday, September 4, 2013 1:21 PM

Answers

  • Hi Jeff,

    Thank you for your suggestions. Currently, I failed to figure out  another way to make a host named range control from an existing interop named range. But I made some improvements of the workaround in my last reply. You are able to re-create all named ranges automatically and keep the process transparent to end-user through the code below:

            public void OnWorkbookOpen(Excel.Workbook wb)
            {
                var sheet = wb.ActiveSheet as Excel.Worksheet;
    
                var vstoSheet = sheet.GetVstoObject(Globals.Factory);
    
                // re-create all named ranges automatically
                foreach (Excel.Name name in wb.Names)
                {
                    Excel.Range range = name.RefersToRange;
    
                    vstoSheet.Controls.AddNamedRange(range, name.Name);
                }
    
                wb.Saved = true;
            }


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    Sunday, September 8, 2013 12:45 PM
    Moderator

All replies

  • Hi jeffmaultby,

    Thank you for posting in the MSDN Forum.

    I'm trying to involve some senior engineers into this issue and it will take some time.

    Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day!

    Best regards,

    Fei


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, September 6, 2013 2:56 AM
    Moderator
  • Hi Jeff,

    By default, dynamically created named ranges are not persisted in the worksheet as host controls when the worksheet is closed. After workbook being closed, you are not able to get the name range control object when the workbook being opened. So "vstoWorksheet.Controls.IndexOf(RangeName)" will always return -1;

    You can get an existing named range object through the code below:

            private void button2_Click(object sender, EventArgs e)
            {
                // get named range object
                var workbook = Globals.ThisAddIn.Application.ActiveWorkbook as Excel.Workbook;
    
                var item = workbook.Names.Item(Type.Missing, "CustomNamedRange", Type.Missing);
            }

    To keep re-create VSTO host Named Range control transparent to end-user, we can set Workbook.Saved property to True to achieve the goal:

            public void OnWorkbookOpen(Excel.Workbook wb)
            {
                var sheet = wb.ActiveSheet as Excel.Worksheet;
    
                var vstoSheet = sheet.GetVstoObject(Globals.Factory);
    
                var rng = sheet.get_Range("A1:A5") as Excel.Range;
    
                var nameRange = vstoSheet.Controls.AddNamedRange(rng, "CustomNamedRange") as NamedRange;
    
                wb.Saved = true;
            }

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Saturday, September 7, 2013 1:03 PM
    Moderator
  • Thanks Fei, I look forward to the senior engineers reply.
    Sunday, September 8, 2013 11:44 AM
  • Thanks for the reply Jeffrey-Chen

    I'm aware that hosted controls are not persisted, that is precisely why i'm re-creating them.

    I'm aware how to retrieve existing (interop) named ranges, that's how i know they EXIST.

    I'm currently already using the workbook.saved = false property as a work-around.

    The purpose of my enquiry is to question why making an existing named range into a non-persisted host control should make the workbook dirty. It looks like a bug to me where the host control remove and add is also removing and adding the (persisted) interop named range. Unless there is another way to make a host named range control from an existing interop named range.

    Sunday, September 8, 2013 11:49 AM
  • Hi Jeff,

    Thank you for your suggestions. Currently, I failed to figure out  another way to make a host named range control from an existing interop named range. But I made some improvements of the workaround in my last reply. You are able to re-create all named ranges automatically and keep the process transparent to end-user through the code below:

            public void OnWorkbookOpen(Excel.Workbook wb)
            {
                var sheet = wb.ActiveSheet as Excel.Worksheet;
    
                var vstoSheet = sheet.GetVstoObject(Globals.Factory);
    
                // re-create all named ranges automatically
                foreach (Excel.Name name in wb.Names)
                {
                    Excel.Range range = name.RefersToRange;
    
                    vstoSheet.Controls.AddNamedRange(range, name.Name);
                }
    
                wb.Saved = true;
            }


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    Sunday, September 8, 2013 12:45 PM
    Moderator
  • Thanks for the reply Jeffrey-Chen

    As I stated in my original post "... I am re-creating host named ranges during workbook open...", so I am already doing what you have posted.

    Thanks for your attempts to find another method to re-create host named ranges.

    Sunday, September 8, 2013 1:23 PM