Yet Another Excel COM Problem -- SAVEAS

40 views (last 30 days)
dpb
dpb on 25 May 2020
Commented: Cris LaPierre on 11 Nov 2021 at 19:45
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

Answers (1)

Image Analyst
Image Analyst on 25 May 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 Comments

Sign in to comment.

Products


Release

R2020a

Community Treasure Hunt

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

Start Hunting!