Matlab to write on already open excel file

Good morning everyone, I'm having problems trying to tell Matlab to write on a specific file already open. It looks like it's possible only on file already open before I open Matlab. Is it correct? Because I'm looking for a code that helps me to write on a file that could be already open in my computer, so it should check if it's open and then if it is write on it, or if it's not open and then write on it.

Would you please help me out?

[Adam Danz]

Could you share the error message and the section of code that opens/writes the file?

[Fabio Adducchio]

    e                   = actxGetRunningServer('Excel.Application');           % it connects matlab to excel
  w                   = e.Workbooks;                                         % 
  numWorkbooks        = w.Count;                                             % it counts the number of excel files already open
  for i = 1:numWorkbooks
        openFiles{i}  = w.Item(i).Name;
  end
  file                = w.get('Item',1);                                     % Seleziona il file 
  eSheets             = e.ActiveWorkbook.Sheets;                             % Rendi il primo sheet attivo
  eSheet1             = eSheets.get('Item',1);
  eSheet1.Activate
  A                   = 1;
  eActivesheetRange   = get(e.Activesheet,'Range','B1');                       % importa il contenuto delle celle
  eActivesheetRange.Value = A;

[dpb]

And the error???

[Guillaume]

The code given will write 1 in cell B1 of whichever workbook is currently active in Excel. This workbook may or may not be the one defined by file.

The principle of the code, to get the currently running excel instance and querying for the workbooks is correct. Using ActiveWorkbook and ActiveWorksheet is a bit iffy, but indeed we need to know what the problem is with the current code and what is actually desired.

Answer 1

[Image Analyst]

"It looks like it's possible only on file already open before I open Matlab"

With actxGetRunningServer() that might be true. So use actxserver() instead of actxGetRunningServer() to launch Excel with the file NOT already open.

try
  % See if there is an existing instance of Excel running.
  % If Excel is NOT running, this will throw an error and send us to the catch block below.
  Excel = actxGetRunningServer('Excel.Application');
  % If there was no error, then we were able to connect to it.
catch
  % No instance of Excel is currently running.  Create a new one.
  % Normally you'll get here (because Excel is not usually running when you run this).
  Excel = actxserver('Excel.Application');
end

[Fabio Adducchio]

My problem is that the connection is made only with the first instance of excel that was open on my computer. I have multiple instances of excel open at the same time, and the code:

   actxGetRunningServer('Excel.Application')

is getting just one of them, do you know how to create a connection with a precise instance or with all of them and then to pick among them the one I want?

[Image Analyst]

Do you suspect that your workbook is already open in one of those instances? If not, then I don't think it matters. You can just use any one of them to call the open() method to open up your filename.

[Guillaume]

If I understood correctly, Fabio is trying to connect to the excel instance that already has the particular workbook open. He doesn't want to open a new workbook.

[Image Analyst]

Then you can just do this:

excel = actxGetRunningServer('Excel.Application');    % it connects matlab to excel
workbooks = excel.Workbooks;       %
numWorkbooks = workbooks.Count;    % it counts the number of excel files already open
indexOfWorkbookWeWant = 1; % Initialize.
for k = 1:numWorkbooks
  openFiles{k}  = workbooks.Item(k).Name;
  if contains(openFiles{k}, 'whatever name you seek.xlsx')
    indexOfWorkbookWeWant = k;
    break;
  end
end
% Active the correct workbook
workbooks.Item(indexOfWorkbookWeWant).Activate;
% file                = workbooks.get('Item',sheetWeWant);                                     % Seleziona il file
excelSheets         = excel.ActiveWorkbook.Sheets;                             % Rendi il primo sheet attivo
eSheet1             = excelSheets.get('Item', 1);
eSheet1.Activate
A                   = 1;
eActivesheetRange   = get(excel.Activesheet,'Range','B1');                       % importa il contenuto delle celle
eActivesheetRange.Value = A;

[Fabio Adducchio]

In theory this would be a great solution, I like it, but it's getting just one instance of excel connected to matlab, so if the file I'm looking to connect it's not open in that exact instance, then I'm not able to reach it.

Any other suggestion? did you try it on your side?

[Guillaume]

Have you read my answer? As I said, if for some reason MergeInstances is false, turning it on may help. If it's already on or doesn't merge the instances, then you're out of luck unless you write a mex or .Net program to list all excel instances.

[Image Analyst]

Yes I actually tried it myself before posting it. I opened several Excel files and had 3 Excel icons on my task bar. Then I searched for one with a particular name. It worked great. I don't know why another suggestion is needed. If you don't need fancy, precise control over Excel then maybe you can just use xlswrite() (not sure this works with an already open workbook though - it might not).

[Guillaume]

It is possible to have completely separate instances of Excel (i.e. several Excel processes in the task manager). The older the version of Excel, the more likely it is to happen by just opening a new workbook.

It is also the default if the Application.MergeInstances property of Excel is off.

If there are several Excel processes running, actxgetRunningServer will return one of them (which one is up to the OS) and the only way to get the others is to make calls to the win32 API.

The Workbooks collection will only return Workbooks opened in the given process.

Answer 2

[Guillaume]

My problem is that the connection is made only with the first instance of excel that was open on my computer

First thing to check is if

 %excel: excel instance returned by excel = actxGetRunningServer('Excel.Application')
 excel.MergeInstances

is true, and if not, set it to true.

If that doesn't solve the problem (and the older the version of excel the less likely it will), then you're out of luck. There is no built-in way to retrieve the additional instances in matlab. The only way to do it would be with a mex file or a compiled .Net assembly.

[Fabio Adducchio]

Hi Guillame, I tried to use your code and this is what I have:

 e = actxGetRunningServer('Excel.Application');
% excel: excel instance returned by excel = actxGetRunningServer('Excel.Application')
excel.MergeInstances
Undefined variable "excel" or class "excel.MergeInstances".

I think I'm missing something, my version of Matlab is 2017a, could be this the problem?

[Guillaume]

The first line of my code was:

%excel: excel instance returned by excel = actxGetRunningServer('Excel.Application')

If you call your variable e instead of excel, then

e.MergeInstances