How to enable Grouping on a protected Excel 2007 sheet - without macros

Question

I have an Excel 2007 XLSX workbook I am developing. It has multiple sheets and most of those use Excel's Grouping feature. I want to be able to protect the sheets, to protect some formulas and such from user intervention, but it seems there's no way to do that and still allow the user to expand or collapse the Grouped columns whenever they want.

It appears the standard answer on this is to insert a macro with commands like:

ActiveSheet.EnableGrouping
ActiveSheet.Proect UserInterfaceonly = true

But this workbook has no macros now and cannot have any ever. Is there a way to do this in Excel without a macro?

Thanks!

Answer 1

If there is nothing preventing you from running the code externally, then just put this code into another macro enabled workbook OR run it from a seperate C# winform/console application exe. Note I had quick search of the Excel 2007 object model and didn't find the EnableGrouping method, but maybe i wasn't looking hard enough.

External VBA

Sub UpdateWorkbook()  
Constant workbookpath As String = "C:\somepath\someworkbookname.xlsx"  
Dim wkbk As Workbook  
Set wkbk = Application.Workbooks.Open(workbookpath)  
Dim wksht As Worksheet  
Set wksht = wkbk.Worksheets.Item("sheetname")  
wksht.EnableGrouping  
wksht.Protect UserInterfaceonly = true  
Set wksht = Nothing  
Set wkbk = Nothing  
End Sub

External C#

    public void UpdateWorkbook()  
    {  
        const string workbookpath = @"C:\somepath\someworkbookname.xlsx";  
    Excel.Application xlApp = New Excel.Application();   
    // To use the next line you need the Excel Extension library otherwise use Type.Missing.  
        Excel.Workbook wkbk  = Application.Workbooks.Open(workbookpath);  
        Excel.Worksheet wksht wksht = (Excel.Worksheet)wkbk.Worksheets.get_Item("sheetname");  
//Check the way this method works..  
        wksht.EnableGrouping();  
    // UserInterfaceOnly is the 6th parameter, so 5 before and 11 after.  
        wksht.Protect(missing,missing,missing,missing,missing,True,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing,missing);  
        Set wksht = null;  
        Set wkbk = null;  
    Set xlApp = null;   
    }