Yet Another Excel COM Problem -- SAVEAS
12 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
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 comentarios
Respuestas (1)
Image Analyst
el 25 de Mayo de 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 comentarios
Himanshu Verma
el 10 de Nov. de 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
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!