Error using FreezePanes in Excel

33 visualizzazioni (ultimi 30 giorni)
Daniel Pereira
Daniel Pereira il 16 Set 2016
Commentato: C. Serafini il 31 Ott 2022
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 Commenti
deRicaud
deRicaud il 1 Mag 2021
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 il 31 Ott 2022
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.

Accedi per commentare.

Risposte (4)

Dwight Bartholomew
Dwight Bartholomew il 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!

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

weicheng Xu
weicheng Xu il 3 Lug 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!

Lucie S.
Lucie S. il 5 Giu 2020
Modificato: Lucie S. il 5 Giu 2020
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 ;)

Community Treasure Hunt

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

Start Hunting!

Translated by