xlsread only works once per matlab session

xlsread will work once and then it returns:

   Error: Object returned error code: 0x800AC472

Error in ==> xlsread at 236
    Excel.DisplayAlerts = 0

If I close Matlab and reopen it works one time again...

[Fangjun Jiang]

open xlsread.m file, put a break point on line 236 or before, step through to see if you can find anything useful. What is the OS, Office and MATLAB version of yours?

Answer 1

[Jan]

Google finds a lot of links for "0x800AC472 Excel". One link is:

http://www.mathworks.com/support/solutions/en/data/1-3QJ5I3/index.html?solution=1-3QJ5I3

[Chad]

I did look at many of those links including the one you posted but they usually refer to xlswrite, not xlsread and none were relevant

[Jan]

As far as I understand, the error code means, that Excel is busy. Is it busy from the former call?

Please post your complete call to XLSREAD.

Answer 2

[Edward Umpfenbach]

I ended up just saving my workbook as a csv and using csvread. Works fine. Thanks, though.

Answer 3

[Charles]

Mathworks followed up with some suggestions, and through experimentation I came up with a solution that appears to work. The bottom line is getting MatLab to disable the PI addins before performing any xlsread (or I am now using X Collection), and then re-enable when the files are closed. In order to handle less-than-graceful endings, I use Cleanup to re-enable.

In my main code I use:

DisableAddins(false)
cleanUp = onCleanup(@()DisableAddins(true));

Then the DisableAddins Routine is:

function DisableAddins(newflag, appExcel)
%%Run this before xlsread
try
  disp('attempting to adjust excel addins')
  if nargin==1
      appExcel = actxserver('Excel.Application');
  end
  numAddins = appExcel.AddIns.Count;
  for ii = 1:numAddins
      addName = appExcel.AddIns.Item(ii).Name;

        if strcmpi(addName,'pipc32.xll')
            hAddin = appExcel.AddIns.Item(ii);
            hAddin.Installed = newflag;
        end
    end
    numCOMAddins = appExcel.COMAddIns.Count;
    for ii = 1:numCOMAddins
        addName = appExcel.COMAddIns.Item(ii).ProgId;

        if strcmpi(addName,'PI DataLink')
            hAddin = appExcel.COMAddIns.Item(ii);
            hAddin.Connect = newflag;
        end
    end
catch error1
    if exist('appExcel','var') && nargin==1
        %Close out excel if it was not open on entry
        appExcel.Quit;
        clear appExcel
    end
    rethrow(error1)
end
if nargin==1
    appExcel.Quit;
    clear appExcel
end

If you are manually opening the COM connection instead of using xlsread, you can pass in the handle to the COM connection and it will use that connection.

This seems to have solved our problem with PI.

Chuck

Answer 4

[Fangjun Jiang]

Maybe it is due to memory problem. Can you try clear all and then run xlsread() again?

[Chad]

I tried that but that also didn't work...

Answer 5

[Edward Umpfenbach]

I am also running into this exact same problem. Matlab 2010a, excel 2010.

I write:

price_per_mile = xlsread('C:\Users\Ed\Documents\MATLAB\Data_Input.xls','Inputs','B1:B1');

carbon_per_mile = xlsread('C:\Users\Ed\Documents\MATLAB\Data_Input.xls','Inputs','B2:B2');

The excel file is pretty large but I am only trying to test this right now and import a single cell, then a second cell. Crashes everytime. Any ideas?

[Friedrich]

Do you have any add ins for Excel installed? If so, deactivate them.

[Nike]

Did you try specifying the Sheet number ??

[Marc]

I agree with Friedrich. When my company updated to Office 2007, I had a PI add on from OSIsoft that was causing my code that called an excel file multiple times, accessing multiple sheets, trouble. This was with 2011b and office 2007. Disabling the add-on got me working again.

From what I could tell, when Matlab "opened" the excel file, the PI add on was trying to initialize to the historian's server and not allowing Matlab to re-access the file.

I also disabled Office 2007 "getting started" add ons because I found xlsread ran faster once those were disabled.

Of course, you never know since my company's IT dept was always pushing "fixes" onto our computers which seemed to constantly screw up our in house software.

Answer 6

[Don]

xlsWRITE will work once and then it returns:

   Error: Object returned error code: 0x800AC472

Error using xlswrite (line 220)
Error: Object returned error code: 0x800AC472

If I close Matlab and reopen it works one time again...

How can I fix this for use in a script? If I enter the commands in Command window it works OK

Here's the script code:

[p,n,e]=fileparts(filename);
newFileName = fullfile(filepath, [n,'Ratio']);
xlswrite(newFileName,RatioResults);

Answer 7

[Charles]

I am running into this with MatLab 2015a. Runs through first time (four different xlsread statements, different files). On second run, it fails, not always on the first xlsread, but it will fail. It fails trying to close the com link (quit). Disable PI add-in, all is well. However, customer uses PI (and the data in the spreadsheet comes from PI). MatLab tech support says not their problem, it is third party add-in. However, the add-in does not fail, MatLab fails. Tech support gave me some Excel commands to make a macro to disable the add-in. However, the macro only disables the Excel Addin, but PI uses two, one excel and one COM.