“Unable to set the FreezePanes property of the Win

问题:

I've build an excel addin which fills a worksheet with data from a database. I also add some styling and lock some rows and columns by using FreezePanes.

worksheet.Activate();
worksheet.Application.ActiveWindow.FreezePanes = false;
worksheet.Application.ActiveWindow.SplitRow = 4;
worksheet.Application.ActiveWindow.SplitColumn = 11;
worksheet.Application.ActiveWindow.FreezePanes = true;

This all worked like a charm in excel 2010/2013 but I recently switched to excel 2016 (office 365) and from then on I had problems with the FreezePanes when my excel worksheet is not on the foreground. I searched the internet and the only thing I come across is that I can only preform a FreezePanes on an active sheet, I knew that - I allready do activate the sheet before setting the FreezePanes. This worked in excel 2010, even though physically my excel wasn't sent to the foreground.

Excel from the office 365 probably really want my excel worksheet to be physically in the foreground but worksheet.Activate() doesn't help and I also tried the following code:

[DllImport("user32.dll")]
[return: MarshalAs(UnmanagedType.Bool)]
static extern bool SetForegroundWindow(IntPtr hWnd);    

[DllImport("user32.dll", SetLastError = true)]
static extern System.IntPtr FindWindow(string lpClassName, string lpWindowName); 

string caption = oExcel.Caption;
IntPtr handler = FindWindow(null, caption);
SetForegroundWindow(handler);

But this too didn't work. Can any body help me with this one?

To be clear: The version of my excel is 2016 Version 1611 (Build 7571.2109)

回答1:

Would it be possible that worksheet.Application.ActiveWindow isn't the window that contains your active worksheet? In previous Excel versions all workbooks had the same window, but since Microsoft dropped MDI for Excel, you might suddenly have two different windows using the same code as before. Mixing those windows up might than result in the problem you encounter.

See this link for some changes since Excel 2013: https://msdn.microsoft.com/en-us/library/office/dn251093.aspx

Another thing you could try is the set the window state to normal before calling FreezePane:

Worksheet.Application.ActiveWindow.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlNormal;

And still another possibility is that this is actually a bug in Excel. I did find someone else that had the same problem but it is unclear whether this person solved the issue or filled a bug report:

https://social.msdn.microsoft.com/Forums/office/en-US/7e6ff1ed-b4c6-4c75-82be-14175f44df55/freezepanes-throws-an-exception-when-excel-is-minimized?forum=exceldev

You could file a bug report with Microsoft and wait to see whether they can confirm this as a bug.

回答2:

Is it a possibility to do this in VBA?

Worksheets("Sheet1").Activate 
ActiveWindow.FreezePanes = False
ActiveWindow.SplitRow = 4
ActiveWindow.SplitColumn = 11
ActiveWindow.FreezePanes = True

If you want to check if the sheet is actually active, you can do:

Private Sub Worksheet_Activate() 
 [method that makes stuff happen]
End Sub

The MSDN documentation would make it seem as if the VBA add-ins work better with 2013/365 than C# ones.

回答3:

Yes! I fixed this like Xatoo suggested with adding:

Worksheet.Application.ActiveWindow.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlNormal;

It is important to add that this solution only works when in fact the window is minimized, so you need to check on this.

Funny detail is that this still does not send the window to the foreground.

回答4:

You can set xlMaximized for best practice. Because xlNormal can same xlMinimized, so get error again.

Worksheet.Application.ActiveWindow.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;