Yet Another Excel COM Problem -- SAVEAS

Question

dpb 2020-5-25

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas

评论： Cris LaPierre 2021-11-11

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Image Analyst 2020-5-25

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#answer_438318

在 MATLAB Online 中打开

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 个评论
显示 10更早的评论隐藏 10更早的评论

dpb 2020-5-25

编辑：dpb 2020-5-25

OK, I went back to the original to see the difference and if could figure out what went wrong there...following the earlier hint that not adding the ".xlsx" extension, I followed your example of creating a new file name string variable and did the strrep thingie -- that also now works in the original.

Go figure -- what's the possible difference in passing the variable name being different if the content is the same? Again, just one of the frustrating mysteries that seem to arise w/ COM.

I suppose it probably has to do with the cleanup function having registered the earlier name and it got changed behind the scenes. I shouldn't try to be so parsimonious with memory I suppose is the moral!

Since yours is shorter/cleaner w/o the external functions, I'll rename mine to be a backup and use yours going forward w/ the one correction of reinserting the cleanup function. It seems key to have to avoid zombie Excel processes hanging around when something does go south elsewhere in the ML code that weren't trapped. Such happens when developing so saves the tedium of opening task manager and deleting those...

OBTW, the Activate for worksheet isn't needed -- the point here is some of these workbooks have many sheets so need to work on the workbook object itself -- when it's alive and well, then SaveAs does its thing for the whole workbook.

My fallback was going to be to use venerable xlsread/xlswrite with the raw data but that then requires iterating over all the sheets in every book and gets to be very slow.

Thanks again...

dpb 2020-5-25

编辑：dpb 2020-5-25

Thanks again...

These are "accounting" (with apologies to any accountants in the audience) workbooks spanning the time since 2002 that started out with a given format but have transmorgrified over the years as number of and names of accounts have changed. Hence, all one can do is read each sheet as raw data and do searches to find the location of the wanted pieces/parts.

There's no hope in writing them to some fixed template without a tremendous amount of work to make that generic template that can cover all the permutations; all can do is just copy the existing workbooks verbatim.

Am having to retrieve data spanning last 7-8 years and smoosh it all together, getting them all to be one version is just a little cleanup on the way.

There are multiple sets of these keeping tracks of various pieces of the puzzle -- one of the simpler ones has a worksheet for every month (but the month names aren't the same in each, another pain) while the most complicated has from 6 to 8 or 9 worksheets in each workbook, but a separate workbook for every month. Those weren't even named with the same convention and even when were supposed to be, the bookkeepers didn't necessarily follow the rules. I have at least got that part fixed...

It's a nightmare pro bono job...a new acccounting system is contracted for but I'm so busy doing this immediate crisis management haven't had time to get the database designed to be able to make the transition.

Anyways, my plaints aside, thanks a lot for the help! Muchly appreciated!

dpb 2020-5-26

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...