Yet Another Excel COM Problem -- SAVEAS - MATLAB Answers

相关文章推荐

英俊的槟榔 · Yet Another Excel COM ...· 2 月前 ·

挂过科的烤面包 · Apple 教育優惠· 11 月前 ·

个性的草稿本 · Mining metrics from ...· 11 月前 ·

焦虑的单车 · 黄伟（云南省昭通市镇雄县领导干部任期经济责任 ...· 1 年前 ·

发财的李子 · VBA数组（三）数组赋值-腾讯云开发者社区-腾讯云· 1 年前 ·

胆小的风衣 · 如何删除pandas中产生的Unnamed: ...· 1 年前 ·

https://www.mathworks.com/matlabcentral/answers/533148-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

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

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

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_862953

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.

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_863083

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

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_863103

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

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_863338

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.

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_863478

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!

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_863483

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_863938

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_865693

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

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_1821524

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_1821804

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_1825869

https://www.mathworks.com/matlabcentral/answers/533148-yet-another-excel-com-problem-saveas#comment_1828989

Community Treasure Hunt

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

Start Hunting!