1

I've been trying for a while to run the following code with Microsoft.Excel.Interop on a new sheet which I add to an existing workbook and fill dynamically, then for display purpose I set these 3 properties:

worksheet.Application.ActiveWindow.SplitRow = 4;
worksheet.Application.ActiveWindow.SplitColumn = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;

Everytime I have the same bug in the resulting workbook : all the other worksheets which were already with frozen panes, become unstructured (half of column B overlaps column C, and other interesting graphical details...).

What I have found out while checking other libraries of Excel automation (ClosedXML for example) is that others modify directly the XML code, so they don't use the ActiveWindow, but directly the worksheet itself.

Is it possible that my problem comes from the fact that I modify a property of the ActiveWindow improperly? If yes, does somebody know whether it's possible to freeze panes without doing it through the ActiveWindow ? If no, does somebody have an idea where it can come from ?

Thanks in advance

Roddhes

Roddhes
  • 11
  • 3

1 Answers1

0

Have You activated appropriate worksheet before setting the properties? I mean something like:

Worksheet.Activate();
Worksheet.Application.ActiveWindow.SplitRow = 4;
Worksheet.Application.ActiveWindow.SplitColumn = 1;
Worksheet.Application.ActiveWindow.FreezePanes = true;

As a help, You can find some code samples for freezing the top row here (be sure to read the comments there for additional code improvements): https://stackoverflow.com/a/5060792/232530

It seems that freezing panes can be done using OpenXML as well. It's described in another SO thread: Freeze Panes in OpenXml SDK 2.0 for Excel document

Community
  • 1
  • 1
Lukasz M
  • 5,635
  • 2
  • 22
  • 29
  • Yes I had activated the worksheet before using it. The panes are properly frozen on my worksheet after I run this code, the only problem is about the collateral damage of all the other sheets in the workbook. – Roddhes May 10 '16 at 09:59
  • 1
    I've tried OpenXML and it works (as for the other XML interop libraries), but to use it I need to close my workbook through Microsoft.Interop then reopen it through OpenXML, and the workbooks I generally use are heavy so I would prefer to deal only with only one library. – Roddhes May 10 '16 at 10:02
  • Could You describe more precisely how You activate the window and where You assign the Worksheet variable? You mentioned that You create the worksheet dynamically, so maybe the way it's created causes the described effects after freezing panes. Please try to freeze an existing pane and see if it works correctly for that one. – Lukasz M May 14 '16 at 18:42
  • Alternatively, instead of setting `SplitRow` and `SplitColumn` values, You may want to write `Range("B5").Select();` (and then set `FreezePanes` to `true`, as before). It does not do the actual split, but seems to work the same for freezing the panes in this scenario, – Lukasz M May 14 '16 at 18:52