MATLAB Answers

Error using FreezePanes in Excel

41 views (last 30 days)
Daniel Pereira
Daniel Pereira on 16 Sep 2016
Answered: weicheng Xu on 3 Jul 2019
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.

  0 Comments

Sign in to comment.

Answers (3)

Dwight Bartholomew
Dwight Bartholomew on 21 Mar 2017
% 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!

  0 Comments

Sign in to comment.


Kenneth Eaton
Kenneth Eaton on 11 Mar 2019
You could try setting the property with a logical value, since it expects a Boolean type:
Excel.ActiveWindow.FreezePanes = true;

  0 Comments

Sign in to comment.


weicheng Xu
weicheng Xu on 3 Jul 2019
% 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!

  0 Comments

Sign in to comment.

Sign in to answer this question.


Translated by