Yet Another Excel COM Problem -- SAVEAS
12 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Have a whole bunch of older Excel files that must process -- and for going forward would like to convert them to the current default .xlsx form programmatically rather than having to do all by hand.
But, as usual, COM is not very cooperative and the error feedback is zilch to figure out what it doesn't like. Can anybody spot the flaw here? Gets to the SaveAs method reliably but then goes boom--
function SaveXlsAsXlsx(filename)
% Takes a .xls file and saves as same name in .xlsx format
% MS VBA Enumerations
% XlFileFormat Enumeration
xlWorkbookDefault = 51; % Workbook default *.xlsx
% XlSaveAsAccessMode Enumeration
xlExclusive = 3; % Exclusive mode
xlNoChange = 1; % Default (does not change the access mode)
xlShared = 2; % Share list
% XlSaveConflictResolution Enumeration
xlLocalSessionChanges = 2; % The local user's changes are always accepted.
xlOtherSessionChanges = 3; % The local user's changes are always rejected.
xlUserResolution = 1; % A dialog box asks the user to resolve the conflict.
% Parse input file name; make sure is .xls
[folder, baseFileName, extension] = fileparts(filename);
if ~strcmpi(extension, '.xls')
error('filename not .XLS type. Aborting.')
end
% Now open Excel COM and do the deed...
try
Excel = matlab.io.internal.getExcelInstance;
catch exc %#ok<NASGU>
warning(message('MATLAB:xlsread:ActiveX'));
basicMode = true;
end
readOnly = false;
[~, workbookHandle,workbookState] = openExcelWorkbook(Excel, filename, readOnly);
c = onCleanup(@()xlsCleanup(Excel,filename,workbookState));
% Substitute .xlsx for .xls as file extension to write
filename=fullfile(folder, baseFileName, '.xlsx');
% and save new file
workbookHandle.SaveAs(filename,xlWorkbookDefault)
%workbookHandle.SaveAs(filename,xlWorkbookDefault,[],[],[],[],xlLocalSessionChanges)
workbookHandle.Close(false);
end
function xlsCleanup(Excel, filePath, alertState)
% Suppress all exceptions
try %#ok<TRYNC> No catch block
% Explicitly close the file just in case. The Excel API expects just the
% filename and not the path. This is safe because Excel also does not
% allow opening two files with the same name in different folders at the
% same time.
[~, name, ext] = fileparts(filePath);
fileName = [name ext];
Excel.Workbooks.Item(fileName).Close(false);
Excel.DisplayAlerts = alertState;
end
end
The pieces of
0 Kommentare
Antworten (1)
Image Analyst
am 25 Mai 2020
Fixes made:
function SaveXlsAsXlsx(filename)
% Takes a .xls file and saves as same name in .xlsx format
% MS VBA Enumerations
% XlFileFormat Enumeration
xlWorkbookDefault = 51; % Workbook default *.xlsx
% XlSaveAsAccessMode Enumeration
xlExclusive = 3; % Exclusive mode
xlNoChange = 1; % Default (does not change the access mode)
xlShared = 2; % Share list
% XlSaveConflictResolution Enumeration
xlLocalSessionChanges = 2; % The local user's changes are always accepted.
xlOtherSessionChanges = 3; % The local user's changes are always rejected.
xlUserResolution = 1; % A dialog box asks the user to resolve the conflict.
% Parse input file name; make sure is .xls
[folder, baseFileName, extension] = fileparts(filename);
if ~strcmpi(extension, '.xls')
error('filename not .XLS type. Aborting.')
end
% Now open Excel COM and do the deed...
try
Excel = matlab.io.internal.getExcelInstance;
catch exc %#ok<NASGU>
warning(message('MATLAB:xlsread:ActiveX'));
basicMode = true;
end
readOnly = false;
if ~isfile(filename)
errorMessage = sprintf('Warning: %s does not exist and it needs to!', filename);
uiwait(errordlg(errorMessage));
return;
end
% If it's not the full path, it will throw an error
if ~(contains(filename, '/') || contains(filename, '\'))
filename = fullfile(pwd, filename); % Prepend current folder.
end
Excel.workbooks.Open(filename);
workbookHandle = Excel.ActiveWorkbook;
% [~, workbookHandle,workbookState] = openExcelWorkbook(Excel, filename, readOnly);
c = onCleanup(@()xlsCleanup(Excel,filename,workbookState));
% Substitute .xlsx for .xls as file extension to write
newFileName = strrep(filename, '.xls', '.xlsx');
% and save new file
workbookHandle.SaveAs(newFileName,xlWorkbookDefault)
%workbookHandle.SaveAs(filename,xlWorkbookDefault,[],[],[],[],xlLocalSessionChanges)
workbookHandle.Close(false);
end
12 Kommentare
Himanshu Verma
am 10 Nov. 2021
@Image Analyst yes, I tried having the extension as '.htm' but that didn't work.
Ok, I'll take a look at it. Thank you
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!