Yet Another Excel COM Problem -- SAVEAS

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

Answer 1

[Image Analyst]

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

[dpb]

Thanks, IA.

I had had trouble with the more direct

Excel.workbooks.Open(filename);
workbookHandle = Excel.ActiveWorkbook;

in the past for some reason also unbeknownst to me so in desperation I pulled the openExcelWorkbook routine from the xlsread code as it always seemed to work. I still don't begin to have a klew as to the difference nor cause of previous problems--it seems "magic happens" sometimes and other times "not so much!"

I'll give it a go...I discovered not terribly long after posting that mine works if I don't specify the file extension--that "makes go boom!" with the version here; will report if works with yours.

I like the idea of the prepending the pwd as a general case; in this case I'll be passing in the resolved full name because it will be called in loop with the result of a dir() listing of the files needing conversion which will have the target directory in it which isn't the current.

[dpb]

Oh.

>> SaveXlsAsXlsxIA(fullfile(cd,'anonymous.xls'))
Warning: The following error was caught while executing 'onCleanup' class destructor:
Unrecognized function or variable 'workbookState'.
Error in SaveXlsAsXlsxIA>@()xlsCleanup(Excel,filename,workbookState) (line 43)
c = onCleanup(@()xlsCleanup(Excel,filename,workbookState));
Error in onCleanup/delete (line 81)
            obj.task();
Error in SaveXlsAsXlsxIA (line 51)
end 
> In SaveXlsAsXlsxIA (line 51) 
>> 

I don't know enough to mung on a cleanup function...so I pulled that from the open routine.

And, the cleanup function has to be local within the referencing routine scope so can't delete it from the file. That may have just been for brevity...

[dpb]

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

[Image Analyst]

If I have fixed content, and I want to use a pre-formatted workbook (cell colors, fonts, etc.) for my results, I'll use xlswrite().

If I have variable content (number of worksheets or columns varies), and I want to use a pre-formatted workbook for my results, I'll use ActiveX.

If I have any content and don't want to use a pre-formatted workbook template, then I'll use writecell() and writematrix() because these are faster than xlswrite(). However writecell() and writematrix() will blow away any formatting you'd done to the workbook, such as cell shading, borders, fonts, etc. Grrrrr... I've complained about that to them and they've put in on the feature request list.

For what it's worth, I'm attaching my ActiveX Excel class that you can use as static methods. You just need to instantiate the Excel variable with getActivexserver() first.

[dpb]

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]

OBTW...I do have the subject set that I started out to fix this AM now renamed... :)

[Image Analyst]

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]

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

[Himanshu Verma]

@Image Analyst

Is it possible to save the excel sheet as .htm file using MATLAB as we would do manually? I'm exporting text and images to excel sheet and saving it. But I want to save the same as .htm file automatically using Actxserver in MATLAB. What's the possible workaround. TIA

[Image Analyst]

@Himanshu Verma, did you try having the filename use an htm or html extension? Otherwise you'd have to look at the API documentation

https://docs.microsoft.com/en-us/office/vba/api/excel.constants

[Himanshu Verma]

@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

[Cris LaPierre]

Answered here.