Error using FreezePanes in Excel

Hello.

I am trying to do such a simple operation as freezing panes in excel, providing the split row and the split column. I have done this thousands of times, but it is not working anymore.

I suspect it is failing due to Windows 10 Pro Anniversary Update [version 1607] (my Excel version is 2013).

I've tested the code on Windows 7 Pro (Excel 2010) and Windows 10 Pro [version 1511] (Excel 2013) and it is working.

The code is the following:

% Use taskkill to close failed excels
clc;  [~,~]=system('taskkill /im Excel.exe /f');  pause(2);
File = [pwd filesep 'Test.xlsx'];

if exist(File,'file');
  delete(File);
end
Excel = actxserver ('Excel.Application');
if ~exist(File,'file')
  xlswrite(File,{''});
end

try
  ExcelWorkbook = Excel.workbooks.Open(File);
catch exc
  try
      ExcelWorkbook = invoke(Excel.workbooks,'Open',File);
  catch exc2
      disp(exc.message);disp(exc2.message);throw(exc2);
  end
end

% Freeze two rows and one column
Excel.ActiveWindow.SplitRow     = 2;
Excel.ActiveWindow.SplitColumn  = 1;
Excel.ActiveWindow.FreezePanes  = 1; % If I remove this, it works, but does not freeze cells, of course.

Excel.Range('A:A').NumberFormat = 'MM/dd hh:mm;@'; % Format as a date
Excel.Range('A1').Select; % Return to cell A1

ExcelWorkbook.Save
ExcelWorkbook.Close(false) % Close Excel workbook.
Excel.Quit;
delete(Excel);

The error I'm getting is:

 Invoke Error, Dispatch Exception:
 Source: Microsoft Excel
 Description: Cannot assign the property "FreezePanes" of the class "Window" (*translated from spanish).
 Help File: xlmain11.chm
 Help Context ID: 0

Any ideas? Thanks in advance.

[deRicaud]

Hi, have you found the solution ? I have the exact same issue. I figured out that this is due to the excel window being really small (you can check this with Excel.Visible = 1; If you make it manually bigger, the freeze pane command works). So I guess the solution would be to make the excel sheet in full size before applying freezePanes, but i don't know how to do this programmatically...

[C. Serafini]

Same issue here, it was working fine and suddenly it stopped. If I open the excel manually its reduced to an unusable size, so I asume that is the problem but I have no clue as to why or how to bypass it.

Answer 1

[Dwight Bartholomew]

% Freeze two rows and one column
Excel.ActiveWindow.SplitRow     = 2;
Excel.ActiveWindow.SplitColumn  = 1;
Excel.ActiveWindow.FreezePanes  = 1;

I'm using Excel 2016 and this is working just fine. And, thank you, this was just the solution I was looking for!

Answer 2

[Kenneth Eaton]

You could try setting the property with a logical value, since it expects a Boolean type:

Excel.ActiveWindow.FreezePanes = true;

Answer 3

[weicheng Xu]

% Freeze two rows and one column
Excel.ActiveWindow.SplitRow     = 2;
Excel.ActiveWindow.SplitColumn  = 1;
Excel.ActiveWindow.FreezePanes  = 1;

Thank you! That's exactly what I want!

Answer 4

[Lucie S.]

I got the same error when I was just running a part of the code and looking at excel File at the same time.

I would try to put at the end of the code the "Freeze two rows and one column" section and execute the code in one time.

Worked for me ;)