MATLAB Answers

Yet Another Excel COM Problem -- SAVEAS

12 views (last 30 days)
dpb
dpb on 25 May 2020
Commented: dpb on 26 May 2020
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 Comments

Sign in to comment.

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

  8 Comments

Show 5 older comments
dpb
dpb on 25 May 2020
OBTW...I do have the subject set that I started out to fix this AM now renamed... :)
Image Analyst
Image Analyst on 25 May 2020
What happens if you just rename the workbooks from .xls to .xlsx? It pops up a warning but you can just click through that. Might be the simplest option if you can live with the warning that the document is an xls instead of an xlsx.
dpb
dpb on 26 May 2020
I tried that...here Excel refused to open the .xls file named .xlsx. Maybe there was a way to click through, but if can't do it programmatically it's no good to process the hundred or so workbooks.
I can work around the difference between the extension but figured might as well get them all converted as a side benefit of adding some consistency to the mix...then if group by file type they'll not be in different groupings in Explorer as a side benefit.
BTW, I downloaded you class -- nice! I had built several similar but not the more esoteric like the column width adjustments, etc. I'll end up using that a lot!
The comments one was interesting -- I had managed to fight my way through that hurdle -- to compound the issues in the accounting distribution workbook, the particular accounts am working with are pooled from a general fund-raising campaign and the only way to track the individual donors was to use a comment...and then the total in the corpus account is accumulated as a sum of the previous month total plus each of the current month's donations as a term in a formula.
I defined a specific format for comments to be entered so that they can be parsed programmatically; unfortunately, the bookkeepers didn't necessarily follow the rules there, either, precisely. So, I have a code that pulls the comment, displays it in a programmers editor to allow them to be cleaned up and restored...that works pretty nicely altho I couldn't figure out initially how to retrieve a given row/column comment so had to iterate through the whole worksheet and save them all. It was another case I could never get the syntax to work for the single cell range object in the COM. Your code may show the light to fix that.
If one had tried to build a more convoluted system don't know could have if had tried...

Sign in to comment.

Products


Release

R2020a

Translated by