Error using FreezePanes in Excel

40 次查看（过去 30 天）
Daniel Pereira
Daniel Pereira 2016-9-16
评论： C. Serafini 2022-10-31
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.
  2 个评论
deRicaud
deRicaud 2021-5-1
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
C. Serafini 2022-10-31
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.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（4 个）

Dwight Bartholomew
Dwight Bartholomew 2017-3-21
% 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!
Kenneth Eaton
Kenneth Eaton 2019-3-11
You could try setting the property with a logical value, since it expects a Boolean type:
Excel.ActiveWindow.FreezePanes = true;
weicheng Xu
weicheng Xu 2019-7-3
% 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!
Lucie S.
Lucie S. 2020-6-5
编辑：Lucie S. 2020-6-5
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 ;)

类别

在 Help Center 和 File Exchange 中查找有关 Data Export to MATLAB 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by