Write to a hidden Excel Worksheet with c#

Question

I am writing a Add-In for excel where i have to store workbookspecific settings in the excel file. I tryed to create a new worksheet, and hide it.

settingsSheet.Visible = XlSheetVisibility.xlSheetHidden;

Now my application was always writing to my open worksheet, not the hidden settingsworksheet. If i remove the part which hides the worksheet, it works fine.

Is there a way to write to hidden excel worksheets? Or is there a better way to store settings?

edit: aditional code

Worksheet newWorksheet;
string newWorksheetname = "SettingsSheet";
foreach (Worksheet sheet in ActiveWorkbook.Sheets)
        {
            if (sheet.Name == newWorksheetname)
            {
                newWorksheet = sheet;                   
            }
        }
if (newWorksheet == null)
 {
  newWorksheet = (Worksheet)Globals.ThisAddIn.Application.Worksheets.Add();
            newWorksheet.Name = newWorksheetname;
  }
newWorksheet.Visible = XlSheetVisibility.xlSheetHidden;
newWorksheet.Cells[1,1].Value="settingsValue";

winghei · Answer 1 · 2016-08-10T12:17:57.803

0

are you properly referencing the worksheet? I think you are referencing the ActiveSheet instead.

edited Aug 10 '16 at 12:17

answered Aug 10 '16 at 12:11

winghei

632
4
9

Yes, I'm pretty sure that I'm referencing the right worksheet. Because when im testing the same code without hiding the settingsworksheet everything work properly. And the settings worksheet was never my ActiveWorksheet when i run the code. – Jonas Wyss Aug 10 '16 at 12:24
It would be nice if you could add some more details on your post. e.g. line where settingsSheet was defined and when you try to populate the worksheet – winghei Aug 10 '16 at 12:42

score 0 · Accepted Answer · edited May 23 '17 at 11:44

0

I didn't found any solution to my problem so I found a method of storing xml in a excel file but not in a hidden sheet. What is the best way to store XML data in an Excel file

edited May 23 '17 at 11:44

Community

1
1

answered Aug 11 '16 at 06:26

Jonas Wyss

152
1
11

Write to a hidden Excel Worksheet with c#

2 Answers2