Access or skip excel file that is locked for editing within a loop?
4 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Calabrese
le 16 Juil 2017
Réponse apportée : Image Analyst
le 16 Juil 2017
While running a loop the code errors out on a file that is locked for editing.
excel_xlsx=dir('*.xlsx'); % list of the files
for i=1:length(excel_xlsx);
[~,file]=xlsfinfo(excel_xlsx(i).name);
L=length(file);
[~,txt]=xlsread(excel_xlsx(i).name,L,'B2'); % read the text
[~,txt2]=xlsread(excel_xlsx(i).name,1,'B3'); % read the text
[~,txt3]=xlsread(excel_xlsx(i).name,1,'A10'); % read the text
[~,txt4]=xlsread(excel_xlsx(i).name,1,'A2'); % read the text
if strcmp(txt,'Number:');
delete(excel_xlsx(i).name);
elseif strcmp(txt2,'PROGRAM');
delete(excel_xlsx(i).name);
elseif strcmp(txt3,'UPPER');
delete(excel_xlsx(i).name);
else
strcmp(txt4,'Effect');
[~,sheets] = xlsfinfo(excel_xlsx(i).name);
xlsprotect(excel_xlsx(i).name,'unprotect_sheet',sheets{1,1});
xlswrite(excel_xlsx(i).name,' ',1,'F1:F3');
xlswrite(excel_xlsx(i).name,' ',1,'E1:E4');
end
end
The code errors out at the following for i=15 which is interesting because the file is in position 11 within the folder, they are sorted alphabetically.
[~,txt]=xlsread(excel_xlsx(i).name,L,'B2'); % read the text
The display of the file that is causing the error is '~$VR380_001.xlsx' found from variable 'excel_xlsx(15,1)'
However the display of all the other files is 'VR442_004.xlsx', 'VR442_008.xlsx', etc.
This is my previous code regarding this portion before I condensed multiple for loops to save time and it never caused any error.
SVMS_xlsx=dir('*.xlsx'); % list of the files
for i=1:length(SVMS_xlsx)
[~,file]=xlsfinfo(SVMS_xlsx(i).name);
L=length(file);
[~,txt]=xlsread(SVMS_xlsx(i).name,L,'B2'); % read the text
if strcmp(txt,'Number:')
delete(SVMS_xlsx(i).name)
end
end
this is the error message I get, the code is running within a GUI
Error using xlsread (line 251)
Error in ProcessCDRL>pushbutton1_Callback (line 116)
[~,txt]=xlsread(excel_xlsx(i).name,L,'B2'); % read the text
Error in gui_mainfcn (line 95)
feval(varargin{:});
Error in ProcessCDRL (line 42)
gui_mainfcn(gui_State, varargin{:});
Error in matlab.graphics.internal.figfile.FigFile/read>@(hObject,eventdata)ProcessCDRL('pushbutton1_Callback',hObject,eventdata,guidata(hObject))
Error while evaluating UIControl Callback
0 commentaires
Réponse acceptée
Image Analyst
le 16 Juil 2017
Microsoft Office uses temporary files that start with a tilde. Chances are that you have the file open in Excel, or abruptly shut down Excel once and left this litter behind. You can skip such files like this:
thisFileName = SVMS_xlsx(i).name
if strcmp(thisFileName, '~')
continue; % Skip filenames starting with ~
end
[~,file] = xlsfinfo(thisFileName );
0 commentaires
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!